Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    First Record Per Person (2000)

    Hi,
    I have a list of customers who have signed up for jobs. They can have lots of jobs over time. I want to be able to filter a list of all customers and only show the first time they used me for a job. So, if Bob M first signed up on Aug 5 2001 and then hired me again Sep 10 2001 and so on and Mary B signed up the first time Sep 7 2001 and again on Sep 8 2001, I would want a query that shows me just Bob M | Aug 5 2001 and Mary B | Sep 7 2001.

    Thanks,
    Russ

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

    Re: First Record Per Person (2000)

    You need a subquery to select the 1st record for each customer, then use the results of that subquery as Selection Criteria. Since date seems to be the controling field here, then then the subquery would start out something like (assuming your query is using "FROM Customer":
    <pre>Select Min(HireDate) from yourtable where yourtable.customerID=Customer.customerID</pre>

    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Apr 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First Record Per Person (2000)

    But then all I have is a query showing one date that is no longer associated with any person. I need to see the first record, by date, of all the customers. And if I use this subquery with just dates in it I can get everyone associated with that date whether it's their first time or second time.

    Russ

  4. #4
    Lounger
    Join Date
    Apr 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    I got it now!

    I was trying to set it up using the sql and instead I went and used the total criteria and put min there. Thanks for your help! That was great.

    Russ

  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: First Record Per Person (2000)

    Then you aren't doing it right. This becomes a tad more difficult if you are trying to get records from the same table. In that case, you subquery needs to differentiate between the Transaction table in the query and the Transaction table as used in the subquery, like this:
    <pre>Select Min(hiredate) from Transaction as T where T.customer=Transaction.customer </pre>


    This would then be the selection criteria of the HireDate field in your query.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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