A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Effective Date" Criteria for Rates



 
 
Thread Tools Display Modes
  #11  
Old October 20th, 2005, 03:46 AM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default "Effective Date" Criteria for Rates

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" wrote in message
...
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" wrote:


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" wrote:

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
about 10 mins after your post) and Celine adapted to Celine's database
successfully.

I you want, put the database (Compact & zip first) on a Web site for
download and advise the location in this thread and I'll try to

download it
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" wrote in message
...
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

Some additional information:
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





Ads
  #12  
Old October 20th, 2005, 10:49 PM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default "Effective Date" Criteria for Rates

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
work with the thread I referred to previously. Unfortunately, your question
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.

--
HTH
Van T. Dinh
MVP (Access)



"Jason V" wrote in message
...
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



  #13  
Old October 25th, 2005, 10:59 PM
Jason V
external usenet poster
 
Posts: n/a
Default "Effective Date" Criteria for Rates

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

Thanks

"Jeff Boyce" wrote:

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

  #14  
Old October 25th, 2005, 11:00 PM
Jason V
external usenet poster
 
Posts: n/a
Default "Effective Date" Criteria for Rates

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

"Van T. Dinh" wrote:

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
work with the thread I referred to previously. Unfortunately, your question
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.

--
HTH
Van T. Dinh
MVP (Access)



"Jason V" wrote in message
...
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




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Range and Average F/X Rate David General Discussion 0 June 23rd, 2005 02:26 PM
Greater than date to multiple criteria Bill Malmgren New Users 1 September 10th, 2004 12:23 AM
QDE (Quick Date Entry) Norman Harker Worksheet Functions 37 September 5th, 2004 01:24 AM
Aggregating Date Data into Weeks and Quarters Roger Running & Setting Up Queries 3 July 11th, 2004 05:56 PM
Date reference as criteria in query Tim Brown Running & Setting Up Queries 1 June 21st, 2004 01:01 AM


All times are GMT +1. The time now is 05:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.