query theory

[Guest_imported]

Hi!
i have 1 record id table on the one side of a one to many relationship. I have 4 tables on the many side. the record id table has a record id that is connected to the other four tables which are address, contact, company, notes.
i include all 5 tables in the query with the intention of getting a list of all my contacts. The reason I have it set up like this is because one person may work for 2 companies or have many people listed under one company. With this set up I can set the default contact and default address. Also the record id defines the unigueness rather than a name or company. The result is one record or entity can have any number or combination of people, companies and addresses.

well when i run the query nothing shows up. I'm assuming it has something to do witht the fact that the query is displaying only information that is unique in all instances.
What i want is when i put the record id table in the qbe grid, and add the address, contact and notes tables in the qbe grid as well, i expect to see every record listed with all the addresses, contacts and notes that have been entered under that record id. but nothing shows up.
can someone help me?, I can send you the file if you want.
cheers and thanks bunches in advance.

 

[UKRacer]

Try this -

If I have understood you correctly, you may be asking the query to display matching results from 5 tables (1 main table linked to 4 others through the id you have set up), when there may not be a single record in the main table which has a match in EACH OF THE OTHER 4.

The solution here is to change the joins within the query from INNER to LEFT, or RIGHT.

To do this, go to the query design window; double-click each join from the main table to each of the other four. In the properties box that pops up you will be able to "Include ALL from table 1 and only those records from table 2 where the joined fields are equal" (obviously the table names will be different for you, but you'll see the idea).

Do this for each join, and you will see every record in the main table, and the data from matching records in the other 4 tables.

Hope this helps,

Pete

 

[Guest_imported]

WOOOHOOOOOOO!!! it worked thanks for the tip pete. I'll look into those options so I can take advantage of them.
you da man
cheers!

 

[WildHare]

Shameless self-promotion department:

FAQ700-1681



Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm