Trying to filter data as I summarize in Query

[ecoulson123]

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.

[pietlinden]

any reason you can't use a base query to filter out the stuff you don't
want?

[Smartin]

ec*********@yahoo.com wrote:

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.

Have you looked at the "Criteria" row in the query builder?

Since you want to want to filter on "Status" conditions, drag this field
into your query. You can uncheck "Show" on this column if you want.

Next, in the Criteria row under Status, put something like ='good'. In
the 'Or' row, put something like ='bad'. You can add similar criteria to
the Error column as well.

Suggestion: after playing with these options, look at your query in SQL
view. This may give you more ideas about how to filter your data.

HTH
--
Smartin