Search results for query: *

Thanks for the additional guidance. Working now for all values. I see where that would not work now with the case statement I tried for bps.

Subsequent to running for entire dataset there is an additional calculation that is needed. Since the case statement looks for (strValue, 5) it converts all K bps, M bps but have now discovered some that are just bps where that will not work. Most of the bps are "0" but some also have some...

Andy/Duane, Thanks for your help. This is now working correctly.

Andy, Actually it's not working. I was able to strip the units (K bps, M bps, and G bps) and thought I could just trim the field values and do calculation but value lengths do vary from 0 to 5 numbers So in using the InStr statement I have entered the following which is not working correctly...

Andy, Thanks for the suggestions. I have it working as desired now.

I am running Access 2016 and receiving a file from an external system which lists average and max usage for each interface. The challenge is that the information is all listed in one field to include the usage in Kbps, Mbps, and Gbps and need to do a calculation in that field based on value...

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

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.

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

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

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.

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

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

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

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

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

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

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.

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

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.