×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Need to query for MAX value

Need to query for MAX value

Need to query for MAX value

(OP)
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.

RE: Need to query for MAX value

Not tested, but give it a shot:

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




---- Andy

There is a great need for a sarcasm font.

RE: Need to query for MAX value

(OP)
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.


RE: Need to query for MAX value

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


---- Andy

There is a great need for a sarcasm font.

RE: Need to query for MAX value

(OP)
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.

RE: Need to query for MAX value

Well, unfortunately I can only work with the "sample data which was provided" smile, 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.

RE: Need to query for MAX value

(OP)
Andy,

I did get it working in expected manner.

Thanks again!!

RE: Need to query for MAX value

(OP)
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?

RE: Need to query for MAX value

Try:

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 ))
UNION
SELECT * FROM MyTable
WHERE ID IN (SELECT DISTINCT ID FROM MyTable
WHERE [Peak Util] = 0)


---- Andy

There is a great need for a sarcasm font.

RE: Need to query for MAX value

(OP)
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.

RE: Need to query for MAX value

Run them separately:

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

Above should give you the outcome as original

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

This should give you records with [Peak Util] = 0


---- Andy

There is a great need for a sarcasm font.

RE: Need to query for MAX value

What is the ACTAL value in Peak Util field?

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

How is Peak Util defined in your Access table?


---- Andy

There is a great need for a sarcasm font.

RE: Need to query for MAX value

(OP)
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....



RE: Need to query for MAX value

So the original Statement:

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


Still should work with this addition:

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


---- Andy

There is a great need for a sarcasm font.

RE: Need to query for MAX value

(OP)
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.

RE: Need to query for MAX value

What do you get when you run this:

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


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


---- Andy

There is a great need for a sarcasm font.

RE: Need to query for MAX value

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

RE: Need to query for MAX value

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

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)
 


---- Andy

There is a great need for a sarcasm font.

RE: Need to query for MAX value

(OP)
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.

RE: Need to query for MAX value

So I assume you get the right data out of this:

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

and the rest of the data out of this:

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)

So you should be able to combine the two and get what you need with the UNION, right?

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


---- Andy

There is a great need for a sarcasm font.

RE: Need to query for MAX value

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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close