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.
Annual Report based by month (1 Viewer)
- Thread starter GrayLowe
- Start date
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.
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.
June7
AWF VIP
- Local time
- Yesterday, 17:33
- Joined
- Mar 9, 2014
- Messages
- 5,466
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
I was lazy with the Cat values, use whatever you want. If you don't have an ID field then remove that.
Query2
Query1: DemoUNION
Code:
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;Query2
Code:
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");
Last edited:
- Local time
- Yesterday, 18:33
- Joined
- Oct 29, 2018
- Messages
- 21,454
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.June7, worked like a breeze when this dumbo worked out I needed two queries. Thank you so much, I feel my headache slowly disappearing.
