Re: "Effective Date" Criteria for Rates

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <JasonV@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C301EC8B-104A-4536-AAA1-ED47B576C914@xxxxxxxxxxxxxxxx
>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.
>
>


.

Relevant Pages

  • Re: Too many criterias...
    ... Not only that, they were just one of a few criterias, so ... Criteria3 = Date of transaction ... For each employee name chosen, ... the next cell and I can just do a sumif function. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Too many criterias...
    ... such as the charge amounts in this case. ... Criteria1= Your employee ... Criteria3 = Date of transaction ... will total for Employee for expense type on date ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Too many criterias...
    ... such as the charge amounts in this case. ... Criteria1= Your employee ... Criteria3 = Date of transaction ... will total for Employee for expense type on date ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Too many criterias...
    ... Criteria1= Your employee ... Criteria3 = Date of transaction ... will total for Employee for expense type on date ... track business expenses amongst about 8 employees. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: "Effective Date" Criteria for Rates
    ... Employee, effective date, ChargeRate or Labour Rate (whichever makes more ... "SELECT Invoice, ActionDate,Staff ... > date that the transaction occurred. ...
    (microsoft.public.access.queries)