423,688 Members | 2,147 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,688 IT Pros & Developers. It's quick & easy.

Carry Over Numbers

P: 3
Microsoft access 2016 not wanting to use VBA really.

I have 1 x meter that record fuel throughput. There is a totaliser at the top of each meter.
Each ltr issued increases the totaliser.

EG Close of day reading 1234567 so the next day 1234567 will be the starting figure.
So 2304 Ltrs issued will increment the figure to 1236871 which will be the start figure for the next day.

Ive tried lots of different variations but cant seem to get this to work. Ideally it will be a form so the data will automatically update once the form has been completed.

Day 1 Start 1234567 500 Issued Finish 1235067
Day 2 Start 1235067 2304 Issued Finish 1237371
Day 3 Start 1237371 1098 Issued Finish 1238469


Hope I've explained well enough.
Feb 11 '18 #1
Share this Question
Share on Google+
5 Replies


gnawoncents
100+
P: 212
Coops, welcome to Bytes!

This should be fairly easy to accomplish. Please tell us a little more about your current construct so we can better help.

A few questions to consider:
- Is the daily total entered only once per day, or multiple times and the overall total should be added only at the start of the next day?
- Do you need to keep daily totals for the totalizer, or is one running total fine?

Based on your needs, it might be something as simple as entering the daily total and having a save/add button that, when pressed, adds the daily total to the grand total. Again, it is based on your current construct and needs, so please give us some more information.
Feb 12 '18 #2

P: 3
Thanks for your help.
The figures are only entered once per day at the close of the day where the figures will be used to compare fuel issued
Feb 12 '18 #3

gnawoncents
100+
P: 212
There are lots of ways to do this, depending on your database structure, some will work better than others. You could use an update query to add the daily amount to the running total, or a little VBA, like the example below.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnAddDayFuel_Click()
  2.  
  3. Dim strSQL As String
  4.  
  5. DoCmd.SetWarnings (False)
  6.  
  7. strSQL = "Update tbTotaliser " _
  8. & "SET fldTotaliser =  Nz(fldTotaliser, 0) + " & Nz(Me.fldDailyFuel, 0) & ";"
  9.  
  10. DoCmd.RunSQL strSQL
  11.  
  12. DoCmd.SetWarnings (True)
  13.  
  14. End Sub
  15.  
In this example, the update happens when the user presses a button (btnAddDayFuel). It takes the daily total (fldDailyFuel) from the active form and adds it to the running toal (fldTotaliser), which is stored in the table "tbTotaliser."
Feb 12 '18 #4

Rabbit
Expert Mod 10K+
P: 12,272
So it sounds like the table is just 2 columns? Date and End of day measurement?

You can get the previous days measurement by joining the table to itself on the Date minus 1 day. This only works if there are no gaps in the dates.
Feb 12 '18 #5

P: 3
Thanks for all your help guys. I’ll be trying this over the next couple of days.
Feb 12 '18 #6

Post your reply

Sign in to post your reply or Sign up for a free account.