Hello all,
I have to tables in a database: Shipments and Packages
I'm trying to display some particular information, but just can't seem to get the SQL query right. Here's what I've got so far:

Code:
SELECT Shipments.ShipmentNumber, Shipments.ShipmentType, Shipments.Packaging, Count([Packages.Hazardous?]=True) AS RadPkgs, Count(Packages.ContainerNumber) AS ContainerCount, Sum(Packages.[Radiation(mR/hr)]) AS ContactTotal, Sum(Packages.TI) AS 1MTotal
FROM Packages INNER JOIN Shipments ON Packages.Shipment = Shipments.ShipmentNumber
GROUP BY Shipments.ShipmentNumber, Shipments.ShipmentType, Shipments.Packaging;
Unforunately, I can't modify the database fields, or I would to avoid the bad convention...

However, you can kind of see what I'm looking to do. My problem lies with the Count([Packages.Hazardous?]=True) AS RadPkgs part. I'm trying to retrieve the number of records where the [Packages.Hazardous?] field is true. I also tried using another SELECT statement in the where clause, but I couldn't find a way to associate the primary key with that subquery with the main query, so I'd just get a total count in the DB.
Any ideas?