Results 1 to 4 of 4
  1. #1
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121

    How to run this query


    I have transport data and I have another table that contains Fuel for every month It show below.

    FUEL_SCHED EFFECTIVE_DATE CHARGE_RATE
    FUEL 01/01/17 18.4
    FUEL 02/05/17 19.5
    FUEL 03/05/17 18.72
    FUEL 04/02/17 18.72
    FUEL 04/30/17 18.33
    FUEL 06/04/17 17.49
    FUEL 07/02/17 16.64


    If any PO's date under fall it should pick up the rate. I cant link to date to date it shows only one date. I need to show fuel if any PO date between 6/4/207 and 7/1/2017 it should pick up 17.49. I am using if condition with hard coding price po date between a to b then show rate.

    is there any other solution. I have 5 years fuel rate I have add to add against every single PO date.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you could add another column that show the End Date of each range, it would be much easier.
    So, for the records you posted above, the End Date would be:
    2/4/2017
    3/4/2017
    4/1/2017
    4/29/2017
    6/3/2017
    7/1/2017
    7/19/2017


    Then, you could do a query where your Fuel Date from this other table is between these two dates.
    So, there would be no Join between the two tables, but the Criteria under your Fuel Date field would look something like:
    Code:
    >=[tblFuel_Rates]![EFFECTIVE_DATE] And tblFuel_Dates.FUEL_DATE)=[tblFuel_Rates]![END_DATE]
    There are various ways you can create/populate this End Date field. You could use a loop through a RecordSet in Access VBA.
    Or you could simply export to Excel, subtract one from the Effective Date below it, and re-import.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515

    fuel rate

    1st: make a query to get the correct rate for the date: qsMaxFuel date.
    It get the MAX date that does not go over the current date the driver used.

    2nd query: The query above is then used to lookup rates and use against drive amt.

    Click image for larger version. 

Name:	q3 together.jpg 
Views:	20 
Size:	84.2 KB 
ID:	29540

  4. #4
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121

    Red face

    Quote Originally Posted by ranman256 View Post
    1st: make a query to get the correct rate for the date: qsMaxFuel date.
    It get the MAX date that does not go over the current date the driver used.

    2nd query: The query above is then used to lookup rates and use against drive amt.

    Click image for larger version. 

Name:	q3 together.jpg 
Views:	20 
Size:	84.2 KB 
ID:	29540
    Thanks a Lot, my problem solved now. thanks.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums