having trouble with a somewhat complex query...

[KMI123]

There are 4 tables that I am working with for this query. Recommendation, Section, Category, and SubCategory.

I have a form allowing the user to categorize recommendations by section, category, and subcategory using cascading combo boxes. The ID (either a number or letter) is then stored in the recommendations table.

I now need to somehow run a query listing the recommendations and the NAMES of the Section, Category, and SubCategory which are stored in those tables. This seems like it should be so simple but access just does not want to cooperate. haha.

I am able to run queries for each one seperately and I can also run one to show the recommendations with both the Section and the Category, but once I get to the SubCategory it doesn't seem to understand me anymore.

Any ideas?? Thanks in advance!!

~Kristin

 

[Remou]

It is always best to post the SQL you are having problems with.
Have you added lookup fields to your tables?

 

[rjoubert]

Can you post the structure of your "Recommendation" table? I'm assuming this table has a number/letter that corresponds to the index of the subcategory table? Do the datatypes match?

 

[KMI123]

ok, sorry...this is my first time with forums, let me try to explain a little bit better...

In my recommendations table i have 3 fields as my primary keys (DocumentID, RecOrder, RecOrderAlpha). I then have the following fields/datatypes in addition to a Recommendation field (Memo).

1) a Section field (datatype is number) in the recommendation table relating to the SectionID in the section table

2) a Category field (datatype is text) in the recommendation table relating to the CategoryID in the category table

3) a SubCategory field (datatype is number) in the recommendations table relating to the SubCategoryID in the subcategory table

The Section table has a primary key (SectionID) and a Section field (which is the description).

The Category table has 2 primary keys (SectionID and CategoryID) and a Category field.

The SubCategory table has 3 primary keys (SectionID, CategoryID, and SubCategoryID) and a SubCategory field.

In my form, when the Section, Category, Subcategory is chosen, the related ID is stored in the recommendations table.

As for the SQL, I will try to post something in a couple minutes...


Hopefully this is making some sort of sense??

 

[Remou]

I think you may have run into:

Did you use the dreaded anti-feature of Lookup Fields in tables?

http://www.mvps.org/access/lookupfields.htm

 

[KMI123]

alright, not sure if this will help or not but here is the SQL for a query I have working. This one will display the recommendation along with both the Section and Category names. Now I just need to somehow get the SubCategory names in there...


SELECT tbl_Recommendations.AuditID, tbl_Recommendations.Recommendation, tbl_Section.Section, tbl_Category.Category

FROM tbl_Section RIGHT JOIN (tbl_Recommendations LEFT JOIN tbl_Category ON (tbl_Recommendations.Section = tbl_Category.SectionID) AND (tbl_Recommendations.Category = tbl_Category.CategoryID)) ON tbl_Section.SectionID = tbl_Recommendations.Section

GROUP BY tbl_Recommendations.AuditID, tbl_Recommendations.Recommendation, tbl_Section.Section, tbl_Category.Category, tbl_Recommendations.Category


i really hope i'm not confusing anyone...thanks so much...

 

[KMI123]

i will read up on lookup fields in tables and see if that helps...thanks

 

[lespaul]

Ok, first off, you only need to use RIGHT and LEFT joins when there's the chance that there is information in one table that may not exist in the other. If your tables are normalized and set up with valid relationships, you shouldn't need those, INNER JOINS will work just fine.

Read the JOIN link below for more information on what they are and how they work.

Secondly, you don't really need a GROUP BY clause if you are not using aggregate functions like COUNT, SUM, AVG, etc.

How about something like:

SELECT R.AuditID, R.Recommendation. S.Section. C.Category, SC.Name
FROM ((tbl_Recommendations R
INNER JOIN tbl_Section S on R.Section = S.SectionID)
INNER JOIN tbl_Category C on R.Section = C.SectionID AND R.Category = C.CategoryID)
INNER JOIN tbl_SubCategory SC on R.Section = SC.SectionID AND R.Category = SC.CategoryID AND R.SubCategory = SC.SubCategoryID

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

You wanted something like this ?
SELECT R.AuditID, R.Recommendation, S.Section, C.Category, SC.SubCategory
FROM ((tbl_Recommendations AS R
LEFT JOIN tbl_Section AS S ON R.Section = S.SectionID)
LEFT JOIN tbl_Category AS C ON R.Section = C.SectionID AND R.Category = C.CategoryID)
LEFT JOIN tbl_SubCategory AS SC ON R.Section = SC.SectionID AND R.Category = SC.CategoryID AND R.SubCategory = SC.SubCategoryID

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

 

[KMI123]

thank you SO much PHV! that's exactly what I was looking for. Leslie - I didn't get to try yours but I will do that to and read up on Joins at that link you sent.

Thanks again! I really appreciate the help