Re: "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" <JasonV@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C917B63C-3258-4BA5-8AFA-B599794F6A19@xxxxxxxxxxxxxxxx
>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
>


.

Relevant Pages

  • Re: Upgrading from Access 97 - any gotchas?
    ... is in the database. ... reference these queries in other queries. ... reference the form object or the query fields. ... Tony Toews, Microsoft Access MVP ...
    (microsoft.public.access.setupconfig)
  • Re: Object reference not set to an instance of an object (asp.net, c#)
    ... will not be set to a reference of an object because there is no table named ... The dataadapter has no way of knowing what to name tables that are a result ... of a query from the database. ... A query can actually return multiple result sets and therefore the ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Object reference not set to an instance of an object (asp.net, c#)
    ... will not be set to a reference of an object because there is no table named ... The dataadapter has no way of knowing what to name tables that are a result ... of a query from the database. ... A query can actually return multiple result sets and therefore the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Append query
    ... Dim db As Database ... Dim prm As Parameter ... > append query there is a reference to the claimID field of this form. ...
    (microsoft.public.access.formscoding)
  • cdt glossary 0.1.4
    ... This glossary seeks to limit lengthy misunderstandings ... basic database research and mathematics. ... When context matters, it is provided. ... It is /not/ the same as a reference. ...
    (comp.databases.theory)