Update a table based on results of a query...

[scriggs]

I have a table with data which I want to update existing records in another table - I am importing legacy data.

I have created a query which extracts the data I need but I cannot get this to update the main table.

qryBudgetDataMatched - contains a record for USED line with 12 months data in columns.
tblBudgetsLocal - contains the records for ALL POSSIBLE line entry with 12 months blank columns.

i want the data in qryBudgetDataMatched to update tblBudgetsLocal

My current SQL is:

UPDATE qryBudgetDataMatched INNER JOIN tblBudgetsLocal ON (qryBudgetDataMatched.NurseryMatch = tblBudgetsLocal.NUMBER) AND (qryBudgetDataMatched.AccountMatch = tblBudgetsLocal.intChartHeaderID) SET tblBudgetsLocal.Month1 = qryBudgetDataMatched.month1, tblBudgetsLocal.Month2 = qryBudgetDataMatched.month2;

I get the error 'Operation must use an updateable query'

 

[PHV]

You may try to replace this:
UPDATE qryBudgetDataMatched INNER JOIN tblBudgetsLocal
By this:
UPDATE tblBudgetsLocal INNER JOIN qryBudgetDataMatched

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriggs]

Thanks PHV, thats a very good point.

I tried changing the query round but still get the same error 'Operation must use an updateble query'

UPDATE tblBudgetsLocal INNER JOIN qryBudgetDataMatched ON (tblBudgetsLocal.intChartHeaderID = qryBudgetDataMatched.AccountMatch) AND (tblBudgetsLocal.NUMBER = qryBudgetDataMatched.NurseryMatch) SET tblBudgetsLocal.Month1 = [qryBudgetDataMatched].[Month1], tblBudgetsLocal.Month2 = [qryBudgetDataMatched].[Month2];

Am I trying to achieve this the right way? Is there a better way which would work?

 

[scriggs]

I had another look at this one myself.

The problem seems to stem from the fact that qryBudgetDataMatched is grouped and therefore not updateable, even though I am not trying to update the query.

For now I have bodged it by using a make table query to make a table from qryBudgetDataMatched and then using the resulting table in my SQL above instead of the query.

This works for now, but if you know why it gives the silly error or a better way to solve then let me know.

Regards.