Unanswered: Query results now contain tablename.fieldname
I had a small application (and it worked) that performed a series of calculations and totals using queries to track inventory information and pending orders and forecasts. There are a few nested queries (qry001 feeds into qry002 etc) and at the end I join the results with some tables for reporting. It worked and has for some time.
Now with a request to make a change - I made a change in the earlier queries - but there was no change in the resulting field outputs. I replaced one query [qry010] with three (two with unique selection criteria [qry005 and qry008] and then merged them together with a Union query [new qry010] to still have the data on the same report). But the query qry015 that takes input from qry010 (previously the old and now the new) suddenly appends the tablename to the fieldname. Each query has the field brand and qry010 has one output field called [brand]. The query qry015 has inputs of a table BRAND (which contains the fieldname brand) and the qry010 which has the fieldname of brand. Now that I have touched this - qry015 results include the column "BRAND.Brand" where previously the results were simply "Brand".
When I try to rename the column with a new different name - it works - but then I have to alter the subsequent query and the report to use this renamed data element. If I try to rename the column back to brand - the output is a column with no data.
I have a feeling that I am missing something basic - but am drawing a blank right now. I have even tried reverting back to the version of the db before I added the new queries and the same results occur.
I'm a little lost with all the nesting you have going on here, but I get the feeling you're looking at the query designer and seeing the fully qualified field name. This is correct. The source table shouldn't be visible in result set at all.
for example, say have I have a table:
NOw I query table test like this:
This will produce a recordset with a column named field1.
I will get the exact same recordset with this query:
I won't be able to tell the difference between the two, the output will be identicle...
The contents of the result set are the same - however when I try to use the output and need to refer to the fieldnames is my problem. The data is fine and correct - but I use these fieldnames in reports.
Prior to this change I simply referred to field1 whereas now I would need to specify tblTest.field1. It was an automated change that Access made to the query - not one that I initiated.
A simplified restatement of my problem follows:
tblTest1 contains three fields
tblTest2 contains numerous fields - but starts with
Previously this worked
SELECT tblTest1.Brand, tblTest1.field2, tblTest1.field3, tblTest2.field5
FROM tblTest1 INNER JOIN tblTest2 ON tblTest1.Brand = tblTest2.Brand;
The output was called: Brand, field2, field3, field5
Now something triggered the output to be called:
tblTest1.Brand, field2, field3, field5
It is as if Access believes tblTest2.brand is another column in the resulting recordset - then I would understand why it would prefix the table - but it is not.
I would like to find out what caused the tablename prefix to be added to the output column name.
"wacky" is probably the nicest word I would use to describe this.
No errant AS statements in SQL.
Here is the actual SQL:
SELECT BRAND.Brand, qry070InventoryAndPending.Loc, qry070InventoryAndPending.Item,
qry080PeriodForecasts.[Item Short Description], qry070InventoryAndPending.OH, qry070InventoryAndPending.Proj, qry070InventoryAndPending.Sold, qry070InventoryAndPending.PendingMTD, qry080PeriodForecasts.SumOfFcstQty, qry080PeriodForecasts.Per1FcstQty, qry080PeriodForecasts.Per2FcstQty, qry080PeriodForecasts.Per3FcstQty
FROM BRAND INNER JOIN (qry070InventoryAndPending INNER JOIN qry080PeriodForecasts ON (qry070InventoryAndPending.[Item Long Description] = qry080PeriodForecasts.[Item Short Description]) AND (qry070InventoryAndPending.Loc = qry080PeriodForecasts.Loc) AND (qry070InventoryAndPending.Item = qry080PeriodForecasts.Item) AND (qry070InventoryAndPending.Brand = qry080PeriodForecasts.Brand)) ON BRAND.Brand = qry070InventoryAndPending.Brand;
And the odd part is - this query has not changed. I modified qry070InventoryAndPendingItem today - and the result set from that query shows a column labeled Brand - while the output of the query above labels the column BRAND.Brand
FYI - I inherited this DB - naming conventions and all. BRAND is a table and Brand is a data element in the table and a characteristic of the item in the other queries.
Well I tried changing enough stuff that I went back to the beginning and changed the name of the table such that the tablename and the fieldname were not identical. For the moment that seems to have resolved the issue - but the thing that gets me is that it worked like that and then suddenly stopped. I would love to figure out what caused this change - but at this point don't know where to look.