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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
"Effective Date" Criteria for Rates
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. Let me know if more information is needed……thanks in advance. |
#2
|
|||
|
|||
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). -- Regards Jeff Boyce Office/Access MVP "Jason V" wrote in message ... 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. Let me know if more information is needed……thanks in advance. |
#3
|
|||
|
|||
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 "Jeff Boyce" wrote: 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). -- Regards Jeff Boyce Office/Access MVP "Jason V" wrote in message ... 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. Let me know if more information is needed……thanks in advance. |
#4
|
|||
|
|||
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 "Jason V" wrote in message ... 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. Let me know if more information is needed..thanks in advance. |
#5
|
|||
|
|||
I have involved in a similar topic recently. Check the sample download
provided by Doug in this discussion: http://tinyurl.com/aa24k -- HTH Van T. Dinh MVP (Access) "Jason V" wrote in message ... 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. Let me know if more information is needed..thanks in advance. |
#6
|
|||
|
|||
Thanks,
I finally got access to the attachment and I am going through it....I think that it will help. JV "Van T. Dinh" wrote: I have involved in a similar topic recently. Check the sample download provided by Doug in this discussion: http://tinyurl.com/aa24k -- HTH Van T. Dinh MVP (Access) "Jason V" wrote in message ... 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. Let me know if more information is needed..thanks in advance. |
#7
|
|||
|
|||
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 "Van T. Dinh" wrote: I have involved in a similar topic recently. Check the sample download provided by Doug in this discussion: http://tinyurl.com/aa24k -- HTH Van T. Dinh MVP (Access) "Jason V" wrote in message ... 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. Let me know if more information is needed..thanks in advance. |
#8
|
|||
|
|||
"Effective Date" Criteria for Rates
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 |
#9
|
|||
|
|||
"Effective Date" Criteria for Rates
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 |
#10
|
|||
|
|||
"Effective Date" Criteria for Rates
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 |
Thread Tools | |
Display Modes | |
|
|
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 |