3021: No Current Record - Update Query 1

[shVBA]

Hi,

Im fairly new to creating databases and please be nice as my database is probably not the best designed but im trying.

My database is designed to compare two datasets from our internal systems and then append data mimatches into [Mismatch Table - Results] then people can use forms and such to investigate the issue and then fix the issue, which means next time the comparison is made the two fields will match. As this event of them matching again on the system is where the mismatch is resolved i am trying to create a query which sets all the now matching items to resolved.

I have set up a select query which finds items that are resolved - SQL Below:

UPDATE [Mismatch Table - Results] SET [Mismatch Table - Results].[Resolution Confirmed by] = "SYSTEM", [Mismatch Table - Results].[Resolution Confirmed Date] = Date()
WHERE ((((SELECT [Mis-Match ID] From [Ready to Resolve - Cost Centre]))=[Mismatch Table - Results].[Mis-Match ID]));

This query works fine and returns the records which i expect, however to run the update query, i have tried several variations of the below SQL however it seems to return no current record and i cant understand why.

UPDATE [Mismatch Table - Results] SET [Mismatch Table - Results].[Resolution Confirmed] = -1, [Mismatch Table - Results].[Resolution Confirmed by] = "SYSTEM", [Mismatch Table - Results].[Resolution Confirmed Date] = Date()
WHERE ((((SELECT [Mis-Match ID] From [Ready to Resolve - Title]))=[Mismatch Table - Results].[Mis-Match ID]));

If anyone could point me in the right direction or explain why this has happened i would be very grateful.

 

[PHV]

You may try this:

UPDATE [Mismatch Table - Results]
SET [Resolution Confirmed] = True, [Resolution Confirmed by] = 'SYSTEM', [Resolution Confirmed Date] = Date()
WHERE [Mis-Match ID] In (SELECT [Mis-Match ID] FROM [Ready to Resolve - Title]);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shVBA]

Hi,

Thanks for your reply, I have amended the SQL and set the query running, however it has been running for over 5 minutes now and is still only 1 little blue bit of running through the query if you understand what i mean. This would suggest that the query is going to take a long time and i have 18 of these queries that will be run on a daily basis.

I have tried joining the tables to do it that way but that produces an error: 'Must us an updateable query'

Im not sure if i gave a good enough description in my first post but if i did - can you suggest a more efficient way of approaching this?