Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare query (2000)

    I have this query with the following fields: Enroll Date ( I have a parameter Between[Start Date] and [End Date] for this field, Client Number and Client Name. If I put in a begin date of 2/1/04 and an End date of 3/1/04, I could have the same client number with a 2/1/04 date and the same Client number with a 3/1/04 date. What I would like to do is find the client numbers that do not have a record for 2/1/04. Is there a way to do this in the query.

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

    Re: Compare query (2000)

    Try a query with this SQL:

    SELECT DISTINCT [Client Number] FROM qrySomething WHERE [Client Number] Not In (SELECT [Client Number] FROM qrySomething WHERE [Enroll Date] = #2/1/04#)

    where qrySomething is the name of your query.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare query (2000)

    Can the WHERE [Enroll Date] = a variable (like the End Date parameter) instead of a specific date? This is for people to use without my intervention. Is there a way to do this all in the same query?

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

    Re: Compare query (2000)

    Yes, just use

    ... WHERE [Enroll Date] = [Start Date])

    or

    ... WHERE [Enroll Date] = [End Date])

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare query (2000)

    That isn't working. My query that I am using is a parameter query and it comes up and asks me for the Start and End date again. Is there any way to do this in the original query. I have the Enroll Date, Client Number and Client Name. When I run this query, I get 2 records - one that has 02/1/04 with client number 000123 and one that has 03/1/04 with client number 000123. I only want to retrieve the ones that there isn't two records for. Is this possible in one query. I want the people who will use this to be able to put the dates they want and find the records that are new compared to last month. I hope this isn't too confusing. Thanks for your help.

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

    Re: Compare query (2000)

    Sorry, I don't understand.

  7. #7
    New Lounger
    Join Date
    Nov 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare query (2000)

    SELECT [Client Data].[Client Number], [Client Data].[Client Name], Count([Client Data].[Client Number]) AS [CountOfClient Number]
    FROM [Client Data]
    WHERE ((([Client Data].[Enroll Date]) Between [Start Date] And [End Date]))
    GROUP BY [Client Data].[Client Number], [Client Data].[Client Name]
    HAVING (((Count([Client Data].[Client Number]))=1));


    This query uses COUNT and grouping to select records between the given dates which have one record. That's the general idea, at least, isn't it?

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare query (2000)

    Thank you so much - with a few minor adjustments, this worked perfectly. I know my explanation of the problem was hard to follow.

Posting Permissions

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