# "Effective Date" Criteria for Rates

G

#### Guest

I have been stuck on this for some time now. I will try to make it as clear
as I can.

I need to create a Report of â€œtransactionsâ€ (days worked of various
employees) that have been assigned a specific invoice number. These reports
need to include calculated â€œamountsâ€ (hours worked x rate).

Different employees have different rates and the rates will change over
time, the rates are always assigned an â€œeffective dateâ€. The rate that is
used to calculate the â€œamountâ€ must use the rate that was effective on the
date that the transaction occurred.

I query all transactions (from the transaction table) to find those with the
specified Invoice Number(the transaction table includes date, employee, hours
worked, etc.). Now I need to assign a rate (from the rate table) to each
transaction based on the date the transaction occurred. Access must look at
the employee, then choose the largest â€œeffective dateâ€ which is less than the
transaction date. Lastly, I need to perform the â€œamountâ€ calc.

Can I set up a single query to do all of this? I know I can do it if each
employee only has a single rate (in the rate table). I just donâ€™t know how
to include the criteria to choose the correct effective date.

J

#### Jeff Boyce

Jason

If your requirement is a single query, I'm confident the more SQL-savvy
'group readers can come up with a SQL statement.

If your requirement is to get the results, consider "chaining" together
several queries, each of which does its portion of the total calculation.
The final query in that "chain" would produce your result (you'd just have
to break the whole into parts/steps).

D

#### Denis Dougall

I hope your data is normalized. But I would first look at a table like
Employee, effective date, ChargeRate or Labour Rate (whichever makes more
sense) where the first two elements are the PK. I would also have a table
like Employee, Invoice Number and SomeDate (which is used to select or
govern the LabourRate), StartDate, EndingDate. I would also include logic
the counts the number of days at a certain rate, ie ( row 1-Bob, 10 hours,
\$10, row-2-Bob 15 hours,\$20) if the project runs from May 16 to June 15 and
there was a rate change June 1 then 15 days at \$10 and 15 days at \$20.

Here is a date type select:

"SELECT Invoice, ActionDate ,Staff
FROM TableName
WHERE ActionDate Between dteStart AND dteEnd
GROUP BY Invoice, Staff"

Another consideration could be if a job is cost by wage levels ie two bosses
at \$50/hour and seven workers at \$25/hour and sometimes a worker can be
doing boss roles and should be billed at a boss rate. So determine if the
rate is at a task level or just a worker level(some more many to many
associatives).

Hope that helps a little

Denis

G

#### Guest

I have thought about stringing multiple queries together....the trouble I am
having is that the rate for each record is based on the transaction date for
that particular record.

I can query for all the transactions assigned with "X" Invoice Number from
the "Transaction" table. Then I want to run another query that finds the
rate (from the rate table)using the following criteria:

Employee match (I can do this by creating a "relationship" in the query)
effective date is the most recent date < transaction date.

Can I set criteria for one field based on another field in the same query?
To find the correct "effective date" I tried (in the effecive date criteria)"
<[Queries]![qryName]![FieldName] but it asks me for the value when I run
it.

thanks

V

#### Van T. Dinh

I have involved in a similar topic recently. Check the sample download
provided by Doug in this discussion:

http://tinyurl.com/aa24k

G

#### Guest

Thanks,
I finally got access to the attachment and I am going through it....I think
that it will help.
JV

G

#### Guest

I have used the topic you referenced to develop the following SQL script.

SELECT
tblLabourTransaction.ReferenceNumber, tblLabourTransaction.ActivityDate,
tblLabourTransaction.Hours, tblCraftCodes.EffectiveDate, tblCraftCodes.Rate,
[Hours]*[Rate] AS Amount
FROM
(tblLabourTransaction
INNER JOIN
[SELECT
tblLabourTransaction.ActivityDate
, Max(tblCraftCodes.EffectiveDate) as MxEffDt
FROM
tblCraftCodes
INNER JOIN
tblLabourTransaction
ON
tblCraftCodes.EffectiveDate <= tblLabourTransaction.ActivityDate
GROUP BY
tblLabourTransaction.ActivityDate]. AS CR
ON tblLabourTransaction.ActivityDate = CR.ActivityDate)
INNER JOIN
tblCraftCodes
ON
CR.MxEffDt = tblCraftCodes.EffectiveDate;

