Results 1 to 7 of 7
  1. #1
    MatthewGrace is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2013
    Posts
    159

    SQL Aggregate function confusion...

    Thank you for your willingness to help me.

    I have taken my problem out of its massive context and created a demonstration table illustrating it on a small, easy-to-read scale.



    The goal is to produce a recordset yielding information about the earliest shipment to this customer. Here's a graphic that I hope says it all:
    Click image for larger version. 

Name:	Commitment.jpg 
Views:	25 
Size:	161.6 KB 
ID:	46822

    I deliberated on whether to include this last part, but if you're wondering, the reason I must have it in one record is because I intend to take this table and join it to another query involving the Orders table using the Customer_ID key. If you're following that in your mind's eye, you can see the problem... I'll accidentally generate 2 duplicate line items for every order.

    Besides, it kinda irks me that I know how to use Min/Max etc. aggregate functions to discover a single sought after record, but I don't know to read any other fields containing pertinent information about that record because inclulding them in the SQL statement creates more records, thus defeating the purpose!

    Where am I going wrong?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make 2 queries:
    Qry1 you have ,show ing the 2 fields.

    Qry2,join Qry1 with the data table on date again to get all the data
    select *.tData from Qry1,tData where tData.date=Qry1.Date

  3. #3
    MatthewGrace is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2013
    Posts
    159
    Thank you Ranman. That's exactly what I was going to do if all else failed and I couldn't get an answer. I see now, that is the answer.

    I posted because I wasn't sure if wise DB developers knew of another way. And I'm still a bit curious if there is another... Maybe a SQL keyword of some kind that forces only shows 1 Row (kinda like how you can show only the top 10 or top 100 records somehow). I thought that might be it. Hm.

    Thank you again.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Could try:

    SELECT TOP 1 * FROM tblShipments WHERE Customer_ID=3 ORDER BY ShipmentDate;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Use the first query as a subquery?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No. Did you try the TOP 1 suggestion?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    MatthewGrace is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2013
    Posts
    159
    Thank you Ranman, and June7 as always. These suggestions both work, each with their own circumstantial idiosyncrasies. I have adapted a version of your suggestions into my rather complex situation. Thank you again for your veteran input.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Aggregate Function Expression Error
    By spyldbrat in forum Access
    Replies: 4
    Last Post: 05-29-2019, 02:12 PM
  2. aggregate function help
    By ltcarlisle in forum SQL Server
    Replies: 16
    Last Post: 06-24-2014, 01:34 PM
  3. Cannot Have Aggregate function
    By vitordf in forum Queries
    Replies: 4
    Last Post: 08-13-2013, 07:24 AM
  4. more aggregate function questions
    By boutwater in forum Access
    Replies: 6
    Last Post: 09-29-2011, 02:53 PM
  5. aggregate error due to function
    By boutwater in forum Access
    Replies: 2
    Last Post: 09-26-2011, 03:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums