?
Solved

Access SQL Query Error 3079: Specified field could refer to more than one table listed

Posted on 2010-01-06
19
Medium Priority
?
1,975 Views
Last Modified: 2013-11-29
So I think I'm using fully qualified field names (by including the table names and brackets and all) everywhere, but it's still giving me this message.

What's going on? Normally fully-qualified names fix this.

What am I shooting for?
I'm trying to make a query to update the data in ModelTaskDataLocal from a more recent table qryModelDataPPQ according to the instructions at:
http://office.microsoft.com/en-us/access/ha100765271033.aspx
UPDATE 
[ModelTaskDataLocal] INNER JOIN [qryModelDataPPQ] 
ON 
([ModelTaskDataLocal].[PM_FREQUENCY] = [qryModelDataPPQ].[PM_FREQUENCY_DAYS]) AND 
([ModelTaskDataLocal].[TIME_STAMP] = [qryModelDataPPQ].[TIME_STAMP]) AND 
([ModelTaskDataLocal].[ModelData_MODEL_WO_NBR] = [qryModelDataPPQ].[MODEL_WO_NUMBER]) AND 
([ModelTaskDataLocal].[PM_LATEST_DATE] = [qryModelDataPPQ].[PM_LATEST_DATE]) AND 
([ModelTaskDataLocal].[PM_EARLY_DATE] = [qryModelDataPPQ].[PM_EARLY_DATE]) AND 
([ModelTaskDataLocal].[PM_DUE_DATE] = [qryModelDataPPQ].[PM_DUE_DATE]) AND 
([ModelTaskDataLocal].[PM_CAT_CD] = [qryModelDataPPQ].[PM_CAT_CD]) AND 
([ModelTaskDataLocal].[PM_STATUS] = [qryModelDataPPQ].[PM_STATUS]) AND 
([ModelTaskDataLocal].[PM_TITLE] = [qryModelDataPPQ].[PM_TITLE]) AND 
([ModelTaskDataLocal].[PM_FREQUENCY_CODE] = [qryModelDataPPQ].[PM_FREQUENCY_CODE]) AND 
([ModelTaskDataLocal].[ModelData_PM_RQ_NUMBER] = [qryModelDataPPQ].[PM_RQ_NUMBER]) AND 
([ModelTaskDataLocal].[ModelData_PM_ID_NUMBER] = [qryModelDataPPQ].[PM_ID_NUMBER]) 
SET 
[ModelTaskDataLocal].[ModelData_PM_ID_NUMBER] = [qryModelDataPPQ].[PM_ID_NUMBER], 
[ModelTaskDataLocal].[ModelData_PM_RQ_NUMBER] = [qryModelDataPPQ].[PM_RQ_NUMBER], 
[ModelTaskDataLocal].[ModelData_MODEL_WO_NBR] = [qryModelDataPPQ].[MODEL_WO_NBR], 
[ModelTaskDataLocal].[PM_FREQUENCY_CODE] = [qryModelDataPPQ].[PM_FREQUENCY_CODE], 
[ModelTaskDataLocal].[PM_FREQUENCY] = [qryModelDataPPQ].[PM_FREQUENCY_DAYS], 
[ModelTaskDataLocal].[PM_TITLE] = [qryModelDataPPQ].[PM_TITLE], 
[ModelTaskDataLocal].[PM_STATUS] = [qryModelDataPPQ].[PM_STATUS], 
[ModelTaskDataLocal].[PM_CAT_CD] = [qryModelDataPPQ].[PM_CAT_CD], 
[ModelTaskDataLocal].[PM_DUE_DATE] = [qryModelDataPPQ].[PM_DUE_DATE], 
[ModelTaskDataLocal].[PM_EARLY_DATE] = [qryModelDataPPQ].[PM_EARLY_DATE], 
[ModelTaskDataLocal].[PM_LATEST_DATE] = [qryModelDataPPQ].[PM_LATEST_DATE], 
[ModelTaskDataLocal].[TIME_STAMP] = [qryModelDataPPQ].[TIME_STAMP];

Open in new window

Err.bmp
Map.bmp
0
Comment
Question by:travisjbennett
  • 10
  • 7
  • 2
19 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26192449
SQL is optimizing your query. Its found that by opening your qryModelDataPPQ query and creating its optimized version of your SQL logic, it found an unqualified reference.

can you show us the SQL for this query? qryModelDataPPQ

J
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 300 total points
ID: 26192551
I agree with jeff.  Most likely the field name in qryModelDataPPQ is different than what you might think, and you have to put it into the higher-level query the same way.  Another possibility is to alias the field name in qryModelDataPPQ, to avoid having to include the table name in other queries that need that field.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26192560
It might actually be easier to do the updating in code, using DAO recordsets.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:travisjbennett
ID: 26192579
No, not with 500,000 records. I'll post that query in a minute.
0
 
