Splitting out information in a report

[MarcieFess]

I need to split out information in a report and I'm not sure how to do it.

The report is based on the following query:

SELECT tblHazardClass.HazardClass, Product.ProductName, Product.Package, Product.Size, tblStoreProducts.Cases, Product.Units, Product.ReportUnits, tblStoreInformation.StoreName, (([tblStoreProducts].[MaxUnits]*[Product].[Size])/[Product].[ConversionRate]) AS QOH, tblStoreProducts.StoreKey
FROM tblStoreInformation INNER JOIN (tblHazardClass INNER JOIN (Product INNER JOIN tblStoreProducts ON Product.UPC = tblStoreProducts.UPC) ON tblHazardClass.HazardKey = Product.HazardKey) ON tblStoreInformation.StoreKey = tblStoreProducts.StoreKey
WHERE (((tblHazardClass.HazardClass)<>"NON-HAZARDOUS")) 
ORDER BY tblHazardClass.HazardClass;

My issue is that the products can have a PhysicalState of 'L' (liquid), 'S' (solid) or 'G' (gas). As of right now, only one of the HazardClass entries has multiple physical states (L or S).

My report details each product in the hazard class and totals the amount of that hazard class. Obviously, one cannot add gallons to pounds and come up with an answer that has any meaning whatsoever. I need to figure out how to have the report total the S and the L within a hazard class separately.

I'm open to any and all suggestions. Do I do this within the query, or within the report itself?

I've already considered making 2 separate Hazard Classes for the one in question (Corrosive). However, since these Hazard Classes are official classifications per the International Fire Code, that's not really an option.

Thank you for your help.

Marcie

 

[MarcieFess]

This is the VBA for the OnOpen procedure for the report in question:

Private Sub Report_Open(Cancel As Integer)
  Set DB = DBEngine.Workspaces(0).Databases(0)
  DoCmd.SetWarnings False
  DoCmd.RunSQL "Delete * From ztblHazardClass;"
  DoCmd.SetWarnings True
  Set GrpPages = DB.OpenRecordset("ztblHazardClass", DB_OPEN_TABLE)
  GrpPages.Index = "PrimaryKey"
  
End Sub

I'm not sure if a sub-group should be created in this code?

I feel like I'm under water on this, but at the same time I feel like it has to be a really simple solution.