table joins problem

[Annoscia85]

Hi All,

This has been bugging me for a few day's.

I want to join 4 tables together, these are:
1 - Costing_Main
2 - Costing_Sub
3 - Machine_Charge_Out
4 - Material_Cost

I can join the first 3 tables together no problem, and they return the columns I need to view, but when i add the final table (Material_Cost) to the SQL code no data from any of my tables are returned, but as soon as i take the Material_Cost away, its works again.

This is my SQL code for my query in access.

SELECT Machine_Charge_Out.Machine_Type, Costing_Sub.Qty_Price_Break, Costing_Main.Enquiry_No, Costing_Main.Current_Date, Material_Cost.Weight_Per1000
FROM Material_Cost INNER JOIN (Costing_Main INNER JOIN (Costing_Sub INNER JOIN Machine_Charge_Out ON Costing_Sub.Machine_Type = Machine_Charge_Out.Machine_Type) ON Costing_Main.Enquiry_No = Costing_Sub.Enquiry_No) ON Material_Cost.Part_No_length = Machine_Charge_Out.Hourly_Rate;

Does anybody have any idea's?

Thanks in advance

 

[plog]

You probably need to add the Material_Cost table with a LEFT JOIN instead of an INNER JOIN. Most likely the Material_Cost table doesn't have records for however you are joining it. By doing a LEFT JOIN you will pull in all records from the first 3 tables and then any data that does match in the Material_Cost table.

Here's how to do that LEFT JOIN: In design view add the Material_Cost table just like you did before, set up the link you had before and then right-click on the line that symbolizes the join, in the dialog that pops up click on 'Join Properties' and then check the box that says something like "...and only those records from 'Material_Cost' where the joined fields are equal."

Then run that query and you should get the results you expected.

 

[Annoscia85]

hi,

sorry its taken so long for me to reply.

Thats works great, thank you, but it still doesnt show anything from the 4th table when they're all joined together.

Ive attached an screen shot to show my explanation.

Many thanks

 

[gemma-the-husky]

you don't have to put everything into one query.

try joining the material cost to the relavant table, first, in a separate query- and then include that query in your final query.

you should be able to get to the result you want by building up gradually.

 

[Annoscia85]

Hi,

All is good and working,

many thanks for your responses

 

[asgard]

Hi,

All is good and working,

many thanks for your responses

This helped me too