update query updates 0 rows

Question

i've created an update query that is updating 0 rows. i have a table [Department Estimates] with estimate numbers for each department (fields) listed by date (records). i want to update those estimate numbers daily with current information from [PODCrewEstData Query]. the update will change the upcoming days estimates (i'd also like it to update today and yesterdays estimates).

The fields [EST_DATE] and [ESTDATE] are formatted for short date. All other fields (ie - [MSA], [FIN], etc) are formatted as numbers.

Here is the SQL:

UPDATE [PODCrewEstData Query] INNER JOIN [Department Estimates]

ON ([PODCrewEstData Query].OTHER = [Department Estimates].OTHER) AND ([PODCrewEstData Query].SECURITY = [Department Estimates].SECURITY) AND ([PODCrewEstData Query].[RX VENDOR] = [Department Estimates].[RX VENDOR]) AND ([PODCrewEstData Query].CHEM = [Department Estimates].CHEM) AND ([PODCrewEstData Query].RW = [Department Estimates].RW) AND ([PODCrewEstData Query].RP = [Department Estimates].RP) AND ([PODCrewEstData Query].OPS = [Department Estimates].OPS) AND ([PODCrewEstData Query].ENG = [Department Estimates].ENG) AND ([PODCrewEstData Query].[MOD] = [Department Estimates].[MOD]) AND ([PODCrewEstData Query].FIN = [Department Estimates].FIN) AND ([PODCrewEstData Query].FAC = [Department Estimates].FAC) AND ([PODCrewEstData Query].MIG = [Department Estimates].MIG) AND ([PODCrewEstData Query].MSB = [Department Estimates].MSB) AND ([PODCrewEstData Query].MSA = [Department Estimates].MSA)

SET [Department Estimates].EST_DATE = [PODCrewEstData Query].[ESTDATE], [Department Estimates].MSA = [PODCrewEstData Query].[MSA], [Department Estimates].MSB = [PODCrewEstData Query].[MSB], [Department Estimates].MIG = [PODCrewEstData Query].[MIG], [Department Estimates].FAC = [PODCrewEstData Query].[FAC], [Department Estimates].FIN = [PODCrewEstData Query].[FIN], [Department Estimates].[MOD] = [PODCrewEstData Query].[MOD], [Department Estimates].ENG = [PODCrewEstData Query].[ENG], [Department Estimates].OPS = [PODCrewEstData Query].[OPS], [Department Estimates].RP = [PODCrewEstData Query].[RP], [Department Estimates].RW = [PODCrewEstData Query].[RW], [Department Estimates].CHEM = [PODCrewEstData Query].[CHEM], [Department Estimates].[RX VENDOR] = [PODCrewEstData Query].[RX VENDOR], [Department Estimates].SECURITY = [PODCrewEstData Query].[SECURITY], [Department Estimates].OTHER = [PODCrewEstData Query].[OTHER]

WHERE ((([Department Estimates].EST_DATE)>=Date()-1));

When i run the update, it says it's going to update zero rows. when i view it in datasheet mode, it is blank.

Answer 1

Your query is requiring all fields in both to match and therefore there would be nothing to update as they already are the same.

Answer 2

so i've made the following change...

UPDATE [Department Estimates] INNER JOIN [PODCrewEstData Query]

ON [Department Estimates].EST_DATE = [PODCrewEstData Query].ESTDATE

SET [Department Estimates].EST_DATE = [PODCrewEstData Query].[ESTDATE], [Department Estimates].MSA = [PODCrewEstData Query].[MSA], [Department Estimates].MSB = [PODCrewEstData Query].[MSB], [Department Estimates].MIG = [PODCrewEstData Query].[MIG], [Department Estimates].FAC = [PODCrewEstData Query].[FAC], [Department Estimates].FIN = [PODCrewEstData Query].[FIN], [Department Estimates].[MOD] = [PODCrewEstData Query].[MOD], [Department Estimates].ENG = [PODCrewEstData Query].[ENG], [Department Estimates].OPS = [PODCrewEstData Query].[OPS], [Department Estimates].RP = [PODCrewEstData Query].[RP], [Department Estimates].RW = [PODCrewEstData Query].[RW], [Department Estimates].CHEM = [PODCrewEstData Query].[CHEM], [Department Estimates].[RX VENDOR] = [PODCrewEstData Query].[RX VENDOR], [Department Estimates].SECURITY = [PODCrewEstData Query].[SECURITY], [Department Estimates].OTHER = [PODCrewEstData Query].[OTHER]

WHERE ((([Department Estimates].EST_DATE)>=Date()-1));

... and it works perfectly! thanks so much for your help!