Stacked query filter to just earliest date row

[forrestgump]

Hi there,

I have a stacked rows of data in a table and i want the query to just return the entire row of the row with the earliest date. I have tried a varitety of things to achieve this but with no luck. I tried by Group By on just the emp id record and then selecting on first for all the other records, but unfortunately this doesn't work even when when the values are sorted e.g.:-

user-id	custom-string1
Group By	custom-string2
First	custom-string3
First	custom-string4
First	custom-string5
First	custom-string6
First	start-date

I have also tried using min on just the start-date field but this mixes up information on the rows:-

Group By	custom-string2
First	custom-string3
First	custom-string4
First	custom-string5
First	custom-string6
Min	start-date

Any ideas anyone? Any help would be greatly appreciated.

Regards,

ForrestGump

 

[jdraw]

Please tell us more about:

-stacked rows of data in a table
-I have tried a varitety of things to achieve this (show us)
-emp id record

What is your table design? Fields and data type
Show us the SQL view of the query you tried.

Something along this syntax may offer a starting point.(untested)
You have to replace yourTable with the actual name of your table.
Also, Access does not like the "-" symbol in field names--enclose that field in square brackets as shown.

Select * from yourtable
where [start-Date]=(Select min([start-Date]) from yourTable as A)

 

[arnelgp]

SELECT T1.[user-id],T1.[start-date], (SELECT [custom-string1] FROM Table8 WHERE [user-id]=T1.[user-id] AND [start-date]=T1.[start-date]) As [custom-string1],(SELECT [custom-string2] FROM Table8 WHERE [user-id]=T1.[user-id] AND [start-date]=T1.[start-date]) As [custom-string2],(SELECT [custom-string3] FROM Table8 WHERE [user-id]=T1.[user-id] AND [start-date]=T1.[start-date]) As [custom-string3],(SELECT [custom-string4] FROM Table8 WHERE [user-id]=T1.[user-id] AND [start-date]=T1.[start-date]) As [custom-string4],(SELECT [custom-string5] FROM Table8 WHERE [user-id]=T1.[user-id] AND [start-date]=T1.[start-date]) As [custom-string5] from (SELECT Table8.[user-id], Min(Table8.[start-date]) AS [start-date]
FROM Table8
GROUP BY Table8.[user-id]) AS T1


just replace Table8 in the query to your actual table name.