SQL with 2 Joins for populating in Access

[ttechie]

this code works for me

SELECT Products.*, Companies.Company_Name FROM Companies INNER JOIN Products ON Products.product_com_code=Companies.Company_Code;

and this one also:

SELECT Products.*, Types.Type_Name FROM Types INNER JOIN Products ON Products.product_type_code=Types.Type_Code

what i need is to combine the two but i can't find the correct way to do complex joins

i basically need something like

SELECT Products.*, Types.Type_Name FROM Types INNER JOIN Products ON Products.product_type_code=Types.Type_Code, Companies.Company_Name FROM Companies INNER JOIN Products ON Products.product_com_code=Companies.Company_Code;

but i couldn't find the correct way to do this (using brackets)

can anyone help?

i am so very desperate and working against the clock

thanks

 

[PHV]

SELECT Products.*, Companies.Company_Name, Types.Type_Name
FROM (Products
INNER JOIN Companies ON Products.product_com_code = Companies.Company_Code)
INNER JOIN Types ON Products.product_type_code = Types.Type_Code;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ttechie]

thank you!

 

[ttechie]

i now have a problem that when i try to make a new entry with the form in which i used that code as the record source
and populated the combo boxes with the lines

SELECT Companies.Company_Name FROM Companies;

and

SELECT Types.Type_Name FROM Types;

as the row sources

it adds a new line to the products table but with a new product type code and a new company code which isn't one of the codes in the suitable tables (Companies and Types) which should represent the chosen name from the combo box

it also adds those new codes to the Companies table and the Types table (using the selected word form the combo box as the name and a new code for the automatic number code fields)

it obviously meant to just add a new entry to the products table using the correct code for the name of the company/type chosen in the combo boxes

i am so tired right now... i really hope that what i just wrote makes sense....


basically each time i add a new entry it is using a new code for the type and the company, adds the new codes and names to the companies and types tables... this is completely wrong, i now have entries with the same name but different codes in the Companies table and the types table

BTW
the codes are autonumber in the the companies table and the types table

i appreciate any help very much
this project is VERY urgent
which is why i am now posting this instead of sleeping

 

[PHV]

Why not simply follow the Combo wizard ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ttechie]

i now see that the wizard is using

SELECT Types.Type_Name, Types.Type_Code FROM Types;

instead of

SELECT Types.Type_Name FROM Types;

but that produced the same results, new company and type codes created

it must be some other thing in the wizard that i am missing... ?

 

[ttechie]

i found the solution in the northwinds example


already too late but at least it is done...