access queries?

Question

I am using access to compare two tables. It shows 5 pieces of information, date, first name, last name, case and status. There is 8 different status'.Is there a way to total each status in a table so I dont have to count them as there is over a few 100 cases each day in this report?

There is 8 different status'. A B C D E F G H but there is multiple. So like 50 As 90 Bs etc. Can I add them up in a query to show it on the table.

Answer 1

In queries, create a new query in design view

Switch to SQL View

Enter this:

SELECT COUNT(column1) AS column1_total, COUNT(column2) AS column2_total, COUNT(column3) AS column3_total FROM tablename GROUP BY column1, column2, column3


In the SQL statement above, column1, column2 and column2 are the names of fields in your Access table; tablename is the name of your access table.

Answer 2

While in query design view, pull down the View menu and select Totals. A "Total" row will appear in the query designer. In that row, pick "Group By" for the status, and "Count" for any other. You only need one other column to count; generally it can be any other column, but you should pick one that will never have NULL values, because they won't be counted.

Answer 3

Not quite sure I understand, but would this work?

select status, count(*) from table group by status