First Entry

[Guest]

Hi there.

I have a table that looks like this:

|ID| --- |COMMENT| --- |DATE|
200601 testing123 3/27/2006
200601 testing456 4/21/2006
200601 testing789 5/8/2006

I want to be able to query the first entry (so only 1 comment, id, and date
is displayed).

For instance, the query would result in:
200601 testing123 3/27/2006
since it was the first entered comment.

This table has numerous comments associated with each id. Each Id has around
2-5 comments, but all I need is the first entered one.

Thanks for the help.
-State

 

[[MVP] S.Clark]

Two Queries
1. qryMinDatePerID: Select ID, Min(Date) From [Tablename] GroupBy ID
2. qryFirstCommentPerDay:
For this one, add your datatabe and qryMinDatePerID to a new query. Link
them by ID and Date. Add desired columns to display.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Marshall Barton]

I have a table that looks like this:

|ID| --- |COMMENT| --- |DATE|
200601 testing123 3/27/2006
200601 testing456 4/21/2006
200601 testing789 5/8/2006

I want to be able to query the first entry (so only 1 comment, id, and date
is displayed).

For instance, the query would result in:
200601 testing123 3/27/2006
since it was the first entered comment.

This table has numerous comments associated with each id. Each Id has around
2-5 comments, but all I need is the first entered one.

SELECT T.ID, T.Comment, T.[Date]
FROM table As T
WHERE [Date] = (SELECT Min(X.[Date])
FROM table As X
WHERE T.ID = X.ID)

Note the use of [ ] in a (probably inadequate) attempt to
avoid problems with your use of a reserved word as a field
name. You should change the name of the field to something
other than Date to prevent other problems.

 

[John Spencer]

SELECT ID, Comment, [Date]
FROM YourTableName as Y
WHERE Y.Date =
(SELECT Min(Y1.Date)
FROM YOURTableName as Y1
WHERE Y1.ID = Y.ID)

Or use two queries.
First query saved as qFirstCommentDate
Build an aggregate query on the table, where you group by ID and get the
Min(imum) date

Using that saved query and your table in a second query as sources, join the
table and the query on the Id and Date fields and show the desired fields
from the table

OR an all in one query that may be faster (but may not be updatable)

SELECT Y.Id, Y.Comment, Y.Date
FROM YourTable as Y INNER JOIN
(SELECT ID, Min(Y1.Date) as FirstDate
FROM YOURTableName as Y1
GROUP BY ID) as Y2
ON Y.ID = Y1.ID and Y.Date = Y1.FirstDate