E
ecoulson123
I apologize for the length.
I am using Access 2000. I am trying to summarize numeric data from a
large database. The problem is that I need the summarization functions
to ignore "junk" data, defined in a couple ways.
Assume an excerpt of the database table looks like this:
Lot SerialNumber Status Error
101 001 Good .3
002 Good .2
003 Mach Fail .4
004 Bad .35
005 Good .2
006 PriorOp Fail.3
102 001 Bad 1.2
002 Good .12
003 Good .22
004 Good .34
005 Bad .2
Etc....
Now, I want to run a query that reports the data in this format:
Lot Lot_Count Avg_Error
101 6 .26
102 5 .22
Etc....
The query will list every lot in the table, the number of entries per
lot, and the average of the errors for all entries per lot. I am able
to make Access do this. Here is my problem:
I want to filter the data such that the query returns the average error
per lot, only including data that has a "Good" or "Bad" status, and
only data that has errors between -1 and +1. So in the data above,
SerialNumbers 003 and 006 from lot 101 would not be included in the
average error calculations for lot 101 because of their status, and
serialnumber 001 from lot 102 would not be included because it is >+1.
Here is how I set up my query so far:
In design view, I drag "Lot" into the first column. Then I drag "Lot"
into the 2nd column. I click on the "Total" tool in the toolbar, and
change the "Total" entry in the 2nd column to "Count". Then I drag
"Error" into the 3rd column, and set the "Total" entry to average. Like
I said, this works, but doesn't provide the data filtering I require. I
do not know how to add the filtering.
Thanks.
I am using Access 2000. I am trying to summarize numeric data from a
large database. The problem is that I need the summarization functions
to ignore "junk" data, defined in a couple ways.
Assume an excerpt of the database table looks like this:
Lot SerialNumber Status Error
101 001 Good .3
002 Good .2
003 Mach Fail .4
004 Bad .35
005 Good .2
006 PriorOp Fail.3
102 001 Bad 1.2
002 Good .12
003 Good .22
004 Good .34
005 Bad .2
Etc....
Now, I want to run a query that reports the data in this format:
Lot Lot_Count Avg_Error
101 6 .26
102 5 .22
Etc....
The query will list every lot in the table, the number of entries per
lot, and the average of the errors for all entries per lot. I am able
to make Access do this. Here is my problem:
I want to filter the data such that the query returns the average error
per lot, only including data that has a "Good" or "Bad" status, and
only data that has errors between -1 and +1. So in the data above,
SerialNumbers 003 and 006 from lot 101 would not be included in the
average error calculations for lot 101 because of their status, and
serialnumber 001 from lot 102 would not be included because it is >+1.
Here is how I set up my query so far:
In design view, I drag "Lot" into the first column. Then I drag "Lot"
into the 2nd column. I click on the "Total" tool in the toolbar, and
change the "Total" entry in the 2nd column to "Count". Then I drag
"Error" into the 3rd column, and set the "Total" entry to average. Like
I said, this works, but doesn't provide the data filtering I require. I
do not know how to add the filtering.
Thanks.