Domain Aggregate Functions in Query Expressions

[KernelK]

Alright, I've got all the pieces to this puzzle, I just can't fit them together properly. I have two tables, tblTickets and tblTicketNotes. They are in a one-to-many relationship, there can be multiple Notes attached to a single ticket. I am trying to set up a query that will pull the first (earliest by date) note for each individual Ticket. Here are the fields from each table that would be of concern here:

tbTickets
TicketID - Autonumber - Primary Key
Issue - Text - I will be using this later as a criteria to limit with, but not neccessary

tblTicketNotes
NoteID - Autonumber - Primary Key
TicketID - Long Integer - This is the foreign key of the relationship
DateStamp - Date/Time - This is the Note Date, I only want the first (earliest date) one
Content - Memo -This is the note information I want

So all I want is the Content of the first/earliest Note for each individual Ticket. I know this should be fairly easy, but I am at a loss.

 

[Jon K]

You can first build a Totals query from tblTictetNotes, Group By TicketID and Min DateStamp.

qryOne:-
SELECT TicketID, Min(DateStamp) AS MinOfDateStamp
FROM tblTicketNotes
GROUP BY TicketID;


Then link the Totals query to tblTicket and back to tblTicketNotes in a second query.

qryTwo:-
SELECT tbTickets.TicketID, tbTickets.Issue, tblTicketNotes.NoteID, tblTicketNotes.DateStamp, tblTicketNotes.Content
FROM (tbTickets INNER JOIN qryOne ON tbTickets.TicketID=qryOne.TicketID) INNER JOIN tblTicketNotes ON (qryOne.TicketID=tblTicketNotes.TicketID) AND (qryOne.MinOfDateStamp=tblTicketNotes.DateStamp)
ORDER BY tbTickets.TicketID;

Run the second query.
.

 

[KernelK]

That was exactly what I needed. Thank you friend.