Re: [MSAccessProfessionals] Combine several update queries into one query

Ok, the simple short answer is that the first query CANNOT be combined with either of the others.

However, the 2nd query [qry_UpdateCOMPAccural] and 3rd query MIGHT be able to be combined...but it depends on when/how they are used. So, in short, it's a timing-sensitive operation.

 

The first query definitely updates a different set of records than the other two.

the 2nd and 3rd queries, on the surface, seem to update the same set of records on the same table joins. So that makes them a candidate to be combined. but, if these queries have served you well for over 10+ years, I would not mess with them unless there was a DANGED GOOD reason to do so, and "reducing the number of queries to maintain" is not necessarily a sufficiently good reason to do so by itself.

 

I would leave well enough alone in this case.

 

- Mark

On 06/28/2023 6:58 PM EDT Jim Wagner <josephwagner2@outlook.com> wrote:

 

 

Mark,
Though this is not a big issue. I have dealt with this for 17 years, so if it cannot be done, i will live. I just was looking at any way of being more efficient. We are finally after 17 years moving to SQL after me pleading and begging and teaching that how we do things is not industry standard. We are still using Access as the front end because it would take another 17 years to redo every database in another UI.


qry_UpdateSICKAccrual
UPDATE [qry_R&D-ACCRUALS] INNER JOIN qry_AccrualsForReport ON [qry_R&D-ACCRUALS].Emplid = qry_AccrualsForReport.Emplid SET qry_AccrualsForReport.sick = [Balance], qry_AccrualsForReport.YTDsick = [Hrs Taken Ytd]
WHERE ((([qry_R&D-ACCRUALS].[Plan Type Descr])="Sick"));


qry_UpdateVACAccrual
UPDATE [qry_R&D-ACCRUALS] INNER JOIN qry_AccrualsForReport ON ([qry_R&D-ACCRUALS].Emplid = qry_AccrualsForReport.Emplid) AND ([qry_R&D-ACCRUALS].[Accrual Proc Dt] = qry_AccrualsForReport.[Accrual Proc Dt]) SET qry_AccrualsForReport.vac = [Balance], qry_AccrualsForReport.YTDvac = [Hrs Taken Ytd]
WHERE ((([qry_R&D-ACCRUALS].[Plan Type Descr])="Vacation"));


qry_UpdateCOMPAccural
UPDATE [qry_R&D-ACCRUALS] INNER JOIN qry_AccrualsForReport ON ([qry_R&D-ACCRUALS].Emplid = qry_AccrualsForReport.Emplid) AND ([qry_R&D-ACCRUALS].[Accrual Proc Dt] = qry_AccrualsForReport.[Accrual Proc Dt]) SET qry_AccrualsForReport.vac = [Balance], qry_AccrualsForReport.YTDvac = [Hrs Taken Ytd]
WHERE ((([qry_R&D-ACCRUALS].[Plan Type Descr])="Vacation"));