Find diff of consequtive values in subform

[ThatAgingNerd]

I have a database I built several years ago that I've used to record printer usage tallies (monthly). In a subform listing the usage tallies for individual printers, I'd like the subform to show the difference between the tally before and the current tally. For example, PrinterA had a tally of 2146 pages (in it's lifetime) on 12/1/2020 and on 1/1/2021 a tally of 2649. What is the difference between the tallies? The difference between 1/1/2021 and 12/1/2020 would be 503. How can I calculate that in the subform?

Example pic of what the form and subform look like:

I'd like to have columns show the difference for the PrinterPages, CopierPages, MonoPages, ColorPages, ScanImages, LedgerPages, LedgerScans ... but I haven't managed to figure out how to do that.

I'm a printer tech in charge of managing over 500 printers and these tallies are invaluable for managing and planning replacements. In the past, I've been copying the data from the database, pasting it into an Excel sheet that has additional columns doing the calculation (easy to do in Excel... apparently not so simple in an Access subform). I'd like the database to show me that calculation without having to manually copy/paste.

Can you help?

[Welshgasman]

Review http://allenbrowne.com/subquery-01.html as one method.

[June7]

Pulling data from another record of same table is not simple. Options:

Correlated subquery http://allenbrowne.com/subquery-01.html#AnotherRecord
You want the prior value for 7? fields, that means 7 nested queries.

Self-join query - gets kind of messy because of date calculation and gets messier if there are gaps in date.

Domain aggregate function expression in textbox.
Unfortunately, calculating first day of previous month is not simple but the month and year parts are really all that is needed. So calculate last day of previous month and extract yearmonth (again, no date gaps - if there are this gets more complicated).
=DLookup("PrinterPages","MyTable","PrinterID=" & [PrinterID & " AND Format([Date],'yyyymm')=" & Format(DateSerial(Year([Date]),Month([Date]),0),"yyyymm"))


The two query options seem to result in a non-editable dataset - not good for a data entry form. Do you want this form to be editable?


Really should not use reserved words as names. Date is a reserved word because it is an intrinsic function.

[CJ_London]

If there is only one reading per month as your data implies, you can get a conversion comparison on a format of yyyymm for your self join.

Basic query might be something like this

SELECT *
FROM 
    (SELECT *, Format([readDate],"yymm") AS Curr) AS Prev FROM Printers)  AS latest
INNER JOIN 
    (SELECT *, Format(DateAdd("m",-1,[readDate]),"yymm") AS Prev FROM Printers) AS Previous
 ON (latest.Curr = Previous.Prev) AND (latest.printerFK = Previous.printerFK)
WHERE latest.printerFK=1200

Using calculated values like this in your joins can be slow due to the fact they cannot be indexed

edit: just realised you want a year difference - so use -12 rather than -1 in the second aliased query (Previous)

[June7]

CJ, difference between 1/1/2021 and 12/1/2020 is not a year, OP is asking for monthly change but the annual option good to know. Your SQL is pretty much what I had in mind for second option but would not include static WHERE clause.

[Micron]

I've been copying the data from the database, pasting it into an Excel sheet that has additional columns doing the calculation

Might be easier to stay with Excel since you already have that set up, but don't copy/paste - link to the Access table from Excel?