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

[Oreynolds]

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

 

[arnelgp]

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]

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.