Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count in query (Access 97)

    (Subject edited by HansV to something more descriptive than 'Access' to assist future searches.)

    I have a sales database that contains two tables: Salesmen and Items. Both tables contain a name and an ID number. The two tables are related many-to-many, and the link is a SalesmenNo-ItemNo table.

    Concentrating on a particular salesman, Smith, I

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

    Re: Count in query (Access 97)

    There are several ways to do this. Here is a method that involves creating 3 queries. Let's say the SalesmanID for Smith is 37.

    1. <LI>Create a query based on the Salesmen-Items table.
      Add the ItemID field and the SalesmanID field to the grid.
      Enter 37 in the criteria for SalesmanID.
      Clear the Show check box of SalesmanID.
      Set the Unique Values property of the query to Yes.
      This query produces a list of distinct items sold by Smith.
      Save it as qrySmith
      <LI>Create another query based on the Salesmen-Items table.
      Add the ItemID field and the SalesmanID field to the grid.
      Enter Not 37 in the criteria for SalesmanID.
      Clear the Show check box of SalesmanID.
      Set the Unique Values property of the query to Yes.
      This query produces a list of distinct items sold by other salesmen.
      Save it as qryOthers
      <LI>Create a new query based on qrySmith and qryOthers.
      Join the queries on ItemID.
      Double click the join, and select the option to return ALL records from qrySmith, then click OK.
      Add the ItemID from qrySmith and the ItemID from qryOthers to the grid.
      Enter Is Null in the criteria line for the second column.
      Clear the Show check box for this column.
      This query will return the ItemID's sold only by Smith.
    If you also want to see the item descriptions, add tblItems to the 3rd query, join it to qrySmith on ItemID (not to qryOthers!), and add the item description field to the grid.

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count in query (Access 97)

    Thank you for you very helpful suggestion - I just coded it, and it worked like a charm.

Posting Permissions

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