Add two values where another field equals given value (1 Viewer)

Oreynolds

Member
Local time
Today, 10:49
Joined
Apr 11, 2020
Messages
111
Hi All,

I'm either missing something obvious or just have total brain block! I have a series of complex queries which collate a load of data together into a single query which is then shown on the report as per below. In the footer of the report I would like to perform some simple calculations for example:

Subtract "VisitsDec" (Column3) Where "View Order" (Column1) = 14 from "VisitsDec" (Column3) Where "View Order" (Column1) = 8

Any ideas?!

Thanks

1623333216413.png
 

arnelgp

error reading drive A:
Local time
Today, 17:49
Joined
May 7, 2009
Messages
12,909
is ViewOrder a Column on your Query or just an Expression (sum all over)?

=DLookup("VisitsDec","qryMaintVisitReportUNION","ViewOrder = 8") - DLookup("VisitsDec","qryMaintVisitReportUNION","ViewOrder = 14")
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:49
Joined
Feb 19, 2002
Messages
31,996
Access is not a spreadsheet. There is no such thing as a cell. In a form, the Me. reference addresses the field value for the current row.. Looks like arnel assumed you had a spreadsheet. Good look with that since to make that work, you will need to know the actual ID of the record you are trying to reference. I would probably use a query of the base data that selects only type 8 and type 14 and multiply type 14 types -1. Then sum and cross tab and add a subreport in the footer.

OR, I would export the data to a spreadsheet and use Excel to do the calculations.
 

Users who are viewing this thread

Top Bottom