LVL 1

Author Comment

by:travisjbennett
ID: 26192672
qryModelDataPPQ is below
SELECT
  E.PM_ID_NUMBER,
  E.PM_RQ_NUMBER,
  E.PM_FREQUENCY_CODE,
  E.PM_FREQUENCY_DAYS,
  E.PM_TITLE,
  E.PM_STATUS,
  E.PM_CAT_CD,
  E.PM_DUE_DATE,
  E.PM_EARLY_DATE,
  E.PM_LATEST_DATE,
  E.MODEL_WO_NUMBER,
  E.TIME_STAMP

FROM  ETTP.TIDECPMS E

WHERE E.FACILITY = 'TEST' 

Open in new window

0
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 1700 total points
ID: 26192732
Helen's right. Either Alias the E.MODEL_WO_NUMBER field  as something else, or remove it if you can. It is a duplicate name as one in your ModelTaskDataLocal table.
J
0
 
LVL 1

Author Comment

by:travisjbennett
ID: 26192743
How do I alias it?
0
 
LVL 1

Author Comment

by:travisjbennett
ID: 26193212
Could you give me an example of how to accomplish this with an aliases?
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 1700 total points
ID: 26193712
SELECT
  E.PM_ID_NUMBER,
  E.PM_RQ_NUMBER,
  E.PM_FREQUENCY_CODE,
  E.PM_FREQUENCY_DAYS,
  E.PM_TITLE,
  E.PM_STATUS,
  E.PM_CAT_CD,
  E.PM_DUE_DATE,
  E.PM_EARLY_DATE,
  E.PM_LATEST_DATE,
  E.MODEL_WO_NUMBER as E_Model_WO_NUMBER,
  E.TIME_STAMP
 
FROM  ETTP.TIDECPMS E
 
WHERE E.FACILITY = 'TEST'
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26193747
That would change this query to look like so

UPDATE  
[ModelTaskDataLocal] INNER JOIN [qryModelDataPPQ]  
ON  
([ModelTaskDataLocal].[PM_FREQUENCY] = [qryModelDataPPQ].[PM_FREQUENCY_DAYS]) AND  
([ModelTaskDataLocal].[TIME_STAMP] = [qryModelDataPPQ].[TIME_STAMP]) AND  
([ModelTaskDataLocal].[ModelData_MODEL_WO_NBR] = [qryModelDataPPQ].[E_MODEL_WO_NUMBER]) AND  <<<<
([ModelTaskDataLocal].[PM_LATEST_DATE] = [qryModelDataPPQ].[PM_LATEST_DATE]) AND  
([ModelTaskDataLocal].[PM_EARLY_DATE] = [qryModelDataPPQ].[PM_EARLY_DATE]) AND  
([ModelTaskDataLocal].[PM_DUE_DATE] = [qryModelDataPPQ].[PM_DUE_DATE]) AND  
([ModelTaskDataLocal].[PM_CAT_CD] = [qryModelDataPPQ].[PM_CAT_CD]) AND  
([ModelTaskDataLocal].[PM_STATUS] = [qryModelDataPPQ].[PM_STATUS]) AND  
([ModelTaskDataLocal].[PM_TITLE] = [qryModelDataPPQ].[PM_TITLE]) AND  
([ModelTaskDataLocal].[PM_FREQUENCY_CODE] = [qryModelDataPPQ].[PM_FREQUENCY_CODE]) AND  
([ModelTaskDataLocal].[ModelData_PM_RQ_NUMBER] = [qryModelDataPPQ].[PM_RQ_NUMBER]) AND  
([ModelTaskDataLocal].[ModelData_PM_ID_NUMBER] = [qryModelDataPPQ].[PM_ID_NUMBER])  
SET  
[ModelTaskDataLocal].[ModelData_PM_ID_NUMBER] = [qryModelDataPPQ].[PM_ID_NUMBER],  
[ModelTaskDataLocal].[ModelData_PM_RQ_NUMBER] = [qryModelDataPPQ].[PM_RQ_NUMBER],  
[ModelTaskDataLocal].[ModelData_MODEL_WO_NBR] = [qryModelDataPPQ].[MODEL_WO_NBR], [ModelTaskDataLocal].[PM_FREQUENCY_CODE] = [qryModelDataPPQ].[PM_FREQUENCY_CODE],  
[ModelTaskDataLocal].[PM_FREQUENCY] = [qryModelDataPPQ].[PM_FREQUENCY_DAYS],  
[ModelTaskDataLocal].[PM_TITLE] = [qryModelDataPPQ].[PM_TITLE],  
[ModelTaskDataLocal].[PM_STATUS] = [qryModelDataPPQ].[PM_STATUS],  
[ModelTaskDataLocal].[PM_CAT_CD] = [qryModelDataPPQ].[PM_CAT_CD],  
[ModelTaskDataLocal].[PM_DUE_DATE] = [qryModelDataPPQ].[PM_DUE_DATE],  
[ModelTaskDataLocal].[PM_EARLY_DATE] = [qryModelDataPPQ].[PM_EARLY_DATE],  
[ModelTaskDataLocal].[PM_LATEST_DATE] = [qryModelDataPPQ].[PM_LATEST_DATE],  
[ModelTaskDataLocal].[TIME_STAMP] = [qryModelDataPPQ].[TIME_STAMP];
0
 
