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.

 

[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

 

[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.