Peak Sum of values based on timestamp

[Gicu]

Did you look at my sample? "Aligning the meters" is the result of your Excel-like thinking. In Access you need to simply group by the account and timestamp intervals and sum the values in the Reading field (based on your table structure shown in post #6). So you do not need to do all that extra work in Excel....



Cheers,

[NoellaG]

When you have a SQL server, I don't see why you would use access to query the database. T-SQL is far more powerfull than access queries and you can publish the results using the SQL reporting services. Only thing to do then is open the query report in your browser and, if needed, download the result in Excel. Much faster than usinbg access.
What you need can be done in 1 T-SQL query using windowing functions.

[kfbeal]

Cheers,

Sorry, my browser or the forum was acting funny and not displaying all posts or allowing me to reply at times and your post wasn't shown. Not exaclty, we need to values of each meter at 10:00 and then totalized. We then need the values of each meter at 10:15 and then totalized. Each 15 minutes has a totalized value of all of the meters. If we have 4 meters, we would have 4 interval values & a total value (SUM of those 4 intervals).

[kfbeal]

When you have a SQL server, I don't see why you would use access to query the database. T-SQL is far more powerfull than access queries and you can publish the results using the SQL reporting services. Only thing to do then is open the query report in your browser and, if needed, download the result in Excel. Much faster than usinbg access.
What you need can be done in 1 T-SQL query using windowing functions.

I started with Access years ago as a front end and just stuck with it. Being able to visually link the tables, create the queries, etc. via a "GUI" without having to know SQL was an added benefit. So you are saying I can get the kw value for each 15 minutes interval of 4 meters and have it sum those values up with one query?

[NoellaG]

using T-SQL queries, not access queries. grouping and using windowing functions (over partition syntax) should do the trick. You will need to take some time to learn T-SQL, but it surely will pay off, given the possibilities. You would also have the benefit of cutting out the Access middle software, which would save license costs and execution time. You can publish the results using SQL reporting. The only thing you won't have is forms to update the data.

What you are doing now to build queries is like having a real good spade to dig a hole and then say: le'ts use a spoon to dig the hole because I'm more comfortable using the spoon.

[Gicu]

It is very easy to get the meters to show as columns by using a crosstab query (in the sample there is only one account, it should be done one account at the time to avoid running out of columns as Access queries can only have 255). But the calculations do not need it, you can compare the values in the original query (qryTotalReadingsByDeviceName) and the TotalofReading field in the new crosstab query.

Cheers,

[kfbeal]

Coming from someone who knows what the heck they're doing, that's easy for you to say. I'm challenged & limited, mostly on time. I'm just short on time and needing to get something at least in place temporarily for this billing period the last day of the month.

[NoellaG]

I'll try to mock up a T-SQL example this or next week (a bit stressed for time now like most of us )

[kfbeal]

It is very easy to get the meters to show as columns by using a crosstab query (in the sample there is only one account, it should be done one account at the time to avoid running out of columns as Access queries can only have 255). But the calculations do not need it, you can compare the values in the original query (qryTotalReadingsByDeviceName) and the TotalofReading field in the new crosstab query.

Cheers,

I think that's exactly what will work & sadly recognizable due to my Excel preference. I just need to figure out how you did it & apply it to my db with queries.

[Gicu]

You can use a form with a combo box to select account and start\end dates then use those in the crosstab query to limit the results to just that account. You can then use the crosstab as the record source for a report. The other query in there returns the Max of all the intervals and that would be your peak value.

Cheers,

[CarlettoFed]

In the attached example you can see a way for how to manage data.
PointHistory.zip

[kfbeal]

In the attached example you can see a way for how to manage data.
PointHistory.zip

Thanks for that, that does help clear some things up. I apologize for not getting back sooner, it's been busy.

Vlad has been more than generous in helping with the data. He's gone beyond what I expected for help and much appreciative of his efforts. He's gotten me on the right track. I appreciate everyone's responses! I'm close to being complete, but still have some changes that need to be made.

I've now foudn out that some accounts need to have the hour intervals summed up. I need to add up 00:00, 00:15, 00:30 & 00:45 for each meter, then totalize the meters of a specific group. Is there an easy method to do that in Access?

[Gicu]

Hi Keith,

Grouping on Format([TIMESTAMP],"mm/dd/yyyy hh AM\PM") instead if just TIMESTAMP would give you hourly sums.

Cheers,
Vlad