Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Trying to Filter data while summarizing in query

Discussion in 'Information Technology' started by ecoulson123@yahoo.com, Oct 29, 2005.

  1. ecoulson123@yahoo.com

    ecoulson123@yahoo.com
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    Spammers trying to convert me to Islam Questions and Answers Aug 27, 2015
    Leisure Okay, so I am trying to sing in a different language... Business, Lifestyle & Leisure Jul 8, 2014
    Canada Ontario politician trying to lure Quebec doctors affected by values charter Breaking News Sep 16, 2013
    World Why the NATO Powers are Trying to Assassinate Moammar Gaddafi Breaking News Jun 14, 2011
    Malaysia Malaysia Says Extremist Groups Trying to Recruit from Schools Breaking News Jan 7, 2011

  3. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Please post the SQL String of your Query.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    <ecoulson123@yahoo.com> wrote in message
    news:1130531455.350926.299630@z14g2000cwz.googlegroups.com...
    >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.
    >
     
  4. KariStetzer

    KariStetzer
    Expand Collapse
    Guest

    Summarizing data from several databases to one

    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? karistetzer@charter.net

    Thanks
     
  5. DL

    DL
    Expand Collapse
    Guest

    Re: Summarizing data from several databases to one

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

    "KariStetzer" <KariStetzer@discussions.microsoft.com> wrote in message
    news:5BB44D32-AF7B-4269-81FE-99209D54C388@microsoft.com...
    > 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? karistetzer@charter.net
    >
    > Thanks
    >
     
  6. KariStetzer

    KariStetzer
    Expand Collapse
    Guest

    Re: Summarizing data from several databases to one

    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" wrote:

    > Perhaps I might ask as to why you have multiple dbs, when the data contained
    > is apparently related?
    >
    > "KariStetzer" <KariStetzer@discussions.microsoft.com> wrote in message
    > news:5BB44D32-AF7B-4269-81FE-99209D54C388@microsoft.com...
    > > 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? karistetzer@charter.net
    > >
    > > Thanks
    > >

    >
    >
    >
     
  7. DL

    DL
    Expand Collapse
    Guest

    Re: Summarizing data from several databases to one

    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.

    "KariStetzer" <KariStetzer@discussions.microsoft.com> wrote in message
    news:FD463CFB-5896-41C7-90A3-B88B51DAD253@microsoft.com...
    > 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" wrote:
    >
    >> Perhaps I might ask as to why you have multiple dbs, when the data
    >> contained
    >> is apparently related?
    >>
    >> "KariStetzer" <KariStetzer@discussions.microsoft.com> wrote in message
    >> news:5BB44D32-AF7B-4269-81FE-99209D54C388@microsoft.com...
    >> > 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? karistetzer@charter.net
    >> >
    >> > Thanks
    >> >

    >>
    >>
    >>
     

Share This Page