Filtered compare & contrast report

  • Thread starter Thread starter Digital Carnage
  • Start date Start date
D

Digital Carnage

Hi Folks,
I have a database under development that tracks employees, assigned
parking permits and recorded violations of the parking rules. The main
table is tblEmployees and the EIN (employee ID) is the key link to every
other table.

What I've Got:
1. An Excel spreadsheet is pulled from a master database (LDAP) and provided
to me so that I can compare my database against the LDAP.
2. There is a main table (tblEmployees) that I will use to compare against
(tblLDAP)
3. There are 2 fields that are relevant... "EIN" and "Contact Code". These
fields exist in both tables.
4. There is a lookup table that contains 189 contact codes that are relevant
to what this database is supposed to track. (tblContactCode)

What I want to do:
1. Import the Excel Spreadsheet into a table called (tblLDAP) I know how to
do this part.
2. Using the lookup table tblContactCode, I want to filter out any records
in the tblLDAP that do not contain one of the 189 contact code records.
3. I want to then compare the tblEmployees & tblLDAP using the EIN as the
basis for the two tables to identify differences in the Contact Codes of the
two tables.
4. Finally, all I want is a report that highlights the differences only.
Meaning, if EIN 101232 in both tables has the same contact code, then it
should be ignored in the output of the report. I only want the
discrepancies.

I'm not sure if a Union query is what I need and I'm not sure about the
filter process. Also, can all of these functions be written into a single
query or do I have to break it down. I'm a fair user of Access and I can
handle a little bit of visual basic and SQL but if someone has a code sample
or suggestion, I'd be really greatful.

Thanks Rich
 
Looks like you have 2 unmatcjed queries to show at
thesame time. That means UNION. You can make it one
single query, but you have to use SQL window and type the
SQL statement. Somethong like:

SELECT A,B,C
FROM table1
LEFT JOIN table2 ON table1.A=table2.A
WHERE table2.A IS NULL

UNION

SELECT A,B,C
FROM table3
LEFT JOIN table4 ON table3.A=table4.A
WHERE table4.A IS NULL

Each of the SELECT statemants is a left join (unmatched
quary).
 
Back
Top