LVL 1

Author Comment

by:travisjbennett
ID: 26194489
Almost... now it says Operation must use an updatable query. (Error 3073)
The table ModelTaskDataLocal is a linked table with data in it. Any clue what's going on here? Do I need to open a new question for that part?

UPDATE 
ModelTaskDataLocal 
INNER JOIN 
qryModelDataPPQAliased 
ON 
(ModelTaskDataLocal.[ModelData_PM_ID_NUMBER] = qryModelDataPPQAliased.[PM_ID_NUMBER]) AND 
(ModelTaskDataLocal.[ModelData_PM_RQ_NUMBER] = qryModelDataPPQAliased.[PM_RQ_NUMBER]) AND 
(ModelTaskDataLocal.[ModelData_MODEL_WO_NBR] = qryModelDataPPQAliased.[E_MODEL_WO_NUMBER]) AND 
(ModelTaskDataLocal.[PM_FREQUENCY_CODE] = qryModelDataPPQAliased.[PM_FREQUENCY_CODE]) AND 
(ModelTaskDataLocal.[PM_FREQUENCY] = qryModelDataPPQAliased.[PM_FREQUENCY_DAYS]) AND
(ModelTaskDataLocal.[PM_TITLE] = qryModelDataPPQAliased.[PM_TITLE]) AND 
(ModelTaskDataLocal.[PM_STATUS] = qryModelDataPPQAliased.[PM_STATUS]) AND 
(ModelTaskDataLocal.[PM_CAT_CD] = qryModelDataPPQAliased.[PM_CAT_CD]) AND 
(ModelTaskDataLocal.[TIME_STAMP] = qryModelDataPPQAliased.[TIME_STAMP])
SET 
ModelTaskDataLocal.ModelData_PM_ID_NUMBER = [qryModelDataPPQAliased].[PM_ID_NUMBER], 
ModelTaskDataLocal.ModelData_PM_RQ_NUMBER = [qryModelDataPPQAliased].[PM_RQ_NUMBER], 
ModelTaskDataLocal.ModelData_MODEL_WO_NBR = [qryModelDataPPQAliased].[E_MODEL_WO_NUMBER], 
ModelTaskDataLocal.PM_FREQUENCY_CODE = [qryModelDataPPQAliased].[PM_FREQUENCY_CODE], 
ModelTaskDataLocal.PM_FREQUENCY = CInt([qryModelDataPPQAliased].[PM_FREQUENCY_DAYS]), 
ModelTaskDataLocal.PM_TITLE = [qryModelDataPPQAliased].[PM_TITLE], 
ModelTaskDataLocal.PM_STATUS = [qryModelDataPPQAliased].[PM_STATUS], 
ModelTaskDataLocal.PM_CAT_CD = [qryModelDataPPQAliased].[PM_CAT_CD], 
ModelTaskDataLocal.TIME_STAMP = [qryModelDataPPQAliased].[TIME_STAMP];

Open in new window

0
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 1700 total points
ID: 26194518
Often times you can't do an update with a Subquery. A lot of Access programmers cheat and run a make table query first, then update directly from that. Do you have the resources for that?
J
0
 
LVL 1

Author Comment

by:travisjbennett
ID: 26194538
I don't see a subquery above. Is that the inner join? Don't I need the inner join to do the update in the first place as in http://office.microsoft.com/en-us/access/ha100765271033.aspx
???
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26194909
See http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23720182.html

Also, feel free to search the Access Zone for the issue "Operation must use an updatable query"

You'll see that your issue isn't all that rare.

J
0
 
LVL 1

Author Comment

by:travisjbennett
ID: 26195141
Ok, but how do I update only the records with matching primary keys? The two primary keys that should be the match between the two tables for the update command to know which field to update from are

(ModelTaskDataLocal.ModelData_PM_RQ_NUMBER = [qryModelDataPPQAliased].[PM_RQ_NUMBER])
AND
(ModelTaskDataLocal.ModelData_MODEL_WO_NBR = [qryModelDataPPQAliased].[E_MODEL_WO_NUMBER])
0
 
