Max Values Query

[Freefall69]

Running Access 2016 and importing a .csv file from server, which contains equipment usage information. The usage information is provided in 15-minute intervals (collection) each day by ID and channel as shown in example. The example shows ID’s pf ACME 01 and ACME 02 and associated channels (1-1-1, 1-1-2) with collection time and associated in and out usage for a two day sample. I am looking for help on methods to take 7 days of collected data and only report the max usage interval for each ID and channel. I have provided a sample of the collected data and desired report output based on export of data from Access query. I am uncertain if this can be reported in final query this way or will need to be separated into in usage and out usage on separate record lines. I believe the sample data and desired output as shown in the attached spreadsheet communicates what I am trying to accomplish but please let me know if additional info is needed.

 

[dhookom]

This query will get you most of the way there. Let us know if you have an issue with the other columns:

SELECT tblFreefall.ID, tblFreefall.Channel, 
(SELECT TOP 1 [Collection]
 FROM tblFreefall F
 WHERE F.ID = tblFreefall.ID and F.Channel = tblFreefall.Channel ORDER BY In_Usg DESC)
   AS Max_in_usage_interval, Max(tblFreefall.In_Usg) AS MaxOfIn_Usg
FROM tblFreefall
GROUP BY tblFreefall.ID, tblFreefall.Channel;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Freefall69]

Duane,

Thanks for the response. I will try it out but wanted to inquire if both the max_in_usg and max_out_usg can be queried and included on one record line as shown in my example. Looks like the SQL statement provided will query for the max_in_usage only. May need to pull the max_out_usg separately but wanted to inquire about what is possible and then try out methods from there. Thanks!!

 

[dhookom]

You just do the same with the Out_usg as I did with the In_usg. Please try it and come back if you have more questions.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Freefall69]

Duane,

This works as desired to pull the max in_usg and max_out_usg from intervals collected based on my testing. Thank You for the help!

One thing that have noticed is there looks to be some outliers in the data that will need to be reviewed. Would like to be able to pull a daily max_in_usg and maz_out_usg and show for each day of study interval. The time field in my sample would be the collection date. I have included sample of desired output below for your review.

 

[dhookom]

Hi Freefall69,
I believe all you would need to do is display totals in your query design and select the field you want to group by (Time), and the fields you want the max (In_usg and out_usg).

Your question doesn't seem to correlate with your image. There are lots of other fields, duplicate Time values, and no max_out_usg.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Freefall69]

Hi Duane,

I left the out_usg off the sample since would just be a matter of changing query to pull based on max_in_usg statement.

Sample shows the max_in_usg for each day. I collect 96 intervals (15-min samples) daily and looking to query for the max_in_usg and max_out_usg and show for each day of study (ex Last 7 days).

Daily report would show as in the sample: id, channel, collection, and max_in_usg and max_out_usg for each day. If compare my original spreadsheet attached with image provided the requirements should be clear. If not can put together another larger sample.

 

[dhookom]

Does this provide what you need:

SELECT tblFreefall.Time, tblFreefall.ID, tblFreefall.Channel, tblFreefall.Collection, tblFreefall.In_Usg, tblFreefall.Out_Usg
FROM tblFreefall
WHERE (((tblFreefall.In_Usg)=(SELECT Max(In_Usg) FROM tblFreefall F WHERE Datevalue(F.Time) = DateValue(tblFreeFall.Time)))) OR
 (((tblFreefall.Out_Usg)=(SELECT Max(Out_Usg) FROM tblFreefall F WHERE Datevalue(F.Time) = DateValue(tblFreeFall.Time))));

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Freefall69]

Duane,

I have attached a new sample adding in the daily query output that is also desired based on data set provided at top of sheet. I reviewed manually to obtain the correct desired results. I have also included the output of the latest daily query SQL statement you provided at bottom of spreadsheet which does not provide correct results.

The overall max_in_usg and max_out_usg query works well as mentioned and results have also been provided. Thanks for the help on that one.

 

[dhookom]

So when you stated "query for the max_in_usg and max_out_usg and show for each day" did you actually want by Day and ID and Channel?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Freefall69]

For the daily query that would be correct. Would like to get results as shown in the additional sample provided in spreadsheet. Apologize if statement was confusing.

 

[dhookom]

Can you figure out how to add the other columns in the subquery? The one I provided uses only the datevalue of the Time field. You would need to add the other two fields.

(SELECT Max(In_Usg) FROM tblFreefall F WHERE Datevalue(F.Time) = DateValue(tblFreeFall.Time))

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Freefall69]

Duane,

I will figure out how to add the other fields to pull into results. I am definitely further along with figuring this all out.

Thank you very much for the help!!

 

[Freefall69]

Hi Duane,

Wanted to circle back with you since having an issue with original SQL statement (7 Aug 19 18:39)when running against the dataset. Expanding on the small sample provided the data set has ACME 1, ACME 2,ACME 3,ACME 4, and each one of those has channel 1-1-1 thru 1-1-8 and then 1-2-1 thru 1-2-8 which a max value needs to be shown in query. The data set is exact in fields to sample with those additional records being the only difference

I am receiving an error "At most one record can be returned by the subquery" and uncertain why since it worked fine on the original smaller sample.

Any additional guidance would be appreciated to get this solved since looking to produce some reporting.

 

[dhookom]

Please provide the SQL of your query that resulted in the error. Do you have duplicate In_usg and Out_usg? If you do, how do you intend to break ties.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Freefall69]

Would be the SQL you provided with just replacing table and field names. I am running in_usg and out_usg separately currently and simply replacing in_usg with out_usg in SQL to get each. Getting duplicate records based on review but can't save results since it errors out.

 

[dhookom]

So you do have duplicate values? If this is the case then you need to answer my previous question "...how do you intend to break ties."

Maybe you could provide actual data since your earlier data worked as you requested.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Freefall69]

Duane,

I have uploaded an excel file showing 1 day of collection based on time field (8\6\2019) for review. I also included the SQL statement on another tab for review. Ideally would like to run for both in_usg and out_usg and should be no ties except in the case of "0" which does occur in this file.

 

[dhookom]

It looks like you might have a ton of records. I would not attempt to perform the queries you desire in MS Access. Consider moving your data to SQL Server (there is a free Express version).

SQL server offers a lot more functionality to meet your needs.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016