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 .
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.
from my understand you are trying to get the previous record correct? so use a subquery. ie.
(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>
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".
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.
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])