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

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

It might actually be easier to do the updating in code, using DAO recordsets.

No, not with 500,000 records. I'll post that query in a minute.

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' 

Select allOpen in new window

How do I alias it?

Could you give me an example of how to accomplish this with an aliases?

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];

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];

Select allOpen in new window

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
???

See https://www.experts-exchange.com/questions/23720182/Operation-Must-Updateable-Query-Error.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

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])

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) ???

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

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");

Select allOpen in new window

Query.bmp

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.