Below is the result of running this query. As you can see, there are
multile records for some of the ReferenceNumbers.

Reference Activity
Number Date Hours EffectiveDate Rate Amount
000208 01-Jun-05 10 01-Sep-04 \$82.00 820
000208 01-Jun-05 10 01-Sep-04 \$83.00 830
000208 01-Jun-05 10 01-Sep-04 \$83.00 830
000208 01-Jun-05 10 01-Sep-04 \$82.00 820
000208 01-Jun-05 10 01-Sep-04 \$221.00 2210
000208 01-Jun-05 10 01-Sep-04 \$221.00 2210
000209 08-Jul-05 10 01-Sep-04 \$82.00 820
000209 08-Jul-05 10 01-Sep-04 \$83.00 830
000209 08-Jul-05 10 01-Sep-04 \$83.00 830
000209 08-Jul-05 10 01-Sep-04 \$82.00 820
000209 08-Jul-05 10 01-Sep-04 \$221.00 2210
000209 08-Jul-05 10 01-Sep-04 \$221.00 2210
000210 10-Oct-05 10 01-Sep-04 \$82.00 820
000210 10-Oct-05 10 01-Sep-04 \$83.00 830
000210 10-Oct-05 10 01-Sep-04 \$83.00 830
000210 10-Oct-05 10 01-Sep-04 \$82.00 820
000210 10-Oct-05 10 01-Sep-04 \$221.00 2210
000210 10-Oct-05 10 01-Sep-04 \$221.00 2210
000211 15-Oct-05 10 12-Oct-05 \$100.00 1000
000212 16-Oct-05 10 12-Oct-05 \$100.00 1000
000213 20-Oct-05 10 20-Oct-05 \$200.00 2000
000214 25-Oct-05 10 20-Oct-05 \$200.00 2000
000215 30-Oct-05 10 20-Oct-05 \$200.00 2000
000216 10-Nov-05 10 20-Oct-05 \$200.00 2000
000217 15-Nov-05 10 20-Oct-05 \$200.00 2000

The Reference Number is a unique identifier for that transaction, each
transaction is assigned a ReferenceNumber, a Craft Code, an Account
Code(straight time, overtime,etc), an Activity Date and other data. The
Craft Code, Activity Date & Account Code determine the Rate based on the
Effective Date of the corresponding AccountCode, CraftCode records in
tblCraftCodes(the rate table). I think that the query is getting all rates
that fit with the EffectiveDate regardless of Craft Code or Account Code.

How do I add the criteria tblCraftCodes.Account Code &
tblCraftCodes.CraftCode to only select a Rate and calculate an Amount once
for each transaction?

I hope that I gave you enough information. I really appreciate any advice
you can give me. Let me know if you need further information.
Thanks a lot,
JV

V

#### Van T. Dinh

