Need to query for MAX value 1

[Freefall69]

Hello,

I am using Access 2016 and need some assistance with pulling a max value for field based on 30 days of daily files. I have a few hundred elements which are collected daily and appended to a access table (Daily Usage Files) and need to pull max value of peak utilization for each one ( 2 records - one inbound and one outbound) based on 30 days of daily data. I have provided a link with a sample of two elements collected over four days in April and then desired output as well. Query needs to look at 30 days of data by Element Name and direction and pull MAX value of Peak Util for each and display only that line in output. I have tried a few max value functions in query but not working as expected. Any assistance and guidance would be appreciated. I believe the sample data accessible with link is self explanatory but please let me know if need more information or clarification.

 

[Andrzejek]

So I assume you get the right data out of this:
[tt]
SELECT * FROM MyTable
WHERE ID IN (SELECT DISTINCT ID FROM MyTable)
AND [Peak Util] IN (
SELECT MyPeakUtil FROM (SELECT MAX([Peak Util]) AS MyPeakUtil, ID
FROM MyTable WHERE [Peak Util] > 0 GROUP BY ID ))
[/tt]
and the rest of the data out of this:
[tt]
SELECT * FROM MyTable
WHERE ID IN (SELECT DISTINCT ID FROM MyTable WHERE [Peak Util] = 0)
AND ID NOT IN (SELECT DISTINCT ID FROM MyTable WHERE [Peak Util] > 0)
[/tt]
So you should be able to combine the two and get what you need with the UNION, right?
[tt]
SELECT * FROM MyTable
WHERE ID IN (SELECT DISTINCT ID FROM MyTable)
AND [Peak Util] IN (
SELECT MyPeakUtil FROM (SELECT MAX([Peak Util]) AS MyPeakUtil, ID
FROM MyTable WHERE [Peak Util] > 0 GROUP BY ID ))
[highlight #FCE94F]UNION[/highlight]
SELECT * FROM MyTable
WHERE ID IN (SELECT DISTINCT ID FROM MyTable WHERE [Peak Util] = 0)
AND ID NOT IN (SELECT DISTINCT ID FROM MyTable WHERE [Peak Util] > 0)
[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

This part produces desired results:

SELECT * FROM MyTable
WHERE ID IN (SELECT DISTINCT ID FROM MyTable)
AND [Peak Util] IN (
SELECT MyPeakUtil FROM (SELECT MAX([Peak Util]) AS MyPeakUtil, ID
FROM MyTable WHERE [Peak Util] > 0 GROUP BY ID ))


The second part shows all days for all elements that show "0" peak utilization during the month. (31 records for each)

Ideally just one record for each is desired. I will have to take this portion and clean it up further to get down to one day and then append to results of first statement.

SELECT * FROM MyTable
WHERE ID IN (SELECT DISTINCT ID FROM MyTable WHERE [Peak Util] = 0)
AND ID NOT IN (SELECT DISTINCT ID FROM MyTable WHERE [Peak Util] > 0)