Show last data entry per day

[MFS]

I have data going into a table every minute, (I'm monitoring critical Boiler data at my facility. This data is being generated via a PLC.)


I have one field that is a running sum of 'Gas Usage'

I'm needing a query that will only show the last 'Gas Usage' data entry for any given day.
What makes this difficult is that today the last entry may be at 23:59:32 and tomorrow it could be 23:59:54. This is due to the triggering and time within the PLC Logic and DB.

Any help would be greatly appreciated,

[Gicu]

Hi,

Create a separate totals query where you use group by your ID and Max([YourDate]), then in another query based on your full table you join both fields (ID to ID and [YourDate] to [MaxOfYourDate]).

Cheers,
Vlad

[MFS]

Thank You Gicu,
That does exactly what I am looking for.

[Gicu]

Glad to help!

[ArviLaanemets]

I have one field that is a running sum of 'Gas Usage'

I hope you have a datetime field too! Otherwise it will be Mission Impossible!

The saved query will be

qDateMaxTime = SELECT INT(YourDateField) AS DateInt, MAX(YourDateField) As MaxDateTime FROM YourTable GROUP BY INT(YourDateField)

The query to get latest GasUsage for every date

SELECT yt.YourDateField, yt.GasUsage FROM YourTable yt INNER JOIN qDateMaxTime mt ON mt.MaxDateTime = yt.YourDateField