Count Distinct in a crosstab

[Rita Brasher]

I have a very large table with the following columns:

-Date
-Location
-Route
-Shipment Number(Master)
-Piece Number (Child) (Each piece has the same Master, but different
children ~ I know, not my terminology LOL)



There are multiple locations, each of which have multiple routes. Each
of these routes contain multiple shipments. While there are multiple
pieces per shipment, I am unconcerned with that at this point in time.

Here's an example of what the data might look like:

Date Location Route Master Child
7/10/2009 MEM FH100 123456789 111111111
7/10/2009 MEM FH100 123456789 222222222
7/10/2009 MEM FH100 123456789 333333333
7/10/2009 MEM FH100 123456789 444444444
7/10/2009 IND YY203 456789123 121212121
7/10/2009 IND YY203 456789123 131313131
7/10/2009 IND YY203 789456123 212121212
7/10/2009 IND YY203 789456123 323232323


The bottom line I need to reach is, by date, location, and route, how
many individual Masters are there...

so in the above, for 7/10/09, I'd have

MEM FH100 1
IND YY203 2


I'm trying to figure this out with a pivot table, but it's counting a
Master for each child and not counting distinct master numbers. I
created a query that omits the child column, but the master is still
repeated for every child row. If there's a way to not repeat the
masters in this query, I can use it to pivot for totals, but again, I
don't know how to stop the repetition.



Thanks in advance for any help you can provide!
Rita Brasher

 

[John Spencer]

Use a Sub-query to return the distinct values and then do a group and count
based on the distinct records.

SELECT Location, Route, Count(Master) as TheCount
FROM
(SELECT DISTINCT Location, Route, Master
FROM YOurTable) as DRoutes
GROUP BY Location, Route

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

 

[John Spencer]

Whoops, looks like a left out your date field so you will need to add that
into the subquery and the query.

SELECT TheDate, Location, Route, Count(Master) as TheCount
FROM
(SELECT Distinct T.Date as TheDate, T.Location, T.Route, T.Master
FROM YourTable as T)
GROUP BY TheDate, Location, Route



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