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]

Not tested, but give it a shot:
[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 GROUP BY ID ))
[/tt]



---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

Andy,

The PK would be the ID.
It is possible to have inbound or outbound records with same peak utilization for each element name(ex Acme-pe-2 at 80%)for 2 or more days over the 30 day study period. This could only be differentiated by the date column. In this case one of the elements would just need to be shown as possible.

 

[Andrzejek]

I see you replied to my original replay, but try what I suggested.


---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

Andy,

Yes, I think our responses crossed paths. That works for sample data which was provided. Having some issue with replacing my values in the SQL statement for table being used and will get it figured out.

Thanks for your assistance.

 

[Andrzejek]

Well, unfortunately I can only work with the "sample data which was provided" , but hopefully you can see my approach to your issue and extrapolate it to your needs.


---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

Andy,

I did get it working in expected manner.

Thanks again!!

 

[Freefall69]

Andy,

Upon further review if the peak value = 0 for all days indicating no data to report over study period then all records are shown. This would also be true I believe if we had the same peak value of any value. Is there a method to only show one of those records when this occurs?

 

[Freefall69]

I have updated data sample file and attached link for review. The SQL statement works well with the exception of when each day's reporting values are "0" for an element at which point all of those records are displayed.

 

[Andrzejek]

Try:
[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] [highlight #FCE94F]>[/highlight] 0 GROUP BY ID ))
UNION
SELECT * FROM MyTable
WHERE ID IN (SELECT DISTINCT ID FROM MyTable
WHERE [Peak Util] [highlight #FCE94F]=[/highlight] 0)
[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

Andy,

That actually adds zero records that were filtered out with original SQL statement. Uncertain what may be the issue at this point but getting 31 March "zero" records over the month for same elements where no usage was observed. I'll have to dig in further.

 

[Andrzejek]

Run them separately:
[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]
Above should give you the outcome as original
[tt]
SELECT * FROM MyTable
WHERE ID IN (SELECT DISTINCT ID FROM MyTable
WHERE [Peak Util] = 0)
[/tt]
This should give you records with [Peak Util] = 0



---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

What is the ACTAL value in [tt]Peak Util[/tt] field?

You show it as:
[pre]
Peak Util
50%
60%
80%
35%
40%
25%
21%
35%
0%
0%
0%
[/pre]
But that looks like a NUMBER formatted as Percentage by Excel.

How is [tt]Peak Util[/tt] defined in your Access table?


---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

The process has union query which pulls multiple excel files from system export and then make_table_query to get into one table. Peak Util is formatted as a number in source table which is source for SQL query.

Values are not formatted further in table:
Peak Util:
0
0.58265027
16.9833612
79.7787767
etc....

 

[Andrzejek]

So the original Statement:
[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 GROUP BY ID ))[/tt]

Still should work with [blue]this addition[/blue]:

[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 [blue]WHERE [Peak Util] > 0[/blue] GROUP BY ID ))
[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

Andy,

That works great with exception of now I don't see any of records with peak value of "0". Ideally I want to show one of these records in report to account for the element although no usage is observed.

 

[Andrzejek]

What do you get when you run this:
[tt]
SELECT * FROM MyTable
WHERE ID IN (SELECT DISTINCT ID FROM MyTable
WHERE [Peak Util] = 0)[/tt]

Assuming you do not have records where [tt]Peak Util[/tt] is 0 and <> 0 for the same [tt]ID[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

Get records that have peak util = 0 and > 0 for the elements with same ID when running. I pulled one element as a sample and see full 31 days of data. Can have an element where peak util could be zero for few days during month and have value on other days. I would of thought in that scenario it would have just pulled the max util and not any of the zero values. I did remove the date field as well from source table but did not have any impact.

Previously provided statement works to provide all elements by unique ID for the month but filters out the zero since showing all > 0

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 ))

 

[Andrzejek]

OK, so what do you get when you run this:

[PRE]
SELECT * FROM MyTable
WHERE ID IN (SELECT DISTINCT ID FROM MyTable WHERE [Peak Util] = 0)
AND ID [highlight #FCE94F]NOT[/highlight] IN (SELECT DISTINCT ID FROM MyTable WHERE [Peak Util] [highlight #FCE94F]>[/highlight] 0)
[/PRE]


---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

Get the elements that show peak util = 0 for 31 days only it appears. No peak util shown other than "0" based on count by unique ID and data output review.