Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query: Single instance of multiple cases (MS Access 2000 SR-1)

    I have a query that shows: Salesperson's first and last name, sales location, our product number and status.

    I want to show all salespeople's "closed" items (ie status "closed") - but only one example of each item (if they closed a sale on 4 of the same item, I only want to show that they closed **at least one time**) Sometimes more than one salesperson works on the same sale, so I want to show each salesperson's closed items under their name.

    The end result will be a report showing that salesperson Z closed a deal for **at least** one product W **at least** one product X and **at least one product Y**. and so on for all of the salespeople.

    I am having difficulty showing only **one instance** of a sale of an item, where there are multiple sales of the same item, and multiple salespeople who worked on the same sale.

    Any ideas?

    Thanks,

    JoeK

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

    Re: Query: Single instance of multiple cases (MS Access 2000 SR-1)

    Create a query based on your table.
    Add: last name, first name, product number and status.
    Change the query to a Totals query (View | Totals).
    Set the criteria for Status to Closed and set the Total option to Where.
    Add a calculated column

    Number: Count(*)

    Set the criteria for this column to >0 and the Total option to Where (unless you want to see the number of closed deals for each product)

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query: Single instance of multiple cases (MS Access 2000 SR-1)

    Yeah! Thanks for the solution. It was driving me nuts.

    Much appreciated, Hans (gold star to you!)

    JoeK

Posting Permissions

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