ACCESS FEED

Rabu, 28 Juni 2023

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

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

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

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

Jim,

Before I could answer that:

1) can we see that actual SQL for the queries
2) If you can give us a representative look at the data in the tables, that would help us greatly to determine if the queries can be engineered more succinctly/efficiently.

While in strict SQL terms it may be possible to combine operations, in SET Theory (mathematical) terms, it may or may not be "safe" to undertake such operations.

in short, we need some more information before we can reasonably answer you.

- Mark Burns

Owner, Professional Microsoft Access Developers Network [PMADN] Group, LinkedIn.com

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

Hello all,
I am looking at a very old process that creates a table daily. My retired boss created it a very long time ago, so I wanted to know if there is a way of making the process more efficient. I am breaking down the multiple queries in sections to see if i can recreate the process in less queries.
There are 3 update queries that update a table with values based on some criteria. there are 2 sources that are joined together to be the source. Below is the
The 3 queries update columns such as sick, vacation and comp with the balance for each. below are the query summary
sick column and Hrs taken Ytd for the YTDsick column based on the criteria of "Sick" for the Plan Type Descr field.
vac column and Hrs taken Ytd for the YTDsick column based on the criteria of "Vacation" for the Plan Type Descr field.
comp column and Hrs taken Ytd for the YTDsick column based on the criteria of "Comp time" for the Plan Type Descr field and "5W" for the Plan Type field.

Is there a way of getting this to be one query or I am I where I am at for good?

Thank You

Jim Wagner

[MSAccessProfessionals] Combine several update queries into one query

Hello all,
I am looking at a very old process that creates a table daily. My retired boss created it a very long time ago, so I wanted to know if there is a way of making the process more efficient. I am breaking down the multiple queries in sections to see if i can recreate the process in less queries.
There are 3 update queries that update a table with values based on some criteria. there are 2 sources that are joined together to be the source. Below is the
The 3 queries update columns such as sick, vacation and comp with the balance for each. below are the query summary
sick column and Hrs taken Ytd for the YTDsick column based on the criteria of "Sick" for the Plan Type Descr field.
vac column and Hrs taken Ytd for the YTDsick column based on the criteria of "Vacation" for the Plan Type Descr field.
comp column and Hrs taken Ytd for the YTDsick column based on the criteria of "Comp time" for the Plan Type Descr field and "5W" for the Plan Type field.

Is there a way of getting this to be one query or I am I where I am at for good?

Thank You

Jim Wagner

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Sabtu, 24 Juni 2023

Re: [MSAccessProfessionals] DLookup not working

DLookup() in a query is very inefficient. Generally speaking you'd join the tables, like:

SELECT Tbl_COTag.[COLOR OUT1:], Tbl_HBColor.ColourName, Tbl_HBColor.FrenchTranslation

FROM Tbl_COTag INNER JOIN Tbl_HBColor ON Tbl_COTag.[COLOR OUT1:] = Tbl_HBColor.ColourName;

In the rare event that you needed a DLookup() it would look like:

DLookUp("ColourName","tbl_HBColor","ColourName = '" & [COLOR OUT1:] & "'")

Paul

------ Original Message ------

From: porag379@gmail.com

To: MSAccessProfessionals@groups.io

Sent: 6/24/2023 3:45:45 AM

Subject: Re: [MSAccessProfessionals] DLookup not working

Dear Sir,
Please find the attached my simple Access Database "TestData" where contained 2 Tables "Tbl_COTag" and "Tbl_HBColor" , actually "Tbl_HBColor" Table carried 2 fields "ColourName" and "FrenchTranslation" . And Table "Tbl_COTag" where field "COLOR OUT1:" stored value should be LOOKUP value from source Table "Tbl_HBColor" in field "ColurName" and "FrenchTranslation" to table "Tbl_COTag" in field "Lookup_HB_ENG_C" and "Lookup_HB_ENG_F"

Actually I need same as excel VLOOKUP function working process in Access Database using DLOOKUP function, for example I've attached both Access "TestData" and Excel "VLOOKUP_Excel" file for your better understanding.

If you give me clear Query example using DLOOKUP function in my attached "TestData" Access Database then it will best for me.

Thanks
Porag

Re: [MSAccessProfessionals] DLookup not working

Dear Sir,
Please find the attached my simple Access Database "TestData" where contained 2 Tables "Tbl_COTag" and "Tbl_HBColor" , actually "Tbl_HBColor" Table carried 2 fields "ColourName" and "FrenchTranslation" . And Table "Tbl_COTag" where field "COLOR OUT1:" stored value should be LOOKUP value from source Table "Tbl_HBColor" in field "ColurName" and "FrenchTranslation" to table "Tbl_COTag" in field "Lookup_HB_ENG_C" and "Lookup_HB_ENG_F"

Actually I need same as excel VLOOKUP function working process in Access Database using DLOOKUP function, for example I've attached both Access "TestData" and Excel "VLOOKUP_Excel" file for your better understanding.

If you give me clear Query example using DLOOKUP function in my attached "TestData" Access Database then it will best for me.

Thanks
Porag

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Jumat, 23 Juni 2023

Re: [MSAccessProfessionals] Filter report before emailing it

Hello Doyce,
I have to do this as well. A solution that I found that works is to use a table to store the ID of the supervisor. Then create the report and query to filter off of that value in the table. Then as you loop, you can update that value with the current supervisor and export the report.

Mike D

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_