Query or code?

  • Thread starter Thread starter David Portwood
  • Start date Start date
D

David Portwood

I have two fields: Category and NumberOfContracts. Category takes 3 values,
call them A, B, and C. For each record there are a number of Contracts
associated with the choice of Category. So my records look like:

Category NumberOfContracts
B 3
A 1
B 2
C 5
C 3

There are other fields as well, of course.

What I want is to sum the number of contracts for each Category. I want to
generate a report that looks something like:

Count of All Contracts: 14 Total of A: 1 Total of B: 5 Total of C: 8

Can I do this using a query or do I have to open a recordset and count
record-by-record?
 
Use a Totals query:

1. Create a query using your table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

3. Drag your Category field into the grid.
Accept Group By in the Total row under this field.

4. Drag NumberOfContacts into the grid.
Choose Sum in the Total row.
 
Hi David,

A simple totals query will give you the sum of each Category:
SELECT Category, Sum(NumberOfContracts) AS TotalContracts
FROM MyTableName
GROUP BY Category;

If you want the total for all groups as well, you can add a subquery which
sums all records:
SELECT Category, Sum(NumberOfContracts) AS TotalContracts,
(Select sum(NumberOfContracts) FROM MyTableName) AS GrandTotal
FROM MyTableName
GROUP BY Category;

However, if you are using the results in either a form or a report it is
easier to simply base the form/report on the first query, and add a textbox
in the footer containing the following expression:
=Sum([TotalContracts])
If you do this, the grand total will reflect any filter applied to the
form/report.

HTH,

Rob
 
I continue to be impressed with the power of Access. This is exactly what I
was hoping to be able to do, but I did not realize it would be so simple. I
envisioned several queries finally resulting in the answers I wanted. When I
pushed the boss to move from Excel to Access I may have been smarter than I
realized.

Thanks very much for the help.
 
Back
Top