Query return on alias fields

[ClaraBarton]

I created a helper table for some small tables I.e. tblCombo for color, type, group, etc. So then I use Pat Hartman's Address Book. It worked great until I created the helper table.
Using her code to BuildSql I get:

SELECT tblCategory.CategoryID, tblCategory.Category,
    tblCombo.Data AS Type,
    tblCombo_1.Data AS [Group],
    tblCategory.Hide, tblCategory.Modified
FROM (tblCategory INNER JOIN tblCombo
    ON tblCategory.fTypeID = tblCombo.CmboID) INNER JOIN tblCombo AS tblCombo_1
    ON tblCategory.fGroupID = tblCombo_1.CmboID 
WHERE Left(Category,1) >= 'A' AND Left(Category,1) <= 'Z'
AND Hide = False ORDER BY Category

This doesn't work because both type and group are from the data column so to get a list box sorted on Type I need:

SELECT tblCategory.CategoryID, tblCategory.Category,
    tblCombo.Data AS Type,
    tblCombo_1.Data AS [Group],
    tblCategory.Hide, tblCategory.Modified
FROM (tblCategory INNER JOIN tblCombo ON tblCategory.fTypeID = tblCombo.CmboID)
    INNER JOIN tblCombo AS tblCombo_1 ON tblCategory.fGroupID = tblCombo_1.CmboID
WHERE (((tblCombo.Data)>='A' And (tblCombo.Data)<='Z') AND ((tblCategory.[Hide])=False))
ORDER BY tblCategory.Category;

In other words, the query does not work with an alias field. So both Type and Group end up with the same field. How can I fix this?

 

[June7]

What is tblCmbo supposed to help with?

Why is TypeID in tblCmbo? What purpose is OldID?

They may pull from same field but values should be different because of key links.

Want to provide sample data or your version of db?

 

[ClaraBarton]

OldID will be deleted when everything works. Also fCatTypeID and fCatGroupID in tblCategory.
It's called tblCombo because it's the basis of little combo boxes.
TypeID is not related to type in Description that I'm trying to pull above.

 

[Pat Hartman]

To use cbocombo, you need to create queries. A separate query to select each value of Description. You need a group query, a type query, a color query, etc. Then you join to whatever query you want for the list you want to see.

 

[ClaraBarton]

Thank you. Turns out it was trickier than I thought. You've simplified it.

 

[tvanstiphout]

It is not recommended to use Reserved Words like Type and Group for object names. https://learn.microsoft.com/en-us/office/troubleshoot/access/reserved-words

 

[Pat Hartman]

Thank you. Turns out it was trickier than I thought. You've simplified it.

You're welcome. It is a technique I use all the time because YEARS ago, I created a mini-app that I add to all new applications that manages ALL simple combo lists using two tables and two forms. A table of "tables" and a table with "table items". I started out with one self referencing table but that confused people when I posted the sample so I dumbed it down a little to use two tables instead. Remember, this solution is only for simple lists. If your "list" has other attributes, you need to manage it the old fashioned way using custom forms and tables.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

 

[ClaraBarton]

I'm attaching the form here. I can't figure out where to go on the SORT option. How do I fit the individual queries into the main one?

 

[June7]

What do you mean by "into the main one"? Your code prompts for inputs of Type and Group - why? Because Type and Group are alias names and they are not available for the ORDER BY clause in the VBA constructed SQL. Has to be:
strOrderBY = " ORDER BY tblCombo.Data, tblCategory.Category"
strOrderBy = " ORDER BY tblCombo.Data, tblCombo_1.Data, tblCategory.Category"

Or build a query object and save it then reference that query object as source to build SQL in VBA and then alias field names will be available. Consider:

SELECT tblCategory.CategoryID, tblCategory.Category, tblCategory.Hide, tblCategory.Modified,
tblCombo.CmboID AS CatTypeID, tblCombo.Data AS CatTypeData,
tblCombo_1.CmboID AS CatGroupID, tblCombo_1.Data AS CatGroupData
FROM tblCombo AS tblCombo_1 INNER JOIN (tblCombo INNER JOIN tblCategory ON tblCombo.CmboID = tblCategory.fTypeID) ON tblCombo_1.CmboID = tblCategory.fGroupID;

Why do some Category values have leading spaces?


NOTE: I changed db to OverlappingWindows so I could view tables and queries side-by-side.