access query help

[kodaksmile]

Is there a way to get results back from the following scenario:

I have a table tbl1 that has a field1 which is the link to 2 other tables - tbl2 and tbl3. tbl2 contains 3 rows for this field1 and tbl3 contains 2 rows. I need to get a total of 3 rows back but, there is no link between tbl2 and tbl3 except field1. I can't do a union because the number of rows returned are different and if I just to a select with joins I get 6 rows back instead of 3. Any suggestions?

 

[dhookom]

I would expect that your relationship is something similar to a tbl1 made up of unique households with a primary key of HHID. tbl2 is the list of occupants with a foreign key that stores the HHID. tbl3 is a list of pets with a foreign key that stores the HHID. There is no relationship between pets and occupants. If this is correct then there isn't a method of creating a single query that displays households with occupants and pets. This can be easily displayed in forms with subforms or reports with subreports.

Duane
MS Access MVP

 

[kodaksmile]

I was hoping there was another way and I was missing it but thanks for confirming my guess.