Daily, hourly and monthly average. (1 Viewer)

[Rai Tahir]

Hi Everyone,

I am looking for a query to generate the daily, hourly and monthly average. I have a table with columns Date, Time (hh:mm:ss), Pressure and temp. in the Time column I have data for every seconds for some days and every 30 second data for some days. I want to generate a query which can give me the records for hourly average, daily average and monthly average.

I would be very thankful if someone could help me to generate this query in Ms Access.

Thanks in advance for your help.

Best Regards
Tahir

 

[plog]

Can you provide sample data? Two sets of data please:

A. Starting date from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect your query to generate when you feed it the data from A.

Again, 2 sets of data--starting and expected results from the starting data.

 

[Rai Tahir]

Hi,
Thank you so much for your prompt reply. Please find attached the sample original file (as this file contains the date for each second so the file size is to big) I have just copied some of the records. Two other files are the expected results.
Please let me know if you need another information.

 

[CJ_London]

This gives your hours

SELECT txt.Date, Format([Time],"hh") & ":00:00" AS [hour], Avg(txt.[Pressure bar]) AS [AvgOfPressure bar], Avg(txt.[Temp C]) AS [AvgOfTemp C]
FROM (SELECT * FROM [TEXT;DATABASE=C:\PathToTextFile;HDR=Yes].Original_file.txt)  AS txt
GROUP BY txt.Date, Format([Time],"hh") & ":00:00";

this gives your days

SELECT txt.Date, Avg(txt.[Pressure bar]) AS [AvgOfPressure bar], Avg(txt.[Temp C]) AS [AvgOfTemp C]
FROM (SELECT * FROM [TEXT;DATABASE=C:\PathToTextFile;HDR=Yes].Original_file.txt)  AS txt
GROUP BY txt.Date;

and this your months

SELECT Format([Date],"yyyy-mm") AS Months, Avg(txt.[Pressure bar]) AS [AvgOfPressure bar], Avg(txt.[Temp C]) AS [AvgOfTemp C]
FROM (SELECT * FROM [TEXT;DATABASE=C:\PathToTextFile;HDR=Yes].Original_file.txt)  AS txt
GROUP BY Format([Date],"yyyy-mm");

I did this by linking directly to the file - just change the path and filename as required. It is the equivalent of creating a linked table to the file.

However your file will be significantly larger, so if performance is an issue you will need to import the data to a table and ensure the date field is indexed - or better create additional columns to store the hour day and month values and index those as well

Note Date and Time are reserved words so should not be used for field names. Can't do much about it in the above code, but if you need to import the data to a table, recommend renaming them - perhaps rDate and rTime

 

[XPS35]

In addition to the correct solution of CJ_London: if you want to round the results to 4 decimal places, you can use the following functions.

Format(Avg(Pressure),".0000") AS AvgPressure, Format(Avg(Temp),".0000") AS AvgTemp

 

[arnelgp]

you may also try this.
when you open the db, enable the macro and it will ask where
your textfile is located. browse to your textfile and it will create a Linked table (data).

your required queries are numbered 1 to 3.

 

[Rai Tahir]

This gives your hours

SELECT txt.Date, Format([Time],"hh") & ":00:00" AS [hour], Avg(txt.[Pressure bar]) AS [AvgOfPressure bar], Avg(txt.[Temp C]) AS [AvgOfTemp C]
FROM (SELECT * FROM [TEXT;DATABASE=C:\PathToTextFile;HDR=Yes].Original_file.txt)  AS txt
GROUP BY txt.Date, Format([Time],"hh") & ":00:00";

this gives your days

SELECT txt.Date, Avg(txt.[Pressure bar]) AS [AvgOfPressure bar], Avg(txt.[Temp C]) AS [AvgOfTemp C]
FROM (SELECT * FROM [TEXT;DATABASE=C:\PathToTextFile;HDR=Yes].Original_file.txt)  AS txt
GROUP BY txt.Date;

and this your months

SELECT Format([Date],"yyyy-mm") AS Months, Avg(txt.[Pressure bar]) AS [AvgOfPressure bar], Avg(txt.[Temp C]) AS [AvgOfTemp C]
FROM (SELECT * FROM [TEXT;DATABASE=C:\PathToTextFile;HDR=Yes].Original_file.txt)  AS txt
GROUP BY Format([Date],"yyyy-mm");

I did this by linking directly to the file - just change the path and filename as required. It is the equivalent of creating a linked table to the file.

However your file will be significantly larger, so if performance is an issue you will need to import the data to a table and ensure the date field is indexed - or better create additional columns to store the hour day and month values and index those as well

Note Date and Time are reserved words so should not be used for field names. Can't do much about it in the above code, but if you need to import the data to a table, recommend renaming them - perhaps rDate and rTime

Good Morning CJ_London.
Thank you so much for your answer. But unfortunately I am not getting my results, I am not very expert user of access so may be I am doing something wrong. So the situation is that I have already created a table in Ms Access and imported all the data into that table which contains Date, Time, Press and Temp Columns. My requirement is to get a reduced files with hourly average, daily average data.
I created a Query and used your provided query, so instead of filepath I choose to select my table but then it ask me for date, pressure and temp and give me a result of that date pressure and temp only.
Secondly I tried to use your query and changed the path and file name but I get error message Syntax error in FROM clause.
I am sorry if I am not doing as you suggested. Would be very thankful if you could let me know what am I doing wrong.

 

[CJ_London]

Post the query you are actually using, it will be a typo. also a bit of background on the process - is this a file you process in its entirety, or one you add to a table and then reproduce the reduced files? Or something else?

 

[Rai Tahir]

Post the query you are actually using, it will be a typo. also a bit of background on the process - is this a file you process in its entirety, or one you add to a table and then reproduce the reduced files? Or something else?

This is the query;
SELECT txt.Date, Format([Time],"hh") & ":00:00" AS [hour], Avg(txt.[Pressure bar]) AS [AvgOfPressure bar], Avg(txt.[Temp C]) AS [AvgOfTemp C]
FROM (SELECT * FROM [TEXT;DATABASE=C:\Users\abdurrt01\Desktop; HDR=Yes].TG-324z_RAW_All_20220101_20220131.txt) AS txt
GROUP BY txt.Date, Format([Time],"hh") & ":00:00";

I have a datafiles with millions of records, containing every seconds data. I created a table in Access and imported the seconds data into that table, Now I want to export a files with every hourly average data of pressure and temp and a file with every day data of average of Pressure and temp.

 

[CJ_London]

Syntax looks OK, the issue seems to be the hyphen after TG. Remove or replace it with an underscore (both code and file name) and it works. Otherwise you will need to create a linked table - take a look at arnel's solution

With regards the description, not very clear. I think what you are saying is

You have one datafile per month (based on your file name)
from that file you want to create two files, one summarising by hour and the other by day for each month

So after 12 months you will have generated 24 files

Not sure where the monthly query comes in? but it would just be a one liner

with regards volume, you will have 86400 records per day or around 2.6m per month

 

[Rai Tahir]

Syntax looks OK, the issue seems to be the hyphen after TG. Remove or replace it with an underscore (both code and file name) and it works. Otherwise you will need to create a linked table - take a look at arnel's solution

With regards the description, not very clear. I think what you are saying is

You have one datafile per month (based on your file name)
from that file you want to create two files, one summarising by hour and the other by day for each month

So after 12 months you will have generated 24 files

Not sure where the monthly query comes in? but it would just be a one liner

with regards volume, you will have 86400 records per day or around 2.6m per month

Thank yo so much for your help, it's working now.