Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. Freefall69

    Parse field and perform calculation

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

    Parse field and perform calculation

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

    Parse field and perform calculation

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

    Parse field and perform calculation

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

    Parse field and perform calculation

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

    Parse field and perform calculation

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

    Max Values Query

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

    Max Values Query

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

    Max Values Query

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

    Max Values Query

    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!!
  11. Freefall69

    Max Values Query

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

    Max Values Query

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

    Max Values Query

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

    Max Values Query

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

    Max Values Query

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

    Max Values Query

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

    Need to query for MAX value

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

    Need to query for MAX value

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

    Need to query for MAX value

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

    Need to query for MAX value

    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.

Part and Inventory Search

Back
Top