Extract hourly data from process data base with excel and ms query

Question

I am quite new using SQL and in that specific case MS query. I have to extract data from a factory database where data are recorded every 5-7 seconds, using excel and ms query. To be able to complete my analysis, I have to use one year set of data. But of course getting every value recorded every 5 seconds on one year will not be possible with excel. So I would like to extract only one value per hour (that can be one value, or an average of all the data in the past hour), but I didn't succeed. I tried to use a GROUP BY, but I only get errors. I saw someone using a DATEADD but in SQL language, doesn't work with ms query. So if any of you had a solution ?

As requested, some more information : - my organization is limiting the software we can use and what we can install. That's the reason for the choice of MS query in Excel. Not sure I can install anything else, especially for accessing databases, as IT would not allow it. - as guessed, I use only 2 fields, the time_field and value_field. data are like this :

IP_TREND_TIME       IP_TREND_VALUE
01/01/2018 00:00    26.05134583
01/01/2018 00:00    28.601511
01/01/2018 00:00    25.26741219
01/01/2018 00:00    28.2553215
01/01/2018 00:00    25.7881279
01/01/2018 00:00    22.28141594
01/01/2018 00:01    25.02994537
01/01/2018 00:01    25.80338669
01/01/2018 00:01    28.67017555
01/01/2018 00:01    29.755476
01/01/2018 00:01    27.90055084
01/01/2018 00:01    30.32291794
01/01/2018 00:02    29.48653221
01/01/2018 00:02    29.06977081
01/01/2018 00:02    28.34592438
01/01/2018 00:02    24.93743706
01/01/2018 00:02    25.89684677
01/01/2018 00:02    29.04116058
01/01/2018 00:03    28.382164
01/01/2018 00:03    26.18581581
01/01/2018 00:03    26.03513527

my current query is :

SELECT TREND_TIME, TREND_VALUE
FROM my database
WHERE TREND_TIME>? And TREND_TIME<?
ORDER BY TREND_TIME

As you can see on the list of data, I have plenty of lines and would like to extract only one from this list, that can be one value or the average of all the values in the hour. And as said above, DATEADD is noot known in MS QUery, unfortunately....

Hope I was clearer this time in my explanations Thanks and regards Makol

Please add some of your code, or additional information so we can have something to work with. Thanks — Nerdi.org, Jul 26 '18 at 19:32

DavidP · Answer 1 · 2018-08-01T12:46:56.287

Is there a reason you need to use MS Query? You might have better luck using another application to query the data and export it as CSV, then opening that in Excel. If you're using a Microsoft SQL server, SSMS works well. If it needs to be MS query, I would still recommend developing the query in another client then copying the SQL statement into MS query instead of trying to build the query there.

Assuming your SQL table has 2 columns, timestamp and data, a query like this might work:

SELECT
    DATEADD(hour, DATEDIFF(hour, 0, [TimeStamp]), 0) as [Hour],
    AVG([TrendValue]) AS [Avg(Data)]
FROM
    [ProcessData]
WHERE
    [TimeStamp] BETWEEN '2018-01-01' AND '2018-01-02'
GROUP BY
    DATEADD(hour, DATEDIFF(hour, 0, [TimeStamp]), 0)

Edit:

Even if your IT department is fairly restrictive, I'd ask about using SQL Server Management Studio. It is a 100% free utility provided directly by Microsoft. I worked for a company with restrictive IT where it took months to get software approved, if they'd approve it at all. SSMS was pre-approved and they installed it for me within hours of me asking for it.

Keeping within MS Query though, you can click the SQL button at the top to edit the raw SQL query. I threw some random data with values every 5 minutes into a database for testing. Through MS Query I was able to use the SQL statement above to get data with hourly averages, as shown in the screenshot below.

Extract hourly data from process data base with excel and ms query

1 Answers1