Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with a Query (Access 97)

    Edited by HansV to use tables

    I need some help building a query. I am attempting to create a report for an existing database. The table captures associate errors through a quality review. The problem that I am running in to is that an associate can have several errors for 1 account, therefore the account number is entered into the table numerous times. To calculate an error weight for this person, the weight of the error is divided by total accounts reviewed. Hence comes the problem, if there are multiple rows with the same account for the same associate, how can I get this to distinguish the account number only once for that associate?

    tblQASample
    <table border=1><td>QASampleID</td><td>AutoNumber</td><td>WORKDATE</td><td>Date</td><td>QASampleAssoc</td><td>Text</td><td>Account Number</td><td>Text</td><td>Error Weight</td><td>Number</td></table>
    <table border=1><td>QASampleID</td><td>WORKDATE</td><td>QASampleAssoc</td><td>AccountNumber</td><td>Weight</td><td align=right>79607</td><td align=right>06/09/2005</td><td>John Smith</td><td align=right>1236</td><td align=right>0</td><td align=right>79602</td><td align=right>06/09/2005</td><td>John Smith</td><td align=right>1235</td><td align=right>0</td><td align=right>79611</td><td align=right>06/09/2005</td><td>John Smith </td><td align=right>1234</td><td align=right>0.3</td><td align=right>79612</td><td align=right>06/09/2005</td><td>John Smith </td><td align=right>1234</td><td align=right>0.2</td><td align=right>79614</td><td align=right>06/09/2005</td><td>Mary Smith </td><td align=right>1234</td><td align=right>0.2</td><td align=right>79616</td><td align=right>06/09/2005</td><td>John Smith </td><td align=right>1238</td><td align=right>0.1</td><td align=right>79605</td><td align=right>06/09/2005</td><td>John Smith </td><td align=right>1239</td><td align=right>0</td></table>
    In this instance John Smith has a total sample size of 5 accounts. Mary Smith has a total sample size of 1 account.

    Thanks for any assistance taht you can provide.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help with a Query (Access 97)

    I'm not entirely sure what you mean. Does the following do what you want?

    - Create a query based on tblQASample.
    - Add the QASampleAssoc, AccountNumber and Weight fields.
    - Change the query to a Totals query (View | Totals)
    - Leave the Total option for the first two fields as is (Group By), and change it for Weight to Avg (Average).

    The weights for records with the same Associate and Account Number will be added and divided by the number of records.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with a Query (Access 97)

    Hans,

    There is a good portion of the information that you provided that I can put to use to resolve my problem. Thanks again!!!

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with a Query (Access 97)

    Hans,

    Have another ? for you about this query that I have built. The account numbers are showing mutiple times if the WORKDATE is different. This is causing my count of samples to exceed what it should be. I have set the query up to group as follows:

    SELECT tblExpressQASample.WORKDATE, tblExpressQASample.Manager, Associate.AssocName, tblExpressQASample.AccountNumber, Count(tblExpressQASample.AccountNumber) AS Samples
    FROM Associate INNER JOIN tblExpressQASample ON Associate.AssocID = tblExpressQASample.AssocID
    GROUP BY tblExpressQASample.WORKDATE, tblExpressQASample.Manager, Associate.AssocName, tblExpressQASample.AccountNumber
    ORDER BY Associate.AssocName;

    Which is producing a result like this:

    WORKDATE AssocName AccountNumber Samples
    6/8/2005 Smith, John 1234 1
    6/10/2005 Smith, John 1234 1

    ....and this is counting as 2 samples when it should be 1. Is there a way to remove the GROUP BY for WORKDATE so that I see the account number only once (probably not)? If not, is there a work around for this that you can think of?

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help with a Query (Access 97)

    Why not remove the WORKDATE field from the query entirely?

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with a Query (Access 97)

    If I remove the WORKDATE is there a way that I can pull data for a particular date range?

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need help with a Query (Access 97)

    Yes, just put the condition in the WHERE clause, viz:

    WHERE WORKDATE Between StartDate AND EndDate

    Of course you will have to change the StartDate and EndDate to your own variable names.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help with a Query (Access 97)

    In addition to Pat's remark: in design view, you set the Total option for WORKDATE to Where instead of Group By. You can specify criteria for the field, but it will not be displayed in the result.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with a Query (Access 97)

    That is good stuff!! Thanks to all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •