How do you subtotal data in reports?

[Menhir]

All,

I am pretty new to Access so I apologize in advance for incredibly stupid questions. That said, let me give you a quick rundown of my current task.

I have an Excel spreadsheet that I maintain which has about 30 columns and 200 rows of data. My task is to summarize this data into a separate tab based on 1's or 0's. As in, if a field contains a certain string count it as a 1, if not count it as a zero. Once that is counted, subtotal it by region, for example, and then get a grand total.

So the end result would include column headers and look something like:

Central 12 10 15 20
East 10 15 20 35
Southeast 13 10 15 20

Total 35 35 40 75

As a first step, I was able to import the Excel data into a table in Access easily. Once I did that, I realized that I needed to design a query if I were to run any reports to get data from the table. But I understand I cannot include logical expression in a query, so it would have to be in a report?

So I created the report and included the logic - For ex: =IIF([Store] = "Complete",1,0). I was able to successfully get the 1's and 0's to display, but I am not able to figure out how to subtotal these numbers. The kicker is that that report should not show these 1's and 0's coz I only need those for calculating the subtotal and total numbers.

The end result should look like the example I have shown. Any help would be appreciated. If none of this makes sense, I can post a screenshot of what I have in Excel which am trying to recreate in Access.

Thanks!

 

[Pat Hartman]

But I understand I cannot include logical expression in a query

But you can.

Select ...., IIF([Store] = "Complete",1,0) As CompleteCount
From YourTable;