Count Totals - get a zero for no data

[jpkeller55]

I have the query listed below in red that counts ShipperID's and DivID's from data in a table and assigns the field name DataName. Does anybody know how I can assign a zero count if a particular ShipperID or DivID is not entered on the table? For example if the data looks like this:

ShipDate ShipperID DivID
9/14/05 FedEx AT
9/14/05 FedEx NF
9/14/05 Courier AT

And I want the below query to produce this:

ShipDate DataName CountTotals
9/14/05 FedEx 2
9/14/05 Courier 1
9/14/05 Taxi 0
9/14/05 AT 2
9/14/05 NF 1
9/14/05 HS 0

SELECT ShipDate, ShipperID As DataName, Count(ShipperID) As CountTotals From ShipDetailTable GROUP BY ShipDate, ShipperID
UNION SELECT ShipDate, DivID, Count(DivID) From ShipDetailTable GROUP BY ShipDate, DivID;

Any thoughts? THanks!

 

[lespaul]

do you have a table that lists all the possible shipperIDs?

 

[jpkeller55]

Yes...I have a table called SHIPPERS and also a table for DIVISIONS

Thanks

 

[jpkeller55]

Sorry, the tables are named: ShipperTable and DivisionTable

 

[PHV]

The solution is here: thread701-1130714