Have 2 Tables and Need to Combine

[g19558]

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

 

[Andrew Fergus]

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

 

[g19558]

Hello

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

Chris

 

[Andrew Fergus]

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) :

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 :

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