Solved Update Query Extract Rates Based on Date (1 Viewer)

AngelSpeaks

Active member
Local time
Today, 08:31
Joined
Oct 21, 2021
Messages
406
I have two tables, tblPWBenefits and tblPWTable. Currently, I am extracting rates by using the County from both tables and performing a join. Works great. However, the rates on tblPWTable do change based on effective date and I want to based the extracting by using CheckDate on tblPWBenefits and Effective Date on tblPWTable (in the future, Trade Title may be added as an extraction criteria. Table tblPWBenefits would have to be joined to tblEmployees to obtain that). Images of queries against these two tables are included. I tried joining both tables on County with a criteria of CheckDate being greater than or equal to Effective Date, but that didn't work. Since tblPWBenefits is a transaction table (rates change), I store the rates on that table for reporting purposes. Thanks

Oh a VBA solution would work.

tblPWBenefits:

1647456978780.png


and tblPWTable

1647456478618.png
 

Attachments

  • 1647456444827.png
    1647456444827.png
    274 KB · Views: 232

bastanu

AWF VIP
Local time
Today, 06:31
Joined
Apr 13, 2010
Messages
1,401
Hi Cathy,

I had a similar structure for a db I did awhile ago for a payroll department at a local university; what I had was a Top 1 query on the benefit rates table sorted descending by the Effective date. Then I would use a parameter query based on it to get the latest rate in effect at the time of transaction (Check or Invoice date) EffectiveDate <=FOrms!frmMyForm!CheckDate. You should be able to do the same in your query but you would need to write that in SQL view.

Cheers,
 

AngelSpeaks

Active member
Local time
Today, 08:31
Joined
Oct 21, 2021
Messages
406
Hi Cathy,

I had a similar structure for a db I did awhile ago for a payroll department at a local university; what I had was a Top 1 query on the benefit rates table sorted descending by the Effective date. Then I would use a parameter query based on it to get the latest rate in effect at the time of transaction (Check or Invoice date) EffectiveDate <=FOrms!frmMyForm!CheckDate. You should be able to do the same in your query but you would need to write that in SQL view.

Cheers,
Thanks Bastanu. These transactions are being imported from an Excel spreadsheet. I should have mentioned that.
 

GPGeorge

Grover Park George
Local time
Today, 06:31
Joined
Nov 25, 2004
Messages
1,775
The source doesn't matter as long as the tables are in an Access accdb, whether imported or linked. Vlad's approach will provide the result needed.
 

AngelSpeaks

Active member
Local time
Today, 08:31
Joined
Oct 21, 2021
Messages
406
My bad. Never heard of a Top 1 query. I just did a search on it. And subquery. I think I got it.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:31
Joined
May 7, 2009
Messages
19,169
you can also use:

DMax("[Base Wage]","qryPWTable","[Effective Date]<=" & Format$([CheckDate], "\#mm\/dd\/yyyy\#"))
 

AngelSpeaks

Active member
Local time
Today, 08:31
Joined
Oct 21, 2021
Messages
406
Update:

I think I'm on the right track. I have to extract from tblPWTable, the following columns (Base Wage, H/W Rate, Vacation Rate, Pension Rate, Training Rate, Other Rate, and State) when the County from tblPWBenefits equals the County in tblPWTable and tblPWBenefits![CheckDate] is greater than or equal to qryPWTable![Effective Date] (which is sorted descending on Effective Date):

These rates are then multiplied by number of hours to get Earnings for each.

I found one article that suggested I right click on the column in the Query Editor and use Zoom to enter the subquery.

For each of the columns mentioned above, I entered the following in Update To:

Code:
(SELECT Top 1 [qryPWTable]![Base Wage] FROM qryPWTable WHERE [CheckDate] >=[Effective Date] and tblPWBenefits![County] = qryPWTable![County])

The Select query appears to be working, we will see how the Update query works.

