Annual Report based by month

[GrayLowe]

Hi All, I have a database that collects demographic numerics on multiple fields on a daily basis. I am trying to create a report that will show totals only for each field based on each month of the year. I have set up a report with the months of the year across the top and the fields running down the report. I have created a field under each column for each of the fields of the report. Here is where I am struggling because I do not know how to populate the control source for each of these fields. I can do it through a query which has the months down the side and the multiple fields summed at the top, but this would be to big for a report. Please help. Thank you.

 

[theDBguy]

Hi. Have you tried using a crosstab query for your report?

 

[plog]

Without specifics of your table(s)/field(s) we can only give generic advice like use a crosstab. If you could provide some sample data we can be more specific in our help. So to do that please provide 2 sets of data:

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

B. Expected results of A. Show what you hope to end up with when you feed in the data from A.

Again, 2 sets of data, no more explanations, just demonstrate it with data.

 

[GrayLowe]

Thanks for the quick answers, I have uploaded the Demographics table in xlsx format. I hope to get totals for each month over a year period.
Thanks for the help, much appreciated.

 

[June7]

That data structure is not normalized. Will need to use a UNION query to arrange in normalized structure then use that query in CROSSTAB.

 

[GrayLowe]

Thanks June 7. Will look up how to normalize and then learn about union queries.

 

[June7]

Here is a start. There is a limit of 50 SELECT lines in UNION. There is no wizard or designer, must type or copy/paste in SQLView of query builder. First SELECT line defines field names and types.

Query1: DemoUNION

SELECT ID, demDate, Year(demDate) AS Yr, Format(demDate, "mmm") AS Mo, dem19 AS Data, "19" AS Cat FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), dem2040, "2040" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), dem4160, "4160" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), dem61plus, "61plus" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), demFemale, "F" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), demOther, "O" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), demMale, "M" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), demAboriginal, "A" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), demEthnic, "E" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), demNonAboriginal, "NonA" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssNonLegalAdv, "NonL" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssBirthControl, "BC" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssBusTickets, "BT" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssNonSTVCounselling, "STV" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssCrisis,"C" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssHealthInfoReferral, "H" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssIncomeTax, "I" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssMiscInfoRefer, "M" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssLGBTQ, "LGBTQ" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssDonationsClosetNumberBags, "D" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssProBono, "P" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssSupport, "S" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssNumPeopleUsingCloset, "N" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), wssClosetVolunteerHrs, "CVH" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), povFoodShelf, "FS" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), povFoodHampersDist, "FH" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), gfbGoodFoodBoxesSld, "G" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), gfbAmntFoodPurchased, "AFP" FROM Demographics
UNION SELECT ID, demDate, Year(demDate), Format(demDate, "mmm"), safApplicantsWWHA, "AW" FROM Demographics;

I was lazy with the Cat values, use whatever you want. If you don't have an ID field then remove that.

Query2

TRANSFORM Sum(DemoUNION.Data) AS SumOfData
SELECT DemoUNION.Yr, DemoUNION.Cat
FROM DemoUNION
GROUP BY DemoUNION.Yr, DemoUNION.Cat
PIVOT DemoUNION.Mo In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[GrayLowe]

You are amazing June7, Thank so much. I just cut and pasted your code into SQL view of a new query and it recorded an error which said Characters found after end of SQL statement.

 

[GrayLowe]

June7, worked like a breeze when this dumbo worked out I needed two queries. Thank you so much, I feel my headache slowly disappearing.

 

[theDBguy]

June7, worked like a breeze when this dumbo worked out I needed two queries. Thank you so much, I feel my headache slowly disappearing.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.