pulling a report from 3 tables

[joewy]

i am very new to access and need help pulling info from 3 tables, all three
tables have- id, last name, first name and DOB
i would like the report to end up like this
dismiss all records that have matching rows on all 3 tables
list all records that have no matches
mark the records from table1 that match either table2 or table 3

best case scenerio...
last name first name DOB table 2
table3
smith john 1-1-2001 true
smith jane 2-2-2002
true
smith fred 3-3-2003

where john was in table2 not table 3
where jane was in table3 not in table2
where fred was only in table1

thanks so much for any help
joewy

 

[Douglas J. Steele]

You should be able to set up Left Joins between the tables.

Drag all 3 tables onto the grid. If the correct relationship lines don't
exist between the tables, add them by dragging the appropriate field(s) from
table 1 to table 2 and from table 1 to table 3. Make sure there aren't
relationship lines between table 2 and table 3.

Highlight the relationship line(s) between table 1 and table 2, and
right-click to get the join properties. Choose the radio button
corresponding to "Include ALL records from 'Table 1' and only those records
from 'Table 2' where the joined fields are equal."

Highlight the relationship line(s) between table 1 and table 3, and
right-click to get the join properties. Choose the radio button
corresponding to "Include ALL records from 'Table 1' and only those records
from 'Table 3' where the joined fields are equal."

Drag your fields from table 1 into the grid. Add two fields at the end:
Table2: IIf(IsNull([Table 2].[Id], "", "True") and
Table3: IIf(IsNull([Table 3].[Id], "", "True")

 

[John Spencer (MVP)]

Does any one of the tables have ALL the rows? In other words does table 1 have
all the rows and table 2 and table 3 are subsets of table 1? If not, then the
problem is a bit more complex as you need to get a master listing of all the
data.

SELECT [Last Name], [First Name], DOB, "T1" as SourceName, "X" as Marker
FROM Table1
UNION ALL
SELECT [Last Name], [First Name], DOB, "T2", "X"
FROM Table2
UNION ALL
SELECT [Last Name], [First Name], DOB, "T3", "X"
FROM Table3

Save that as the source query for a crosstab query. Where you use Last Name,
First Name, and DOB as Rows; SourceName as a Column, and Max(Marker) as the
value to display in the column

You cannot build the UNION query in the grid. You have to do that by typing the
text into the SQL view of the query.

You can use criteria to eliminate any rows that have an "X" in all three columns (T1,T2,T3)