Problem with Grouping & Totals

[MarcieFess]

My report has a main level grouping on field GRP.

There are 3 separate fields within a Group Rating: Health, Flammability, & Reactivity.

Each of these fields may contain a number 0 through 4.

I have designed a query that compares the 3 fields for any record and creates an entry in the GRP field according to which field (Health, Flam. or React) holds the largest number.

At this point my report will report all records with the largest number being in the Health field, then all the records with the largest number being in the Flammability field, then all the records with the largest number being in the Reactivity field.

Now I need to further sub-divide my report and sub-total on those subdivisions, but I'm not sure how to go about it.

I need the report to subtotal on the largest digit.

In other words, under Health, I need all of the records where the entry under Health is 4 to be grouped together, then the total QOH for all those records will be totaled. Then I need all of the 3's (under Health) to be grouped together, etc.

My problem is, I don't know how to do that. When the GRP changes, so does the field that contains the information to use to determine the sub-group.

Any ideas on how to do this?

Marcie

 

[David R]

GRP holds the 'name' of the field that is greatest, right? I saw your earlier thread on this subject, but didn't read your final solution closely. If so all I would do is use a similar process to calculate that highest value (whatever it is) and reference it as [GRPvalue].

You should then be able to GROUP BY [GRP] and [GRPvalue], and use Count on your Primary Key or some other non-null field, to get the subtotals you want.

 

[MarcieFess]

Thank you, David!

I did something quite similar, actually.

I had created a query, and that query created a new field through nested Iif statements.

My solution was to create a second query, based on that first query. This second query looked at the value in the Grp field created by the first query, then looked at the value in the field that had determined said value, and it created another new field and put in one of 12 values (i.e. Flammability 4, Flammability 3, Flammability 2, Flammability 1, Health 4, etc.)

I created a Make Table query from this second query which allowed me to create the table necessary for one of my reports, ignoring the records that don't meet minimum criteria.

Probably a LOT more cumbersome than what most experts would do, but it works!

In case anyone is interested, here's the code I wrote:

SELECT DISTINCT tblStoreInformation.StoreName, qryAustinHMIRFReportQuery.Grp, qryQuantityOnHand.QOH, Product.ReportUnits, Product.ProductName, Product.Size, Product.Units, tblStoreProducts.StoreProductKey, Product.NFPAFlammability, Product.NFPAHealth, Product.NFPAReactivity, Product.PhysicalState, tblStoreProducts.StoreKey, tblStoreInformation.StoreNum, IIf(([Grp]="Flammability") And ([qryAustinHMIRFReportQuery].[NFPAFlammability]=4),"Flammability 4",IIf(([Grp]="Flammability") And ([qryAustinHMIRFReportQuery].[NFPAFlammability]=3),"Flammability 3", Iif(([Grp]="Flammability") And ([qryAustinHMIRFReportQuery.NFPAFlammability]=2), "Flammability 2", Iif(([Grp]="Flammability") And ([qryAustinHMIRFReportQuery.NFPAFlammability] = 1), "Flammability 1",Iif(([Grp]="Health") And ([qryAustinHMIRFReportQuery.NFPAHealth] = 4), "Health 4", Iif(([Grp]="Health") And ([qryAustinHMIRFReportQuery.NFPAHealth] = 3), "Health 3", Iif(([Grp]="Health") And ([qryAustinHMIRFReportQuery.NFPAHealth] = 2), "Health 2", Iif(([Grp]="Health") And ([qryAustinHMIRFReportQuery.NFPAHealth] = 1), "Health 1",Iif(([Grp]="Reactivity") And ([qryAustinHMIRFReportQuery.NFPAReactivity] = 4), "Reactivity 4", Iif(([Grp]="Reactivity") And ([qryAustinHMIRFReportQuery.NFPAReactivity] = 3), "Reactivity 3", Iif(([Grp]="Reactivity") And ([qryAustinHMIRFReportQuery.NFPAReactivity] = 2), "Reactivity 2", Iif(([Grp]="Reactivity") And ([qryAustinHMIRFReportQuery.NFPAReactivity] = 1), "Reactivity 1","O")))))))))))) AS GrpSub
FROM (Product INNER JOIN ((tblStoreInformation INNER JOIN (tblStoreProducts INNER JOIN qryQuantityOnHand ON tblStoreProducts.StoreProductKey = qryQuantityOnHand.StoreProductKey) ON (tblStoreInformation.StoreKey = tblStoreProducts.StoreKey) AND (tblStoreInformation.StoreKey = qryQuantityOnHand.StoreKey)) INNER JOIN qryAustinHMIRFReportQuery ON (tblStoreInformation.StoreKey = qryAustinHMIRFReportQuery.StoreKey) AND (tblStoreProducts.StoreProductKey = qryAustinHMIRFReportQuery.StoreProductKey)) ON Product.ProductKey = tblStoreProducts.ProductKey) INNER JOIN tblStoreDepartment ON tblStoreInformation.StoreKey = tblStoreDepartment.StoreKey
ORDER BY tblStoreInformation.StoreName, qryAustinHMIRFReportQuery.Grp, Product.ProductName, Product.NFPAFlammability DESC , Product.NFPAHealth DESC , Product.NFPAReactivity DESC;

I just ran the Make Table query and it looks beautiful, with all the information just as I need it. Hopefully creating the reports now will be a relative snap.

Marcie

 

[David R]

Looks good, but why did you have to use Make Table? You can pull a query into a query... I may be missing something.