Solved Update Query Extract Rates Based on Date

[AngelSpeaks]

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:

and tblPWTable

 

[bastanu]

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]

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]

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]

My bad. Never heard of a Top 1 query. I just did a search on it. And subquery. I think I got it.

 

[bastanu]

Great news, post back if you get stuck

Cheers,

 

[arnelgp]

you can also use:

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

 

[AngelSpeaks]

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:

(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]

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.

 

[AngelSpeaks]

Would I replace the select with the dmax?

 

[arnelgp]

it's entirely up to you, my opinion is but an opinion.

 

[AngelSpeaks]

it's entirely up to you, my opinion is but an opinion.

But probably correct. Since I had my update query ready, I tested it and it didn't update.

 

[AngelSpeaks]

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):

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

 

[arnelgp]

you need to concatenate your form controls on the DMax():

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

 

[AngelSpeaks]

Thanks but I still got
'Data Type Mismatch in Criteria Expression'.

 

[AngelSpeaks]

Thanks but I still got
'Data Type Mismatch in Criteria Expression'.

Ok, found the error. It wasn't the DMax causing the error. Thanks

 

[AngelSpeaks]

Ugh! I created a Select query using this:

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]

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

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.