This is too complicated for me to analyse without the database but the
sample database I refer to works. I referred another question ("Formula in
a query referring to a table of reference" by Celine Brien in this newsgroup
successfully.

I you want, put the database (Compact & zip first) on a Web site for
and have a look. If you got no Web site to upload, post a question with
attachment in the Web forum Utter Access.

--
HTH
Van T. Dinh
MVP (Access)

Jason V said:
I have used the topic you referenced to develop the following SQL script.

SELECT
tblLabourTransaction.ReferenceNumber, tblLabourTransaction.ActivityDate,
tblLabourTransaction.Hours, tblCraftCodes.EffectiveDate,
tblCraftCodes.Rate,
[Hours]*[Rate] AS Amount
FROM
(tblLabourTransaction
INNER JOIN
[SELECT
tblLabourTransaction.ActivityDate
, Max(tblCraftCodes.EffectiveDate) as MxEffDt
FROM
tblCraftCodes
INNER JOIN
tblLabourTransaction
ON
tblCraftCodes.EffectiveDate <= tblLabourTransaction.ActivityDate
GROUP BY
tblLabourTransaction.ActivityDate]. AS CR
ON tblLabourTransaction.ActivityDate = CR.ActivityDate)
INNER JOIN
tblCraftCodes
ON
CR.MxEffDt = tblCraftCodes.EffectiveDate;

Below is the result of running this query. As you can see, there are
multile records for some of the ReferenceNumbers.

Reference Activity
Number Date Hours EffectiveDate Rate Amount
000208 01-Jun-05 10 01-Sep-04 \$82.00 820
000208 01-Jun-05 10 01-Sep-04 \$83.00 830
000208 01-Jun-05 10 01-Sep-04 \$83.00 830
000208 01-Jun-05 10 01-Sep-04 \$82.00 820
000208 01-Jun-05 10 01-Sep-04 \$221.00 2210
000208 01-Jun-05 10 01-Sep-04 \$221.00 2210
000209 08-Jul-05 10 01-Sep-04 \$82.00 820
000209 08-Jul-05 10 01-Sep-04 \$83.00 830
000209 08-Jul-05 10 01-Sep-04 \$83.00 830
000209 08-Jul-05 10 01-Sep-04 \$82.00 820
000209 08-Jul-05 10 01-Sep-04 \$221.00 2210
000209 08-Jul-05 10 01-Sep-04 \$221.00 2210
000210 10-Oct-05 10 01-Sep-04 \$82.00 820
000210 10-Oct-05 10 01-Sep-04 \$83.00 830
000210 10-Oct-05 10 01-Sep-04 \$83.00 830
000210 10-Oct-05 10 01-Sep-04 \$82.00 820
000210 10-Oct-05 10 01-Sep-04 \$221.00 2210
000210 10-Oct-05 10 01-Sep-04 \$221.00 2210
000211 15-Oct-05 10 12-Oct-05 \$100.00 1000
000212 16-Oct-05 10 12-Oct-05 \$100.00 1000
000213 20-Oct-05 10 20-Oct-05 \$200.00 2000
000214 25-Oct-05 10 20-Oct-05 \$200.00 2000
000215 30-Oct-05 10 20-Oct-05 \$200.00 2000
000216 10-Nov-05 10 20-Oct-05 \$200.00 2000
000217 15-Nov-05 10 20-Oct-05 \$200.00 2000

The Reference Number is a unique identifier for that transaction, each
transaction is assigned a ReferenceNumber, a Craft Code, an Account
Code(straight time, overtime,etc), an Activity Date and other data. The
Craft Code, Activity Date & Account Code determine the Rate based on the
Effective Date of the corresponding AccountCode, CraftCode records in
tblCraftCodes(the rate table). I think that the query is getting all
rates
that fit with the EffectiveDate regardless of Craft Code or Account Code.

How do I add the criteria tblCraftCodes.Account Code &
tblCraftCodes.CraftCode to only select a Rate and calculate an Amount once
for each transaction?

I hope that I gave you enough information. I really appreciate any advice
you can give me. Let me know if you need further information.
Thanks a lot,
JV

G

#### Guest

I have posted the attachment on the Web forum Utter Access with the Subject:
Van Thien Dinh - "Effective Date" Criteria for Rates. It is under the
Access Queries Forum.

Thanks
JV
Van T. Dinh said:
This is too complicated for me to analyse without the database but the
sample database I refer to works. I referred another question ("Formula in
a query referring to a table of reference" by Celine Brien in this newsgroup
successfully.

I you want, put the database (Compact & zip first) on a Web site for
and have a look. If you got no Web site to upload, post a question with
attachment in the Web forum Utter Access.

--
HTH
Van T. Dinh
MVP (Access)

Jason V said:
I have used the topic you referenced to develop the following SQL script.

SELECT
tblLabourTransaction.ReferenceNumber, tblLabourTransaction.ActivityDate,
tblLabourTransaction.Hours, tblCraftCodes.EffectiveDate,
tblCraftCodes.Rate,
[Hours]*[Rate] AS Amount
FROM
(tblLabourTransaction
INNER JOIN
[SELECT
tblLabourTransaction.ActivityDate
, Max(tblCraftCodes.EffectiveDate) as MxEffDt
FROM
tblCraftCodes
INNER JOIN
tblLabourTransaction
ON
tblCraftCodes.EffectiveDate <= tblLabourTransaction.ActivityDate
GROUP BY
tblLabourTransaction.ActivityDate]. AS CR
ON tblLabourTransaction.ActivityDate = CR.ActivityDate)
INNER JOIN
tblCraftCodes
ON
CR.MxEffDt = tblCraftCodes.EffectiveDate;

Below is the result of running this query. As you can see, there are
multile records for some of the ReferenceNumbers.

Reference Activity
Number Date Hours EffectiveDate Rate Amount
000208 01-Jun-05 10 01-Sep-04 \$82.00 820
000208 01-Jun-05 10 01-Sep-04 \$83.00 830
000208 01-Jun-05 10 01-Sep-04 \$83.00 830
000208 01-Jun-05 10 01-Sep-04 \$82.00 820
000208 01-Jun-05 10 01-Sep-04 \$221.00 2210
000208 01-Jun-05 10 01-Sep-04 \$221.00 2210
000209 08-Jul-05 10 01-Sep-04 \$82.00 820
000209 08-Jul-05 10 01-Sep-04 \$83.00 830
000209 08-Jul-05 10 01-Sep-04 \$83.00 830
000209 08-Jul-05 10 01-Sep-04 \$82.00 820
000209 08-Jul-05 10 01-Sep-04 \$221.00 2210
000209 08-Jul-05 10 01-Sep-04 \$221.00 2210
000210 10-Oct-05 10 01-Sep-04 \$82.00 820
000210 10-Oct-05 10 01-Sep-04 \$83.00 830
000210 10-Oct-05 10 01-Sep-04 \$83.00 830
000210 10-Oct-05 10 01-Sep-04 \$82.00 820
000210 10-Oct-05 10 01-Sep-04 \$221.00 2210
000210 10-Oct-05 10 01-Sep-04 \$221.00 2210
000211 15-Oct-05 10 12-Oct-05 \$100.00 1000
000212 16-Oct-05 10 12-Oct-05 \$100.00 1000
000213 20-Oct-05 10 20-Oct-05 \$200.00 2000
000214 25-Oct-05 10 20-Oct-05 \$200.00 2000
000215 30-Oct-05 10 20-Oct-05 \$200.00 2000
000216 10-Nov-05 10 20-Oct-05 \$200.00 2000
000217 15-Nov-05 10 20-Oct-05 \$200.00 2000

The Reference Number is a unique identifier for that transaction, each
transaction is assigned a ReferenceNumber, a Craft Code, an Account
Code(straight time, overtime,etc), an Activity Date and other data. The
Craft Code, Activity Date & Account Code determine the Rate based on the
Effective Date of the corresponding AccountCode, CraftCode records in
tblCraftCodes(the rate table). I think that the query is getting all
rates
that fit with the EffectiveDate regardless of Craft Code or Account Code.

How do I add the criteria tblCraftCodes.Account Code &
tblCraftCodes.CraftCode to only select a Rate and calculate an Amount once
for each transaction?

I hope that I gave you enough information. I really appreciate any advice
you can give me. Let me know if you need further information.
Thanks a lot,
JV

G

#### Guest

Van,
I have posted a thread with my DB attached like you suggested I do. I don't
mean to pressure you, I just hope that you haven't forgotten about me.....you
are my last hope and I am running out of time to complete this darn thing. I
would really appreciate any feedback.

Thanks,
Jason

Jason V said:
I have posted the attachment on the Web forum Utter Access with the Subject:
Van Thien Dinh - "Effective Date" Criteria for Rates. It is under the
Access Queries Forum.

Thanks
JV
Van T. Dinh said:
This is too complicated for me to analyse without the database but the
sample database I refer to works. I referred another question ("Formula in
a query referring to a table of reference" by Celine Brien in this newsgroup
successfully.

I you want, put the database (Compact & zip first) on a Web site for
and have a look. If you got no Web site to upload, post a question with
attachment in the Web forum Utter Access.

--
HTH
Van T. Dinh
MVP (Access)

Jason V said:
I have used the topic you referenced to develop the following SQL script.

SELECT
tblLabourTransaction.ReferenceNumber, tblLabourTransaction.ActivityDate,
tblLabourTransaction.Hours, tblCraftCodes.EffectiveDate,
tblCraftCodes.Rate,
[Hours]*[Rate] AS Amount
FROM
(tblLabourTransaction
INNER JOIN
[SELECT
tblLabourTransaction.ActivityDate
, Max(tblCraftCodes.EffectiveDate) as MxEffDt
FROM
tblCraftCodes
INNER JOIN
tblLabourTransaction
ON
tblCraftCodes.EffectiveDate <= tblLabourTransaction.ActivityDate
GROUP BY
tblLabourTransaction.ActivityDate]. AS CR
ON tblLabourTransaction.ActivityDate = CR.ActivityDate)
INNER JOIN
tblCraftCodes
ON
CR.MxEffDt = tblCraftCodes.EffectiveDate;

Below is the result of running this query. As you can see, there are
multile records for some of the ReferenceNumbers.

Reference Activity
Number Date Hours EffectiveDate Rate Amount
000208 01-Jun-05 10 01-Sep-04 \$82.00 820
000208 01-Jun-05 10 01-Sep-04 \$83.00 830
000208 01-Jun-05 10 01-Sep-04 \$83.00 830
000208 01-Jun-05 10 01-Sep-04 \$82.00 820
000208 01-Jun-05 10 01-Sep-04 \$221.00 2210
000208 01-Jun-05 10 01-Sep-04 \$221.00 2210
000209 08-Jul-05 10 01-Sep-04 \$82.00 820
000209 08-Jul-05 10 01-Sep-04 \$83.00 830
000209 08-Jul-05 10 01-Sep-04 \$83.00 830
000209 08-Jul-05 10 01-Sep-04 \$82.00 820
000209 08-Jul-05 10 01-Sep-04 \$221.00 2210
000209 08-Jul-05 10 01-Sep-04 \$221.00 2210
000210 10-Oct-05 10 01-Sep-04 \$82.00 820
000210 10-Oct-05 10 01-Sep-04 \$83.00 830
000210 10-Oct-05 10 01-Sep-04 \$83.00 830
000210 10-Oct-05 10 01-Sep-04 \$82.00 820
000210 10-Oct-05 10 01-Sep-04 \$221.00 2210
000210 10-Oct-05 10 01-Sep-04 \$221.00 2210
000211 15-Oct-05 10 12-Oct-05 \$100.00 1000
000212 16-Oct-05 10 12-Oct-05 \$100.00 1000
000213 20-Oct-05 10 20-Oct-05 \$200.00 2000
000214 25-Oct-05 10 20-Oct-05 \$200.00 2000
000215 30-Oct-05 10 20-Oct-05 \$200.00 2000
000216 10-Nov-05 10 20-Oct-05 \$200.00 2000
000217 15-Nov-05 10 20-Oct-05 \$200.00 2000

The Reference Number is a unique identifier for that transaction, each
transaction is assigned a ReferenceNumber, a Craft Code, an Account
Code(straight time, overtime,etc), an Activity Date and other data. The
Craft Code, Activity Date & Account Code determine the Rate based on the
Effective Date of the corresponding AccountCode, CraftCode records in
tblCraftCodes(the rate table). I think that the query is getting all
rates
that fit with the EffectiveDate regardless of Craft Code or Account Code.

How do I add the criteria tblCraftCodes.Account Code &
tblCraftCodes.CraftCode to only select a Rate and calculate an Amount once
for each transaction?

I hope that I gave you enough information. I really appreciate any advice
you can give me. Let me know if you need further information.
Thanks a lot,
JV

J

#### Jeff Boyce

Jason

I see this is still "open".

While I'm not familiar with a way to use a query result field within the
same query as a criterion (unless you resort to SQL), consider revisiting
the notion of "chaining" several queries together, where each subsequent one
builds on the results of the earlier one(s).

Regards

Jeff Boyce
<Office/Access MVP>

Jason V said:
Van,
I have posted a thread with my DB attached like you suggested I do. I don't
mean to pressure you, I just hope that you haven't forgotten about me.....you
are my last hope and I am running out of time to complete this darn thing. I
would really appreciate any feedback.

Thanks,
Jason

Jason V said:
I have posted the attachment on the Web forum Utter Access with the Subject:
Van Thien Dinh - "Effective Date" Criteria for Rates. It is under the
Access Queries Forum.

Thanks
JV
Van T. Dinh said:
This is too complicated for me to analyse without the database but the
sample database I refer to works. I referred another question ("Formula in
a query referring to a table of reference" by Celine Brien in this newsgroup
successfully.

I you want, put the database (Compact & zip first) on a Web site for
and have a look. If you got no Web site to upload, post a question with
attachment in the Web forum Utter Access.

--
HTH
Van T. Dinh
MVP (Access)

I have used the topic you referenced to develop the following SQL script.

SELECT
tblLabourTransaction.ReferenceNumber, tblLabourTransaction.ActivityDate,
tblLabourTransaction.Hours, tblCraftCodes.EffectiveDate,
tblCraftCodes.Rate,
[Hours]*[Rate] AS Amount
FROM
(tblLabourTransaction
INNER JOIN
[SELECT
tblLabourTransaction.ActivityDate
, Max(tblCraftCodes.EffectiveDate) as MxEffDt
FROM
tblCraftCodes
INNER JOIN
tblLabourTransaction
ON
tblCraftCodes.EffectiveDate <= tblLabourTransaction.ActivityDate
GROUP BY
tblLabourTransaction.ActivityDate]. AS CR
ON tblLabourTransaction.ActivityDate = CR.ActivityDate)
INNER JOIN
tblCraftCodes
ON
CR.MxEffDt = tblCraftCodes.EffectiveDate;

Below is the result of running this query. As you can see, there are
multile records for some of the ReferenceNumbers.

Reference Activity
Number Date Hours EffectiveDate Rate Amount
000208 01-Jun-05 10 01-Sep-04 \$82.00 820
000208 01-Jun-05 10 01-Sep-04 \$83.00 830
000208 01-Jun-05 10 01-Sep-04 \$83.00 830
000208 01-Jun-05 10 01-Sep-04 \$82.00 820
000208 01-Jun-05 10 01-Sep-04 \$221.00 2210
000208 01-Jun-05 10 01-Sep-04 \$221.00 2210
000209 08-Jul-05 10 01-Sep-04 \$82.00 820
000209 08-Jul-05 10 01-Sep-04 \$83.00 830
000209 08-Jul-05 10 01-Sep-04 \$83.00 830
000209 08-Jul-05 10 01-Sep-04 \$82.00 820
000209 08-Jul-05 10 01-Sep-04 \$221.00 2210
000209 08-Jul-05 10 01-Sep-04 \$221.00 2210
000210 10-Oct-05 10 01-Sep-04 \$82.00 820
000210 10-Oct-05 10 01-Sep-04 \$83.00 830
000210 10-Oct-05 10 01-Sep-04 \$83.00 830
000210 10-Oct-05 10 01-Sep-04 \$82.00 820
000210 10-Oct-05 10 01-Sep-04 \$221.00 2210
000210 10-Oct-05 10 01-Sep-04 \$221.00 2210
000211 15-Oct-05 10 12-Oct-05 \$100.00 1000
000212 16-Oct-05 10 12-Oct-05 \$100.00 1000
000213 20-Oct-05 10 20-Oct-05 \$200.00 2000
000214 25-Oct-05 10 20-Oct-05 \$200.00 2000
000215 30-Oct-05 10 20-Oct-05 \$200.00 2000
000216 10-Nov-05 10 20-Oct-05 \$200.00 2000
000217 15-Nov-05 10 20-Oct-05 \$200.00 2000

The Reference Number is a unique identifier for that transaction, each
transaction is assigned a ReferenceNumber, a Craft Code, an Account
Code(straight time, overtime,etc), an Activity Date and other data. The
Craft Code, Activity Date & Account Code determine the Rate based on the
Effective Date of the corresponding AccountCode, CraftCode records in
tblCraftCodes(the rate table). I think that the query is getting all
rates
that fit with the EffectiveDate regardless of Craft Code or Account Code.

How do I add the criteria tblCraftCodes.Account Code &
tblCraftCodes.CraftCode to only select a Rate and calculate an Amount once
for each transaction?

I hope that I gave you enough information. I really appreciate any advice
you can give me. Let me know if you need further information.
Thanks a lot,
JV

V

#### Van T. Dinh

Jason

I have been very busy with the contract work (which pays!) so I haven't been
on Utter Access. I am surprised that no one in Utter Access replied to
your question with the test database. "DougY" actually did a fair bit of
is not something that we can answer quickly. Generally, the SQL needs to be
tested to be sure it works correctly.

If I have a chance tonight - Friday (not a promise as we are implement a new
version of the database), I have a look at your database. Paid work, of
course, comes first.

G

#### Guest

Jeff,
I took your advice and I think that I finally have it figured out.

Thanks

G

#### Guest

Van,
I have it figured out, I used a combination of 3 queries.
Jason