Query that combines fields from mother table and child tables

[lios1984]

Hello everyone. I desperately need your help.

Suppose I have a mother table with 600 records and 4-5 child tables that have a subsum of the 600 records of the mother table. For example, I have 600 books as mother table and another child table Bought that contains 80 of the 600 books.

What I want to do is this:
Create a query that takes 3 primary fields from mother table, 2 from Child1table 2 from Child2table and 2 from Child3table. My problem is that, since there is a certain relationship among tables, I can't have all the 600 records in the query.

Example of a record of the query:

Primary1 Primary2 Child1table1 Child1table2 Child1table2 Child2table2
Title1 Author1 Purchdate1 PurchPrice1 2ndeditdate 2ndeditprice

.....

Title600 Author600 Purchdate600 PurchPrice600 2ndeditdate600 2ndeditprice600

If for a certain Title a record can't be found in a table, e.g Child1table, I want that value to be shown as null in the query.


Any ideas??

 

[stopher]

Sounds like you need to implement a LEFT JOIN in your query.

Assuming you are doing this via the query designer, create the query as normal i.e. your parent table with a join to each child table.

Then double click on one of the join lines and a dialogue box will open (make sure your mouse it exactly on the line or else it won't work).

You should see an option to include all records from the parent table and only records from the child where they match. Click this option and do the same for the other joins.

That's it.

Chris

 

[lios1984]

Amazing how easy it was!!!! I was about to get into VBA code thinking I couldn't do it any other way. Thank you very much stopher!!!