Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    2

    Unanswered: Group by query - Pull out a row with certain criteria

    Hi all,

    I'm new with Access and learning.

    I have two table, Customer and Purchases.

    Customer:
    CID Name
    1 A
    2 B
    3 C

    Purchases:
    CID Date Item Total
    1 1/1/08 323 124
    1 4/3/08 121 90
    1 5/7/08 301 158
    2 2/4/08 121 320
    2 1/2/08 301 258
    3 6/4/08 323 109
    3 3/2/08 121 321

    I need to make a query with Customer ID, Name and his latest purchase, item and total of that purchase.

    I haven't been able to pull out the whole row on Purchases table if I group it by ID. Please help.

    Thanks

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Give this query a try and let me know if that's not what you are looking for.

    Code:
    SELECT DISTINCT Customer.CID, Customer.CName, Purchases.Item, Purchases.Total, Purchases.Date
    FROM Purchases, Customer
    WHERE Purchases.Date=(SELECT MAX(Date) FROM Purchases WHERE Customer!CID =Purchases!CID)
    C

  3. #3
    Join Date
    Aug 2008
    Posts
    2
    Thank you so much. It seem to do what I want. But I have to add INNER JOIN for the two table to get the correct result

    Can you explain why
    Code:
    (SELECT MAX(Date) FROM Purchases WHERE Customer!CID =Purchases!CID)
    only return result for that single CID? Does Customer!CID carry the value from the parent query? How about Purchases!CID? Since we have 2 table in parent query (Customer and Purchases)

    Thanks and sorry for the questions. I'm trying to learn MSSQL at the same time.

  4. #4
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    The SELECT MAX part is just a subquery. All it does is find the Last purchase date that matches the current CID and then uses that as a criteria for displaying the record.

    Think of the query as two separate queries.

    The first one just selects (I think that the proper term is 'projects' but college was too long ago at this point) the records from the two tables - Customer and Purchases. At this point you would have all the purchases from the Purchases table.

    The subquery further restricts which row to display. It's kind of like using a WHERE Date = #1/1/2008# but since you don't know what the date is you use the subquery to find the Max date and you use the Customer!CID =Purchases!CID to make sure that the subquery returns the correct date for the CID that was projected from the first part of the query.

    I hope that makes a little sense and doesn't complicate things more for you.

    C

Posting Permissions

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