How do I create a comparison query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a query that will compare data within 2 tables. The
query will specifically look at 3 columns in these 2 tables. If the
information in these 3 columns is the same in each table, then the query will
NOT pull the rest of the info.

Basically, table 1 and table 2 will compare data from 3 columns (ID, Course,
and Start Date). If the data is the same for all three columns in both
tables, then the information will not be pulled down in to the next table
that is created by the query.

I'm still an access noob, so I hope all of this makes sense! I appreciate
any help that you can give me.

Thanks!
 
I would make this part a subquery, as follows:

SELECT * FROM InfoTable LEFT JOIN ComparedTable ON InfoTable.ID=ComparedTable.
ID And InfoTable.Course=ComparedTable.Course And InfoTable.
StartDate=ComparedTable.StartDate WHERE (ComparedTable.ID Is Null) OR
(ComparedTable.Course Is Null) OR (ComparedTable.StartDate Is Null);

Call this sq maybe, qryCompData.

Then, in your higher level query, after you select the correct fields, add
the following join:

"... INNER JOIN qryCompData ON InfoTable.ID = qryCompData.ID And InfoTable.
Course=ComparedTable.Course And InfoTable.StartDate=ComparedTable.StartDate...
"

The subq is filtering out all places where there's a match, and only allowing
to "pass through" the records where there's no match.

The hlq is reading the data based on the data the subq allows through.

HTH
 
Hi Justin,

If you have the same data in more than one problem there may well be trouble
with the basic design of your database especially if you are pouring this
data into a third table. If this is a one-time-thing to clean up data, that's
OK.

Below is a way to find the records in one table but not the other. As you
need to go both ways, this will take a union query. If you have a lot of
data, this could be very, very slow. Change table and column names as
necessary.

SELECT ASIF.OWNER, ASIF.TABLE_NAME, ASIF.COLUMN_NAME
FROM ASIF
WHERE NOT EXISTS
(SELECT "X"
FROM ASIF2
WHERE ASIF.COLUMN_NAME = ASIF2.COLUMN_NAME
AND ASIF.TABLE_NAME = ASIF2.TABLE_NAME
AND ASIF.OWNER = ASIF2.OWNER)
UNION
SELECT ASIF2.OWNER, ASIF2.TABLE_NAME, ASIF2.COLUMN_NAME
FROM ASIF2
WHERE NOT EXISTS
(SELECT "X"
FROM ASIF
WHERE ASIF.COLUMN_NAME = ASIF2.COLUMN_NAME
AND ASIF.TABLE_NAME = ASIF2.TABLE_NAME
AND ASIF.OWNER = ASIF2.OWNER)
 
Thanks for the help guys.

Now I have another question. I am trying to generate a username and password
for the data that I get from this query. The username will contain the first
letter of the first five letters of the last name. The password will contain
the first letter of the first name and the first letter of the last name,
followed by the last 4 digits of the ID number. I am not sure how to create
the expression for this, or if this should be some sort of validation rule?

Thanks again!
 
Sorry, I'm not familiar with creating passwords. Try re-posting this question
in a new thread, and put this one to bed.

Sam
Thanks for the help guys.

Now I have another question. I am trying to generate a username and password
for the data that I get from this query. The username will contain the first
letter of the first five letters of the last name. The password will contain
the first letter of the first name and the first letter of the last name,
followed by the last 4 digits of the ID number. I am not sure how to create
the expression for this, or if this should be some sort of validation rule?

Thanks again!
I would make this part a subquery, as follows:
Click to expand...
[quoted text clipped - 33 lines]
Click to expand...
 
Back
Top