Reporting and Counting Unique Records

[RobFJ]

I've got a query comprising 3 fields from a single table that has over 20
fields.

When I run the query many of the lines are identical (which is correct).

What I'd like to do is just have one line for each unique record and a count
of the occurences.

For info the field names are Dept, Old_Title, New_Title

Can SKS point me in the right direction in terms of how to do it

TIA

Rob

 

[John Spencer]

Use a totals query
SELECT Dept, Old_Title, New_Title, Count(Dept) as RecordCount
FROM YourTable
GROUP BY Dept, Old_Title, New_Title

In query Design View
-- Add your table
-- Add The fields Dept, Old_title, New_title
-- Add Dept a second time
-- Select View: Totals from the menu
-- Change Group By to Count under the second instance of Dept

Run the query

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================