Trying to Filter data while summarizing in query

[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.

 

[Van T. Dinh]

Please post the SQL String of your Query.

 

[Guest]

How do I summarize data (pull it together) from several databases to display
all information in one reporting database? Each database must have a label
based on it's information.

I also need a subreport within the database to sum each databases information.

i.e. the report database will have all detail from each database A, B, C,
D, E. I then need to summarize each A, B, C, D, E separately within the one
database; which I assume I need a subreport.

Is it possible to email me? (e-mail address removed)

Thanks

 

[DL]

Perhaps I might ask as to why you have multiple dbs, when the data contained
is apparently related?

 

[Guest]

It was the original way it was put together, because we have to do separate
analysis by networks of the data based on other tables we import. The
databases would be extremely large combining all of the data to analyze.

Below is what I would like to do just for the reporting end piece (perhaps
linking the final tables to one reporting (printing) database):
I need to combine the several databases into one one reporting database by
customer, by network and detail with a summary for each network. So that
each customer prints by customer, their summary and then their detail ALL in
one database versus several. We are spending too much time collating each
network database by customer (since they could have all 3 as in the example
below). It would simplify and save time to have it all sort correctly in one
database with the summaries ahead of the detail.

For example:

a. Summary customer by network1 detail (page break)
a1. Detail customer records for network 1 (page break)
b. Summary customer by network2 detail (page break)
b1. Detail customer records for network 2 (page break)
c. Summary customer by network3 detail (page break)
c1. Detail customer records for network 3 (page break)

 

[DL]

As no one has proffered any suggestions perhaps I might hazard a few;
I dont believe you can achieve what you require, within Access unless you
combine the db's into one.
I may be wrong on this.
You may have to resort to a third party app, if you do not wish to combine,
maybe Crystal Reports could help.
You can use multiple Access db's, using Borland Delphi.
good luck.