Software
Using Microsoft Access I am trying to compare two tables to find matches based on several criteria. How can I do this and where can I find information on how this is done.
Thanks,
John Starnes
by dklippert
This is the definition of a query. Check the Help file or just play with it.
Create a new query based on a couple of tables.
It works best if there is a common element between them; like a Porduct number or Employee ID. If you can "Join" the tables you can now enter the world of Left,Right, Inner and Outer joins.
(Start with a simple query)
by johntstarnes
by bob sellman
Here's the process (look in Access help under the topic Query for more info). I'll assume TableA with columns ColA1, ColA2, ColA3, etc., and TableB with columns ColB1, ColB2, ColB3, etc., where the columns may be the same or different types/data/etc.
Let's assume that ColA1 should equal ColB2 and ColA5 should equal ColB5. (You can use more column matches if necessary.) These pairs of columns should of course be the same types.
Click Query on the Database window, then click New.
Select the two tables (TableA and TableB) from the list of tables, then close the table selection box.
Click and hold on ColA1 in the TableA column list and drag the pointer over to ColB2 and let go of the mouse button. A line will now be drawn between the two. Do the same for ColA5 and ColB5. You can double click each line to get details of the linking parameters for each pair and set as appropriate.
Drag the columns you want to see as a result of this query into the query grid. Set sorting parameters as you want.
To test the query, click the button on the top with the asterisk (!) to run the query.
This is the core power of a relational database. TableA could be your detailed data with ColA5 containing a code (like Social Security Number), and TableB could be a list of people, with ColB1 containing the SSN). By just matching ColA5 to ColB1 you can now see in the query the names, addresses, etc. of each person identified by the SSN.
by johntstarnes
by sl-campbell
Go to the Query window and start the query designer with-out selecting any tables(close the table selection window)
Under the menu point 'View' select SQL.
In this window is where you can define your query. You need to enter the tables to compare, the fields that you want the query to return, and the fields to set criteria on, (the criteria is the based on the fields in both tables that you want to compare on.
The following example compares 2 tables, based on three fields and returns those 3 fields. You use an inner join here so that the query returns only the records that are found in both tables and match the criteria. In the criteria I deliberatly used two fields with diffrent names, but normally the would have the exact same name.
SELECT PERS.PersID, Dept.DepartmentNr
FROM PERS
INNER JOIN DEPT ON PERS.PersID = Dept.ResponsiblePersNr;
You could add additional criteria, maybe excluding a department:
SELECT PERS.PersID, DEPT.DepartmentNr
FROM PERS
INNER JOIN DEPT ON PERS.PersID = DEPT.ResponsiblePersNr
WHERE (DEPT.DepartmentNr)<>"123");
Or you could find a comparison over several tables using several Joins, (the person has to exist in all tables):
SELECT PERS.PersID, DEPT.DepartmentNr,SECTION.SectionNr
FROM (PERS
INNER JOIN DEPT ON PERS.PersID = DEPT.ResponsiblePersNr)
INNER JOIN DEPT ON PERS.PersID = SECTION.ResponsiblePersNr)
WHERE (DEPT.DepartmentNr)<>"123") AND (SECTION.SectionNr)<>"456") ;
by sl-campbell
by johntstarnes
by johntstarnes
This discussion is closed to new replies.
Sort by Trending