Have 2 Tables and Need to Combine

g19558

New Member
I have two tables of very similar data. Each table references an invoice number. I am not very good with Access and would like to know if there is a way to take the invoice number and then part number on my Debit Table (listed below) and have that look at the invoice number and then part number on the Credit Table (listed below) and tell me how much credit or debit there is on each invoice.

Debit Table

My Part Number
Cust Part Number
Quantity
Invoice Price
Price Paid
Variance
Total Debit
Trx Number
APC

Credit Table

My Part Number
Cust Part Number
Quantity
Invoice Price
Contract Price
Variance
Total Accrual
Trx Number
APC

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hello and welcome to MrExcel.

Do the 2 tables have a unique but common field? You mentioned invoice number (which would be unique and common) but I don't see it listed as one of the fields in the 2 tables.

Andrew

Hello

The common field would be TRX Number. Thanks for your help

Chris

Hi Chris

Is it likely you will have transactions in the credit table that are not in the debit table? And vice versa? Or will a transaction always appear in both tables? If the transactions always appear in both tables then you could create a select query that looks like this (in SQL view) :

Code:
``````SELECT [Credit Table].[Trx Number], [Credit Table].[My Part Number], Sum([Credit Table].[Total Credit]) AS Credits, Sum([Total Debit]) AS Debits, Sum([Total Credit])-Sum([Total Debit]) AS Difference
FROM [Debit Table] INNER JOIN [Credit Table] ON ([Debit Table].[My Part Number] = [Credit Table].[My Part Number]) AND ([Debit Table].[Trx Number] = [Credit Table].[Trx Number])
GROUP BY [Credit Table].[Trx Number], [Credit Table].[My Part Number];``````

Make sure you use your actual field and table names.

Let's say you always have transactions in the credit table and possibly matching transactions in the debit table, then you could modify the query slightly, like this :

Code:
``````SELECT [Credit Table].[Trx Number], [Credit Table].[My Part Number], Sum([Credit Table].[Total Credit]) AS Credits, Sum(nz([Total Debit])) AS Debits, Sum([Total Credit])-Sum(nz([Total Debit])) AS Difference
FROM [Debit Table] RIGHT JOIN [Credit Table] ON ([Debit Table].[My Part Number] = [Credit Table].[My Part Number]) AND ([Debit Table].[Trx Number] = [Credit Table].[Trx Number])
GROUP BY [Credit Table].[Trx Number], [Credit Table].[My Part Number];``````

If it is the other way around (always in the debit table and possibly in the credit table) then you change the links between the tables to point the other way (double click them top open the link options / join properties).

HTH, Andrew

Replies
1
Views
364
Replies
2
Views
194
Replies
7
Views
846
Replies
8
Views
160
Replies
1
Views
245

1,203,523
Messages
6,055,895
Members
444,832
Latest member
Kauri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back