Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query question (2003)

    I have a query which returns the following sample results...

    Contract, Name
    23, Joe Smith
    24, Mike Jones
    28, Tom Michaels
    28, Tom Michaels
    28, Tom Michaels
    29, Adam Brady

    Is there a way to take the above query and write a new query to filter out the results that have more than one record? So basically I would be eliminating 28, Tom Michaels? The new result set would appear like this...

    Contract, Name
    23, Joe Smith
    24, Mike Jones
    29, Adam Brady

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

    Re: Query question (2003)

    A relatively easy way is as follows:
    - Click New in the Queries section of the database window.
    - Select Find Duplicates Query Wizard and click OK.
    - Select the query you want to filter, and click Next.
    - Select the Contract field, click > and click Next.
    - Select the other fields you want to display or click >>, then click Next.
    - Specify a name (you probably don't want the suggested name), click the option to view the design of the query, and click Finish.
    Look at the criteria for the Contract field. You'll see something like

    In (SELECT [Contract] FROM [qrySomething] As Tmp GROUP BY [Contract] HAVING Count(*)>1 )

    Change Count(*)>1 to Count(*)=1
    Switch to datasheet view to see the result.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query question (2003)

    You could use the DISTINCT keyword, like this:

    SELECT DISTINCT [Contract], [Name] FROM .....

    Or, you can make it a TOTALS query, using the GROUP BY clause.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Query question (2003)

    I interpreted the question to mean that records that have a duplicate must be omitted altogether.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query question (2003)

    Hmm, looking back at it, I think you are right. You could do it in one SQL statement.

    SELECT Contract, [Name] FROM tblContract INNER JOIN (SELECT ContractID, Count(*) as CountOfContract FROM tblContract GROUP BY Contract) AS q on q.Contract = tblContract.Contract WHERE q.CountOfContract = 0
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2003)

    Hans,

    That worked perfectly! Thanks again!!!

Posting Permissions

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