10 Replies Latest reply on Apr 13, 2015 3:48 PM by krystalm

    Reconciliation

    krystalm

      Title

      Reconciliation

      Post

      Hello,

      I currently have a fuel database with 3 tables. Which tracks fuel we receive (Diesel and Gasoline) as well as fuel consumed.

      Shop Fuel

      Shop Fuel_Odometer (copy of Shop Fuel)

      Equipment

      I recently added a new filed labeled "Fuel In Tank" which is used when the tank is measured for the amount of fuel left in it for reconciliation.  I am trying to create a reconciliation which takes the last "fuel in tank" entry - sBalance (which is a balance of what we show should be in the tank) and the result would be the difference from the two. 

       

      I have tried using ''Fuel In Tank'' - sBalance  in a Summary by Fuel Type Part but it isnt resulting in what I need. 

       

      Any help on this matter?

       

        • 1. Re: Reconciliation
          philmodjunk

          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?

          • 2. Re: Reconciliation
            krystalm

            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

            • 3. Re: Reconciliation
              philmodjunk

              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.

              • 4. Re: Reconciliation
                KM

                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!!

                • 5. Re: Reconciliation
                  KM

                  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? 

                  • 6. Re: Reconciliation
                    philmodjunk

                    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.  

                    • 7. Re: Reconciliation
                      KM

                      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!!

                       

                      • 8. Re: Reconciliation
                        philmodjunk

                        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. 

                        • 9. Re: Reconciliation
                          krystalm

                          I have 1 table titled shop fuel and here are the relationships I have set up:

                           

                          • 10. Re: Reconciliation
                            krystalm

                            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