Need some assistance to extract data for a non 24 day

[henniec]

Good day,

I need a little help with two queries. It is related to the same problem.
I import data from a different sever via Excel and try to do most of the manipulations in Excel. Most of the work is straight forward but the one problem I experience is capturing the data. The data for a day starts at 08:00 am on day one and end at 7:59 am on day two.
How could I calculate the daily average? One way I use is to change the date for the period 00:00 to 07:00. Is there a different way of doing it?
My other problem with the one sample point is, there is not a flow for 24 hours a day. I need to find the first instance when flow started and record the volume and time. Once again the first instance is from 08:00 in the morning. I do believe some VBA code would do the trick. I tried running a query using the first function but it comes up way short. From the actual data it gives me only 13 records but a visual check show about 4 times as much.

The SQL for that query is (approximate no of records 2000)
SELECT qryMEWS0300StatsInput.Sdate, First(qryMEWS0300StatsInput.Flow) AS FirstOfFlow
FROM qryMEWS0300StatsInput
GROUP BY qryMEWS0300StatsInput.Sdate
HAVING (((First(qryMEWS0300StatsInput.Flow))>0));

Thanks. Hennie

 

[huggyboy]

You dont give any details about the database you are capturing the data from, how the data is being pulled into excel, how far you've got in the vba and what kind of field sdate is.

I'm going to give some suggestions about the date/time problem assuming pulling data from Access, sql query using 'get external data' in excel and sdate is a datetime field (no vba for the moment)

I made a table in access with comment in field f1 (unique to the day) and the date time in field f2 - running the query gets the first data after 8 am for each day

SELECT f1table.f1, First(DateDiff('s',Format([f2],"dd/mmm/yyyy") & ' 08:00:00',[f2])) AS Expr1
FROM f1table
GROUP BY f1table.f1;

This works by calculating the difference in time in seconds between 8 am and the current time for each day and selecting the first (i think you would probably use min in transact sql - sql is not my strongest subject)
if you want to group by day you would have to group by the date part of sdate try format again - i cant see grouping by the entire sdate working.

i hope this gives you a start

H

 

[PHV]

The data for a day starts at 08:00 am on day one and end at 7:59 am on day two
So, aggregate the data by (sdate-(8/24))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[henniec]

Thank you both for responding and the information . I looked at both and decided to use the one from PHV. I am now able to extract the correct number of days on the first instance and work backwards to the result I finally need. It needs about three additional queries and I can live with it.

Huggyboy. Only to clarify your curiosity. The data I work with are hourly and I extract the data into Excel from a program called Processnet. Do the necessary manupliations in Excel before I import the data into Access where I manipulate all the data before it is exported into an XML format.

Once again thank you.

 

[huggyboy]

No problem - PHV gave the better solution - i still had my vb head on - nice to have a couple of ideas sometimes to get the best fit to ones problem

H

 

[dhookom]

In addition, you might want to store the StartHour value in a table some place and reference it in an expression like PH has created.

I have contracted to a company that changed the start of their production day from 7:00 AM to 11:00 PM. It was a real mess trying to find everywhere the previous developer had hard-code 7:00 AM into expressions. I created a small table with a single record in a single field that contained the amount of time to add/subtract from the actual.

Duane
Hook'D on Access
MS Access MVP

 

[henniec]

Thank you Duane. I will keep it in mind although I do not think this one will change whatsoever.

I agree with have more than one soultion.

Hennie