Old queries have entries like "Table_field: field" - ???

DennisD

New member
Local time
Today, 13:15
Joined
Jan 7, 2014
Messages
6
I am trying to figure out, and then update, an old database that uses a Query to populate a Report. In the SQL Statement: Query Builder that is used to populate the report, two of the fields don't have regular entries that correlate to fields on a table, but have a sort of "composite" or "built-up" entry of the format: Table_Field: Field. I can't figure out what this is or why it is different, BUT IT WORKS!!! So obviously it isn't a typo, it was entered on purpose, and I'd like to figure out WHY.

The basic format of the database is two independent tables, let's call them tReports and tRequirements. A third table connects these two, and each report can be associated with multiple requirements, and each requirement can be associated with multiple reports. A fourth table, tCategory, is "on top" of the requirements table to divide the requirements into, duh, categories. Categories have a 1 to many relationship to Requirements.

The report prints out a page for each report with the requirements listed for that report, and the requirements are divided into categories.

For the field in the Query Builder where I would expect the Field to be "CategoryName" and the Table to be tCategory, the Table is as expected, but the Field has instead "tCategory_CategoryName: CategoryName".

If I use a drop down to just select CategoryName, the report doesn't work right, it asks me which CategoryName I want when I open the report, and obviously something is wrong.

Can anyone tell me what this is? I've tried searching "_", query codes, and other things but I can't link in to the right name to even learn more about this. Thanks in advance!
 
For whatever the reason, the developer gave the field name an alias, which is what appears before the colon. The report is looking for the alias name, not the actual field name. If you switch the query to SQL view, you'd see:

CategoryName AS tCategory_CategoryName

It's a little unusual to use them for a regular field name. More commonly you'd use it for a calculation or concatenation, like

FullName: LastName & ", " & FirstName
 
Thanks Paul!

Exactly as you stated, when I look at SQL view, I see just what you posted.

So the way to get rid of this (I'm trying to make it "cleaner") is to find where in the report it calls for the alias name and switch it back to a field name? Anything else I need to watch out for?
 
I'm new to SQL view, but I do a little VBA and other programming. When I'm looking at other details in SQL view, some have Table.Field and others have Table.[Field]. What is the reason for the [] brackets?
 
PMFJI,

The [] brackets are necessary when a field name has an embedded spaces or special characters.
The standard/normal/suggested characters are alphanumeric and "_".

Good luck
 
Thanks Paul and jdraw! I cleaned up the field names and got rid of the spaces, and that got rid of the [] brackets.

I got rid of the alias, and found the two places in the report (text boxes in the report, as well as the sorting and grouping) that referenced the alias.

And it all still works!!! Thanks!
 
Last edited:

Users who are viewing this thread

Back
Top Bottom