Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    8

    Unanswered: Help selecting first and last entries by id

    Here's a problem I hope someone can help me with. Let's say I have a table with sales information by salesperson (includes salespersonid, date, and feedback on sale). A salesperson may have one or more entries for sales. I want to take the everyone with at least two entries and put their results into two separate tables by date:

    Table 1 - records of all first sales
    Table 2 = records of all last sales

    I don't want to include anyone with only one sale in either of those tables. The idea is to compare performance between the first sale and last sale for each person.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I am not sure how your sales are stored. But if they are by date then you can use Min and Max to find the first and last date of their sales. Then compare the Min and Max to see if they are the same date (equals one sale).

    A question I have is... Can a sales person have more than one sale on a day? If you have two sales on one day then is that qualify as two sales?

    If you can have two or more sales in a day, how can you determine the very last sales? Is there an Order ID or something like that?

  3. #3
    Join Date
    Aug 2006
    Posts
    8
    Thanks for your solution. Yes, it is possible for a salesperson to have more than one sale with the same date and adding a sales number would be a good solution and it's how I would have designed it; it's always easier if the person analyzing the data is the same as the person designing the database.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Find Dups

    You can do it via some queries or a function. If you do it via a function you can choose DAO or ADO code. The attachment is the query way you can do it by running the 2 make-table queries in the attachment (Modify it as you need to). It uses like DCKunkle mentioned, the Min and Max functions in the queries.
    Attached Files Attached Files
    Last edited by pkstormy; 10-05-06 at 18:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Aug 2006
    Posts
    8
    Thank you, Paul. Your example is exactly what I needed.

Posting Permissions

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