link tables many to many one unique field in each table

  • Thread starter Thread starter Pamela Creighton
  • Start date Start date
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
 
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.
 
Back
Top