Grabbing 12 months of data for a summary report

[salisbut]

I am currently working with a database that keep track of meters and the electrical loads that they have for each month. I want to do an end of the year summary which grabs each months data for the year. It is easy for me to grab one month but how can I grab two months data at the same time? Do I have to create a set of 12 queries for each month that takes advantage of date add function and then join them into one query that I would like to be the control source for the report? Or is there some way that I can query 12 months of data by using the existing queries I have and bring them into report? All my queries are run by a frmSelection that had a period start date and period end date that I query off of.

[jzwp11]

You would not need separate queries for each month. You can probably do this with 1 maybe two queries. Can you provide the structure (field names) of the table in question and what type of data is stored in the field? You need only include those that are pertinent to your question.

[salisbut]

The table is tblDay and the fields are DayID, MeterPointID, Day, HE1, HE2, HE3, HE4, HE5, HE6, HE7, HE8........., HE25, Max, Total. Summing the total column results in rounding errors so what I do is add horizontally to get totals for each day. What I need is the totals for the month for certain MeterPointID's and in some cases the Light load hour totals (6AM to 10PM and all of Sundays) and High load hour totals(everything else). These queries are all set up it's just that I'm not sure how to get 12 months at once. The types are as follows: DayID=Autonumber, MeterPointID=Number, Day=Date/Time, HE1,HE.....=Number (long integer), and Max and Total and numbers. Max and Total are not pertinent.

[jzwp11]

I see you haven't normalized your database as I suggested in this post. Things would be much easier if you normalized.

[salisbut]

Shoot I guess it is coming back to bite me. At the time it seemed that the database would never get complex enough where it would be a problem. I guess I was just turned off by the 27 append queries. You think this is necessary to complete what I am asking for? I just want to make sure it is completely necessary to do before I go to the trouble of changing tables and query relations.

[jzwp11]

Anytime you want to do something different with the database, it will come back to haunt you! You will be better off in the long run and save yourself a bunch of headaches to normalize the database.

[salisbut]

Ok the tables have been rearranged to how you suggested. How should I proceed for the report?

[jzwp11]

You will need to create a query that brings all of the details together. You can have criteria in the query that filters the records to include only those for the period you want (you said you wanted a 1 year summary, so I assume 01/01/2010 to 12/31/2010).

Then base the report on the query. You will need to set up group levels for meterpoint (I assume) and month (you can use the month() function to extract the month from your date field). If you want to do any sums, you would do this in the group footer.

[salisbut]

Ok I am familiar with how to do the first paragraph you wrote. I will set the report to be controlled by the year query. Then I must understand what you mean by group levels. Would I have the text box control what data is collected? For example a text box could say, "Sum(Meterreading where MeterPointID=? AND Month(Yourdatefields) = ?)". Is this on the right track?

[jzwp11]

Then I must understand what you mean by group levels. Would I have the text box control what data is collected? For example a text box could say, "Sum(Meterreading where MeterPointID=? AND Month(Yourdatefields) = ?)". Is this on the right track?

I would use the report wizard, it will prompt you for how you want to group the data.

You may want to check out Crystal's site and take a look at the section on form and report design.