Microsoft Access - Cross match of records - Asked By Shoro on 06-Jun-12 02:39 PM

Each month i receive a spreadsheets (GL entries from Finance dept) in Excel format which I import to Access table (table G). I already have a database with table A that holds sales entries which the user input. Now I want to be able to validate the user input in table A against entries in table G to look for any possible differences and then create a report.  In both tables, I have common common fields (such as Amount, Currency, Branch and GL ID) and I would like to do cross-match only for only these fields.
Could someone please let me know how to set up a query in Access as I am relatively new to this. 
Your help will be much appreciated.  

hi Asif,


two achieve this.....you have to Add both tables to the query.
Suppose you have two tables:

Table 1 - USERS:

USER ID (PK) ; DEPARTMENT; ROLE; SHIFT; MANAGER ID.

 

Table 2 - EMPLOYEE:

USER ID; DEPT; JOINING DATE; STATUS.

Join them by dragging User ID from Users to User ID in EMPLOYEE.

Double-click the join line and select the option to return ALL records from Users and only related records from EMPLOYEE, then click OK.

Add * from the Users table to the query design grid.

Add User ID from EMPLOYEE to the query design grid; clear the Show check box for this column, and enter Is Null in the Criteria line.


hope it helps....

I would suggest you do two group-by queries, one for each table, grouping by branch, gl id and currency, totaling the amounts, then two queries that compare the intermediate queries.  First comparison could be a left join of g/l totals to sales total, then second could be a left joined from sales total to g/l total, looking for unmatched records.  Something like:

qryGLTotal
SELECT Branch, [GL ID], Currency, sum(Amount) as MonthlyGLAmount
FROM [table G]
GROUP BY Branch, [GL ID], Currency;

qrySalesTotal
SELECT Branch, [GL ID], Currency, sum(Amount) as MonthlySalesAmount
FROM [table A]
GROUP BY Branch, [GL ID], Currency;

qryGLtoSales
SELECT qryGLTotal.Branch, qryGLTotal.[GL ID], qryGLTotal.Currency, qryGLTotal.MonthlyGLAmount, qrySalesTotal.MonthlySalesAmount
FROM qryGLTotal LEFT JOIN qrySalesTotal ON (qryGLTotal.Currency = qrySalesTotal.Currency) AND (qryGLTotal.[GL ID] = qrySalesTotal.[GL ID]) AND (qryGLTotal.Branch = qrySalesTotal.Branch);

qrySalesnoGL
SELECT [table A].Branch, [table A].[GL ID], [table A].Currency, [table A].Amount
FROM [table A] LEFT JOIN [table G] ON ([table A].Currency = [table G].Currency) AND ([table A].[GL ID] = [table G].[GL ID]) AND ([table A].Branch = [table G].Branch)
WHERE ((([table G].Branch) Is Null));