Picky but important detail: I don't see three tables described, but rather two tables and three table occurrences. At least that's how I interpret: "Copy of Shop Fuel'
is sBalance a summary field? Is it a running total summary field?
In which table is it defined? Shop Fuel?
In what table is "fuel in Tank" defined?
And in which table did you define the calculation that attempts to find the difference?
Sorry, I just looked at it and that is correct, I have one table and 2 table occurances.
sBalance is a summary field
I also have an sBalanceRunning field which is a running balance of cBalance (which is a calculation of Fuel Received- Fuel Consumed)
sBalance and "Fuel in Tank" are both defined in Shop Fuel table.
I also defined the calculation that attempts to find the difference in Shop Fuel Table
Set up a calculation like cBalance but which includes Fuel in Tank
Fuel Received + Fuel In Tank - Fuel Consumed.
Set up a summary field to sum that field.
How do I get the function to only grab the Last "fuel in tank" entry? The problem is the "fuel in tank" calculation is adding all the entries for "fuel in tank" and is throwing off my calculation for the summary of the cFuel in tank(fuel received - fuel in tank - fuel consumed)
I want to be able to enter in a Fuel balance from 2/24/15 and have it calculate the difference that is in the tank on that day... hope this makes sense!
Thank you for your help!!
I created a duplicate table occurrence of Shop Fuel named "Shop Fuel_Fuel in Tank" and have the relationship of:
Shop Fuel Date > Shop Fuel_Fuel in tank and the date is sorted in ascending order.
My calculation is as follows in the table occurrence "Shop Fuel_Fuel in Tank": cFuel in Tank = Fuel Received - Fuel Consumed - Last(Fuel in Tank)
And a Summary of cFuel in Tank. This is the field on my layout report...
The problem is that it works with the 1st set of dates selected but when I add another set of dates for the 2nd reconciliation it is grabbing the last "fuel in tank" number as well as the "fuel in tank" number before that... is there a way to get it pull the LAST "Fuel in tank" number based on the dates selected?
I had assumed that Fuel in tank was a value recorded in each record and used in each record's calc.
To access the value of a field in the last related record, use the Last function.
Thank you for your reply.
Fuel in Tank is a value recorded about once a month when the level of fuel in the tank is checked. I have the last function set up but in my Summary report I have a summary of cFuel in Tank and it is grabbing ALL the Fuel in Tank values... How Should I set this up? I would like for it to all be on the same report...
Fuel Received = sFuel Received
Fuel Consumed = sFuel Consumed
Balance = sBalance (Summary of cBalance = Fuel Received - Fuel Consumed)
Difference = sFuel in Tank (Summary of cFuel in Tank= Fuel Received - Fuel Consumed - Last (Fuel in Tank)
It works for the first set of dates 1/1/14 - 12/31/14 (Balance = 18, Fuel in Tank = 18, Difference = 0). But when I have the dates 1/1/14 - 2/24/15 selected the difference is not calculating correctly. It is adding the 18 from the prior Fuel in Tank.
Hope this makes sense! Thank you for you help!!
I think that I've been making too many assumptions about the design of your database.
Let's back up a bit by describing your tables and relationships in more detail.
I have 1 table titled shop fuel and here are the relationships I have set up:
I have a summary report set up which gives me what should be the balance in the tank. each time gas is consumed it is entered in a single entry (amt of fuel consumed).
I have sFuel Received = Total of "fuel received"
SFuel Consumed= Total of "fuel consumed"
sBalance = Total of cBalance cBalance = Fuel received - fuel consumed