JOIN 4 tables!? (1 Viewer)

[danb]

Hi,

I have the following four tables:

tblGroup:

Group_Id (PK) | Group_Name


tblSubGroup:

SubGroup_Id (PK) | Group_Id (FK) | SubGroup_Name


tblProductType:

ProductType_Id (PK) | SubGroup_Id (FK) | ProductType_Name


tblProduct:

Product_Id (PK) | ProductType_Id (FK) | Product_Name




I need to select a single Product_Name (first one which appear alphabetically) from tblProducts given a Group_Id.

Is this possible? Presumably I need to join the tables in between?

If anyone can help with this it would be much appreciated, thanks...

 

[JohnD]

I just went through something similar, maybe this will help,

http://www.access-programmers.co.uk/forums/showthread.php?t=98985

John D

 

[danb]

Hmm... so I don't need to JOIN the tables? I've never used UNION before.

 

[RuralGuy]

Dan,
In your case you will need to join the tables. Have you played in the Query Builder yet? Put your tables in the grid and join them and add your criteria and then look at the DataSheet view to see what you get.

 

[danb]

Having just read http://www.w3schools.com/sql/sql_union.asp it seems that UNION is to select values from different tables. This isn't what I want. I need to select a value from the tblProduct table where the product belongs only to a particular group (I will pass Group_Id into the select statement). I'm pretty sure I'm going to have to use joins for this no?

Anyone know how to perform a join on these four tables to get the Product_Name based on GroupId?

Thanks for your help

 

[danb]

I had this quite recently, where someone tried to point me in the direction of querybuilder (which which I'm totally unfamiliar). Thanks for suggesting this, but I really just need a SQL select statement which I can use in my ASP page to select the data from the Access tables.

I honestly don't have time to get to grips with querybuilder at the moment.

 

[RuralGuy]

The QueryBuilder is *very* easy to work with and once you get the results you desire you switch to SQL view and just copy it.

 

[danb]

Thanks RuralGuy,

Like most things, I'm sure it's the easiest thing in the world if you know how to do it. I've just opened up querybuilder and I can't do it.

Rather than ask you to explain to me how querybuilder works, I'll probably just try to do more Googling for multiple inner joins. Will probably save us both time.

Thanks anyhow, I suspected it was going to come down to this, but hoped someone would know the syntax for this kind of operation.

 

[Pat Hartman]

danb, try the query builder again. You need to make it your friend.
1. Add the three tables you need. You don't need tblGroup unless you want the group name.
2. Click on the join field of one table and drag it to the coresponding field in the next table.
3. Select the columns you want from each table.
4. Add criteria to the criteria row.