LVL 1

Author Comment

by:travisjbennett
ID: 26195287
See -- I'm trying to update one table (ModelTaskDataLocal) from a query with the updated information as efficiently as possible (as with 500,000 records this needs to run quickly). We used to clear the table completely then redownload ALL the table again with an append query. However, the database size went nuts (you know how Access 2007 can get) quickly.
Thus, I'd like to try an update query. But how the heck to I update one table's fields by using corresponding records in the other table/query (on the right side of the = operator) without putting a join at the top (between the update and the set) ???
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26195605
as is showin in the link above, you qualify the join using a where clause instead.

Update myTable
Set MyField = SomeValue
FROM mySecondTable
Where myTable .field = mySecondTable .field AND myTable .field2 = mySecondTable .field2

something along those lines.
J

0
 
LVL 1

Author Comment

by:travisjbennett
ID: 26282738
Close I think... I want to replace myTable.MyField with a mySecondTable.field, not just SomeValue. That part I think I got working by simply having it update where the FROM is a table and not a query, as it won't update from a query. Replacing the query with the source table (not always easy or possible) fixed some of the original problem.
So, now the difficulty is that I'm not sure how to get it to update if a small field changes. For example, while the two linked fields in the QueryBuilder view relate the records, I want it to, for example, update the frequency code or title if that changes in the source table for the record identified by the PMID and PMRQ numbers. Is this query designed to do that, or do I need to link more fields or something?

UPDATE ModelTaskDataLocal INNER JOIN TIDECPMS ON (ModelTaskDataLocal.ModelData_PM_ID_NUMBER=TIDECPMS.PM_ID_NUMBER) AND (ModelTaskDataLocal.ModelData_PM_RQ_NUMBER=TIDECPMS.PM_RQ_NUMBER) SET ModelTaskDataLocal.ModelData_PM_ID_NUMBER = [TIDECPMS].[PM_ID_NUMBER], ModelTaskDataLocal.ModelData_PM_RQ_NUMBER = [TIDECPMS].[PM_RQ_NUMBER], ModelTaskDataLocal.ModelData_MODEL_WO_NBR = [TIDECPMS].[MODEL_WO_NUMBER], ModelTaskDataLocal.PM_FREQUENCY_CODE = [TIDECPMS].[PM_FREQUENCY_CODE], ModelTaskDataLocal.PM_FREQUENCY = [TIDECPMS].[PM_FREQUENCY_DAYS], ModelTaskDataLocal.PM_TITLE = [TIDECPMS].[PM_TITLE], ModelTaskDataLocal.PM_STATUS = [TIDECPMS].[PM_STATUS], ModelTaskDataLocal.PM_CAT_CD = [TIDECPMS].[PM_CAT_CD], ModelTaskDataLocal.PM_DUE_DATE = [TIDECPMS].[PM_DUE_DATE], ModelTaskDataLocal.PM_EARLY_DATE = [TIDECPMS].[PM_EARLY_DATE], ModelTaskDataLocal.PM_LATEST_DATE = [TIDECPMS].[PM_LATEST_DATE], ModelTaskDataLocal.TIME_STAMP = [TIDECPMS].[TIME_STAMP]
WHERE ((TIDECPMS.FACILITY)="BNP");

Open in new window

Query.bmp
0
 
LVL 1

Author Comment

by:travisjbennett
ID: 26288389
Ok... so the verdict is in.
I got it to work. If you want details: Follow these steps.
1. List all tables needed.
2. Join all fields which must be related: Even those keyfields between the table to-be-updated and the tables from which data is taken. However, just because a field is to be updated doesn't mean that it needs a join.
3. Avoid listing predicate joins (<=, >=, <>, etc.) and complicated on-a-function joins until the very last step.
4. In query designer view, list all the fields (Field, Table rows) to which you would like to post your data, as if you are selecting the fields to which you would like to place new/updated data in a select query (which your query should still be). This may seem a bit backwards at first, as you'd think you want to select the source data and not the destination data, but hang in there.
5. Make it into an update query via the ribbon button (rightmost tab). This may be somewhere else prior to Access 2007.
6. Pretend the "Update To:" row says "Set To:" (credits Jim B.), or "set the field above (in Field, Table rows) to this value:" and enter either a formula or your source field in [SourceTable].[SourceField] notation.
7. Set criteria! Otherwise, you'll update all fields on everything every time. If, for example, you need to speed up your query or only some data will change, use the Criteria and/or Or rows to name your "update this record only if [true]" criteria.
I'm going to award points based on how closely each of you hit on the solving answer to the question. While this thread achieved my end result, I wish it could have been split into seperate questions so I could award more points.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question