Return First Record of Each day based on [Date/Time] field (1 Viewer)

[Gigitty]

Hi guys.

Have had a look around on this one and surprisingly can't find any answers. I'm simply trying to return the first record for each day based on a [Date/Time] field (see screen shot example below). Any help would be greatly appreciated.

 

[arnelgp]

create 2 queries.

1st query (qryGroupedDays)

select studydate, Min([date/time]) As Dte from yourTable group by studydate

2nd query, join qryGruopedDays with "yourTable":

Select A.studydate, A.Dte As [Date/Time], B.[Accession Number]
B.Needles, [B.Gloves Off]
From qryGroupedDays As A Inner Join yourTable As B
On A.Dte = B.[Date/Time]

 

[plog]

To get the full record requires a sub query to first get the first value of a day.

 SELECT DateValue ([Date/Time]) AS MeaningfulFieldName, MIN(([Date/Time]) AS EarliestTime
FROM YourTableNameHere
GROUP BY DateValue ([Date/Time])

Then you build another query using it and YourTableNameHere, joining them via [Date/Time] to EarliestTime to get all the fields from YourTableNameHere.

 

[plog]

Looking at your data closer:

1. There's multiple 'first' records. 4/1/2022 has 2 'first' records.

2. Your storing duplicate data. Study date and Needle is just the date and time of Date/Time broken out seperately, you shouldn't do that, just use Date/Time

3. Poor names. Only use alphanumeric characters in names (no slashes or spaces). And give meaningful names to fields. What date/time does Date/Time represent? Prefix or suffix that into the name.