link tables many to many one unique field in each table

P

Pamela Creighton

Okay I know this is strange and maybe I am missing something simple
I have 3 table that I wish to combine into a simple query to export out to
excel
Table 1 has idfield, address one to many table
Fred anytown
Sam anytown
Table 2 is idfield, fruit, this is a many item same id field number
Fred apples
Fred grapes
Sam apple
Sam orange
Table 3 is idfield, colour, this is a many item same idfield number
Fred red
Fred blue
Fred purple
Sam green
Sam blue

If I create a query or append to temp table I get the following
Fred anytown apple {blank}
Fred anytown grape {blank}
Fred anytown {blank} red
Fred anytown {blank} blue
Fred anytown {blank} purple

If create a reg query I get
Fred anytown apple red
Fred anytown apple blue
Fred anytown apple pruple
Fred anytown grape red
Fred anytown grape blue
Fred anytown grape purple

What I am trying to get is a compacted list that does not create blanks or
a way to supress the blanks
Table 1 info Table 2 info Table 3 info
Fred anytown apple red
Fred anytown grape blue
Fred anytown {blank} purple

Any help suggestions greatly appreciated
 
D

Duane Hookom

You aren't missing something simple. The method I would try involves
creating queries from table 2 and table 3 that add a sequence field that
numbers fruit like

Fred apples 1
Fred grapes 2
Sam apple 1
Sam orange 2

and

Fred red 1
Fred blue 2
Fred purple 3
Sam green 1
Sam blue 2

Then you might need to create a union query of the names and sequences to
make sure you have all sequence values for all names. Then create a query
with table 1 the union query, and the two sequence queries. Join the names
and sequences making sure you include all the records from the union query.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top