Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2013
    Posts
    3

    Unanswered: Find Previous Transaction

    I need help! I am trying to find the fuel cost for a delivery. The fuel purchase would be on the fuel picked up prior to the first delivery date/time which could be the previous day, or weeks previously. I have a fuel cost table with FuelPurchID and Date/Time,Truck Number/Cost. The Delivery table has the Truck Number and Date/Time. There are multiple Trucks and Fuel purchases. FuelCost.Date/Time < Delivery.DateTime does not give me the results I want. There are several delivery dates and purchase dates, so Max date won't work. I am using queries populating temp tables. I am a novice, so please plain english .

    Thanks very much.

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    We would need a lot more details in order to assist. What Delivery? What are the field names? What tables are they in (table names)? Can you post a sample copy? Eliminate all confidential data, compile and zip.

  3. #3
    Join Date
    Jun 2012
    Posts
    85
    from my understand you are trying to get the previous record correct? so use a subquery. ie.

    Code:
    (SELECT [FuelCost] FROM [TheTable] AS Alias WHERE
    [Date/Time] = (SELECT Max(Date/Time) FROM
    [TheTable] AS Alias2 WHERE
     Alias2.[Date/Time] < [TheTable].[Date/Time] AND 
    Alias2.[FuelPurchID] = [TheTable].[FuelPurchID]) AND 
    Alias.[FuelPurchID] = [TheTable].[FuelPurchID]) As Previous
    Assuming the FuelPurchID Is what distinguishes your records. this will select the previous value in the FuelCost Field. IE>
    Code:
    FuelPurchID....Date/Time...FuelCost......Previous
    1                  21/05/2013  500.00          
    1                  22/05/2013  600.00         500.00
    1                  28/05/2013  700.25         600.00
    1                  26/06/2013  500.16         700.25
    1                  24/07/2013  451.02         500.16
    2                  21/05/2013  450.03           
    2                  23/05/2013  800.00         450.03
    2                  28/05/2013  641.00         800.00
    2                  01/06/2013  430.00         641.00 
    2                  08/06/2013  544.00         430.00
    Mike
    ________

  4. #4
    Join Date
    Aug 2013
    Posts
    3
    I don't think I explained it very well. I am looking to populate the Delivery table with the Fuel Cost from the Fuel Cost table based on the purchase date/time of fuel Prior to the delivery date/time. The common link is the Truck Number. It seems like it would be an easy thing to do, but I cannot seem to "get it".

    FuelCost table

    FuelPurchID TruckNbr Date/Time Cost
    12348 145 08/02/2013 07:00 pm 3.00
    12347 123 08/02/2013 12:00 am 5.00
    12346 789 08/01/2013 10:00 pm 3.00
    12345 123 08/01/2013 05:00 pm 2.50
    12344 123 07/31/2013 06:00 pm 2.00
    12343 145 07/15/2013 01:00 pm 2.75
    ================================================== ================

    Delivery Table

    TruckNbr Date/Time Fuel Cost = (FuelCost Date/Time < Delivery.Date/Time)
    123 08/02/2013 02:00 am $5.00
    145 08/02/2013 10:00 am 2.75
    123 08/01/2013 10:00 am 2.00

  5. #5
    Join Date
    Aug 2013
    Posts
    3

    anyone?

    can anyone help?

  6. #6
    Join Date
    Nov 2011
    Posts
    413
    Why would you want to store that value twice?

  7. #7
    Join Date
    Jan 2005
    Posts
    146
    If I understand correctly, you would like to find the fuel cost pulled from the FuelCost table where the TruckNbr matches the TruckNbr in the Delivery table and the Date/Time in the FuelCost table is the next nearest date as the Date/Time in the Delivery table. What you first are looking for is the record in FuelCost who's Date/Time is the next nearest to the record in the Delivery table and then use that Cost value in your calculation.
    Code:
    GetDate = DMax("[Date/Time]","FuelCost","[Date/Time]<" & [Delivery].[Date/Time] & "# And [TruckNbr]=" & [Delivery].[TruckNbr])
    GetFuelCost=Dlookup("[Cost]","FuelCost","[Date/Time]=#" & GetDate & "# And [TruckNbr]=" & [Delivery].[TruckNbr])

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •