Reid McCamish Reid McCamish - 1 year ago 109
SQL Question

Why would Access spontaneously start displaying a non-existant table in a query?

I updated a simple Access select query, adding four fields from the single table the query uses. When I then attempted to edit data in the form that references that query, Access did not allow any edits. After poking around at other possible edit rights problems I went back to the query design, and saw this:

enter image description here

The table CC_Tracker_1 does not exist in the database, nor is there a query by that name, as you can see in the object list:

enter image description here

In the design view, CC_Tracker_1 displays as an exact copy of CC_Tracker. Additionally, the four fields I added had CC_Tracker_1 listed as their table of origin.

Deleting CC_Tracker_1 from the query and re-adding the fields from CC_Tracker fixed the problem, but I'm very curious as to how this could happen.

Edit to add: This is the SQL Access generated. As you can see the alias it created isn't used anywhere in the code except in the variable list. Why it would do this is still the question:

SELECT CC_Tracker.LAST_NAME, CC_Tracker.FIRST_NAME, CC_Tracker.MRN, CC_Tracker.RIN,
CC_Tracker.SUBSCRIBER_ID, CC_Tracker.ASSIGNED, CC_Tracker.Letter, CC_Tracker.[1stCall],
CC_Tracker.CHRA, CC_Tracker.[ICP/Review], CC_Tracker.F2F, CC_Tracker.ICTCont,
CC_Tracker.ICTSheet, CC_Tracker.ICP2MD, CC_Tracker.SigPg, CC_Tracker.HTR_1st_cont,
CC_Tracker.HTR_2nd_cont, CC_Tracker.HTR_3rd_cont, CC_Tracker.HTR_Letter,
CC_Tracker.CLOSE, CC_Tracker.Comments, CC_Tracker.CHRA_Next, CC_Tracker.ICP_Next,
CC_Tracker.F2F_Next, CC_Tracker.ICT_Next, CC_Tracker_1.Final_Follow, CC_Tracker_1.BH_SA,
CC_Tracker_1.Readmitter, CC_Tracker_1.CCM, CC_Tracker.ASSIGNED_CARE_COORDINATOR, CC_Tracker.Final_Follow
FROM CC_Tracker, CC_Tracker AS CC_Tracker_1
WHERE (((CC_Tracker.ASSIGNED_CARE_COORDINATOR)=[Forms]![frmCoord_Selector]![cmbCoords]))
Like [Forms]![frmCoord_Selector]![cmbCoords]) Is Null));

Answer Source

If you look at the SQL that is generated for the query, you will see that it has created an alias called CC_Tracker_1 for the CC_Tracker table.

Your query may need to be rewritten to exclude this alias, and any incorrect joins.

If you post the actual query generated, we will be able to help fix it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download