Thanks again.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:31
Joined
May 7, 2009
Messages
19,169
The Select query appears to be working,
the difference is sub-query will render your query non-updateable.
while using Domain function will not.
sub-query will load slow, but when it does finished there is not too much crawl
when you navigate the recordset.
on the other hand, dmax may show the result much faster but navigating through
the recordset will be a craw since everytime you go up or down a record the domain
function is called.
indexing on the "where" expression of the domain function will improve its performance.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:31
Joined
May 7, 2009
Messages
19,169
it's entirely up to you, my opinion is but an opinion.
 

AngelSpeaks

Active member
Local time
Today, 08:31
Joined
Oct 21, 2021
Messages
406
you can also use:

DMax("[Base Wage]","qryPWTable","[Effective Date]<=" & Format$([CheckDate], "\#mm\/dd\/yyyy\#"))
I entered this exactly as shown and I get 'Data Type Mismatch in Criteria Expression'.

In tblPWBenefits - [Base Wage] is Number, Double, Fixed, 2 decimals
[CheckDate] is Date/Time, Short Date
in tblPWTable - [Base Wage] is currency.
[Effective Date] is Date/Time, m/d/yy;@
Is that the cause of the error message?

tblPWTable was created by importing an Excel spreadsheet.

I received the same error message with this version of the code (I need the County to be the same and CheckDate >= Effective Date):
Code:
DMax("[Base Wage]","qryPWTable","[Effective Date]<= [tblPWBenefits]![CheckDate] And [tblPWBenefits]![County]=[qryPWTable]![County]")

I did change the format of Effective Date in qryPWTable to Short Date, and [Base Wage] to Fixed 2, no luck
Thanks
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:31
Joined
May 7, 2009
Messages
19,169
you need to concatenate your form controls on the DMax():

Code:
DMax("[Base Wage]","qryPWTable","[Effective Date]<= #" & Format([tblPWBenefits]![CheckDate], "mm\/dd\/yyyy") & "# And [tblPWBenefits]![County]= '" & [qryPWTable]![County] & "'")
 

AngelSpeaks

Active member
Local time
Today, 08:31
Joined
Oct 21, 2021
Messages
406
Thanks but I still got
'Data Type Mismatch in Criteria Expression'.
 

AngelSpeaks

Active member
Local time
Today, 08:31
Joined
Oct 21, 2021
Messages
406
Ugh! I created a Select query using this:
Code:
Expr1: DMax("[Base Wage]","qryPWTable","[Effective Date]<= #" & Format([tblPWBenefits]![CheckDate],"mm\/dd\/yyyy") & "# And [tblPWBenefits]![County]= '" & [qryPWTable]![County] & "'")

And I received "Enter Parameter Value" qryPWTable!County
 

AngelSpeaks

Active member
Local time
Today, 08:31
Joined
Oct 21, 2021
Messages
406
When I didn't get anywhere with DMax or Top 1, I decided to try a different approach. I found out how to join tables on a column with date ranges.

So first, I created a query called qryPWTableRange. I added two columns, ToDate, which was created as
Code:
ToDate: (SELECT MIN([Effective Date])
     FROM tblPWTable AS P2
     WHERE P2.County = P1.County
     AND P2.[Trade Title] = P1.[Trade Title]    
     AND P2.[Effective Date] > P1.[Effective Date])
And EndDate. If the ToDate was Nulls, I used 12/31/2099 and if not, I subtracted one from ToDate, so I can easily use BETWEEN.

The image below are the results and my new rate table.

Then I joined tblPWBenefits to qryPWTableRange on County and tblPWBenefits.[CheckDate] BETWEEN qryPWTableRange.[Effective Date] and EndDate.

Before I ran my update query, I exported tblPWBenefits to Excel. I ran my update query, exported the update tblPWBenefits to Excel and ran a compare between the two spreadsheets. The rows in tblPWBenefits for Check Date 2/1/22 were changed to reflect the two listed in the image for Cook and DuPage Counties (these rates were test rates). The only other changes were related to rounding. The tblPWTable only had rates dated 12/1/21. The 10/4/21 and 2/1/22 rates were added for testing.

MIN.png
 

Users who are viewing this thread

Top Bottom