Need some Access XP help

[sao123]

I'm building a simple access database for work which will track our snowplow salt usage... and I need some help...

Due to environmental concerns & cost measures, we have rules about how much salt can be dispensed during a particular storm... each truck has a computer called a GL-400 controller, which automates spreading material while driving down the road. Material is generally spread in one of 4 mixes... 100% Salt, 75% Salt, 50% Salt, 25% Salt with the remaining percent antiskid microgravel.

The data we are trying to track is the following...
1)the amount of time a GL-400 is operated in automatic mode vs manual mode.
2)the amount of material mixture used in pounds per lane mile.

If either one of those values crosses the upper threshhold, we call this violation an exception. Reports are printed detailing the exception, and now a justification & a management responce must be recorded.

Here is my problem...

I need to be able to make a 12 pie chart report detailing combinations of the data...

Pie Chart 1: Breakdown of Total exceptions (% auto, vs lbs per mile)
Pie Chart 2: Breakdown of Total exceptions by Justification reason
Pie Chart 3: Breakdown of Total exceptions by Mgmt Responce
Pie Chart 4: Breakdown of Total exceptions by Severity

Pie Chart 1: Breakdown of %Auto exceptions (% auto, vs lbs per mile)
Pie Chart 2: Breakdown of %Auto exceptions by Justification reason
Pie Chart 3: Breakdown of %Auto exceptions by Mgmt Responce
Pie Chart 4: Breakdown of %Auto exceptions by Severity

Pie Chart 1: Breakdown of Lbs per Mile exceptions (% auto, vs lbs per mile)
Pie Chart 2: Breakdown of Lbs per Mile exceptions by Justification reason
Pie Chart 3: Breakdown of Lbs per Mile exceptions by Mgmt Responce
Pie Chart 4: Breakdown of Lbs per Mile exceptions by Severity

I cant figure out how to get all this information in 1 query, since it seems you can only calculate 1 aggregate per query...
and I dont know how to base 1 report off of 12 querys...


My main table looks like this...

[GL400]
PriKey EntryID
OperatorLog (other table)
MaterialMix (Lookup table)
Total Pounds Spread
Total Miles Spread
Lbs Per Mile
%Auto
ExJustification (Lookup table)
ExResponce (Lookup table)
ExSeverity (Lookup table)

[Operator Log]
PriKey LogID
Operator (Lookup Table)
Supervisor (Lookup Table)
Date
Shift (Lookup Table)
Equipment# (Lookup Table)
County (Lookup Table)
Stockpile (Lookup Table)

I'll post my entire database if someone is willing to provide me with some general guidance on how to do a multifield aggregrate query

 

[KLin]

Make 12 queries, make 12 reports, and add all 12 reports as subreports to a single report. The subreports would need to link to a unique ID(I'm asuming it's the EntryID or LogID) in the main report.

 

[sao123]

I got that far...
Im having a problem with record count...

here are a few rows of my table with some of the unneeded records omitted

GL400ID | %Auto | Mix | Lbs/Mile | Justification
1 .............96.........100%...1000....NULL
2..............90.........100%.....800......Ice Formation
3..............96.........100%...1100......GL400 Malfunction
4..............50.........100%...1200......Operator Error


I need a record count of %auto > 95 and a record count of Lbs/mile > 1000
to make a pie chart, it should say there are 2 %auto exceptions and 2 Lbs/mile exceptions...

however record count counts only records that are not NULL...
So this query would make 3 row entries, thus I keep getting 3 & 3 instead of 2 & 2...

I cant figure out how to fix this query.

 

[KLin]

So you don't want to count the record that is showing a null justification value? Just say "Is Not Null" in the criteria field for the justification column.

 

[sao123]

Originally posted by: KLin
So you don't want to count the record that is showing a null justification value? Just say "Is Not Null" in the criteria field for the justification column.

tried that... its doesnt work.
count only counts table rows, it completely ignores the criteria field. I confirmed this with an access XP book.

 

[KLin]

For the justification field, use the NZ function. the NZ function will replace a null value in a record with whatever you want. Say like for the justfication field:

NZ([Justification], "NA") Not Available would be a good replacement for a null value I guess.