Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Location
    Princeton, NJ USA
    Posts
    13

    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.

    Thanks in advance for any suggestions.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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:

    tblTest
    ---------
    field1
    field2

    NOw I query table test like this:

    SELECT field1
    FROM tblTest

    This will produce a recordset with a column named field1.

    I will get the exact same recordset with this query:

    SELECT tblTest.field1
    FROM tblTest

    I won't be able to tell the difference between the two, the output will be identicle...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2005
    Location
    Princeton, NJ USA
    Posts
    13
    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
    Brand
    field2
    field3

    tblTest2 contains numerous fields - but starts with
    Brand
    field4
    field5 etc

    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.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    wacky, you shouldn't ever see the prefix in the output.

    Poke around for an errant "AS" identifier in the underlying SQL for your query. See if something is explicitly defining the field name to be a literal:

    SELECT tbl1Test.Brand AS "tblTest1.Brand"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2005
    Location
    Princeton, NJ USA
    Posts
    13
    "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.

  6. #6
    Join Date
    Feb 2005
    Location
    Princeton, NJ USA
    Posts
    13
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •