How could I extract the first record for everday

[henniec]

Good day,
I need to create a query to select the first reading for every day where the data does not equal zero or less. I have a table generated by a data logger and records the data for every hour. The data recorded is for flow and there are times where there is no flow and as such I cannot select a specific time. I also need to specify a time after 8:00am as the day runs from 8:00am to 7:59am the following day.

How would I go about to create a query to extract the required data as I also have to create a second query based on the first by adding 4 hours to the time recorded.

Thank you for your time.
Hennie

 

[dhookom]

Provide significant table and field fields as well as some sample records and desired output.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[henniec]

Thank you for the reply. Attached is the input table and the result expected. If it is easier to do this via code I would go for it. I have deleted several data in between to limit the lenght of the table but the idea is still there.

Thanks.

Hennie

Input

sDate sTime Flow pH
01/01/2007 1:00 0 8.12
01/01/2007 2:00 0 8.12
01/01/2007 3:00 0 8.11
01/01/2007 4:00 0 8.1
01/01/2007 5:00 0 8.1
01/01/2007 6:00 0 8.12
02/01/2007 4:00 0 8.23
02/01/2007 5:00 0 8.19
02/01/2007 6:00 0 8.24
02/01/2007 7:00 0 8.21
02/01/2007 8:00 0 8.21
02/01/2007 9:00 4060 8.19
02/01/2007 10:00 4190 8.12
02/01/2007 11:00 4230 8.11
03/01/2007 9:00 0 8.23
03/01/2007 10:00 0 8.22
03/01/2007 11:00 0 8.1
03/01/2007 12:00 3650 7.93
03/01/2007 13:00 3720 7.8
03/01/2007 14:00 3600 7.93
03/01/2007 15:00 3590 8.43
03/01/2007 16:00 0 8.29
04/01/2007 1:00 0 8.08
04/01/2007 2:00 0 8.09
04/01/2007 3:00 0 8.12
04/01/2007 4:00 0 8.03
04/01/2007 5:00 3640 8.34
04/01/2007 6:00 3480 8.29
04/01/2007 7:00 3500 8.25
04/01/2007 8:00 3500 8.22

Output
sDate sTime Flow pH
02/01/2007 9:00 4060 8.19
03/01/2007 12:00 3650 7.93
04/01/2007 5:00 3640 8.34

 

[PHV]

A starting point:

SELECT A.sDate, A.sTime, A.Flow, A.pH
FROM Input AS A INNER JOIN (
SELECT sDate, Min(sTime) As FirstTime FROM Input GROUP BY sDate
) AS F ON A.sDate = F.sDate AND A.sTime = F.FirstTime

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[henniec]

Thank you,

I have tried the sql and created two additional tables Input with the data and A as empty with the same fields. Created a new query and copied the sql as is.

The result remained the same as the input. Should there not be something that refer to the flow as it is the one that will determine the first record for the output table.

I will only be able to check on any reply on Monday.

Hennie