Need to show items checked by month

[jon98548]

Hi, all. I really don't know where to start with this. I have a database I use to check compliance with certain items. An auditor goes to a division and asks some questions, taken from the database, and enters a compliance level for that question. When the auditor enters a response to a question a date is automatically entered for that question. Each division has a specific number of questions which are spread over three years. I need to look at responses by year, which is done easy enough with a parameter query in a report. In this report, I look at total questions asked in that year, compliance level and a few other stats.

I am having trouble with one feature I need to see. Each division is assigned a number based on it's size. So, there are N widgets for every division that I can translate into a workload for the year. With me? I can do a calculation based on [(# questions asked in a month/total questions for that year)*N widgets]. Still with me? You can see what I'm getting is simply a way to measure progress. I need to show the number of widgets I've done to date toward the total by month. In a query I reformat the date to the 3 letter month, group them by year and month, and then performs the above calculation. If I asked 25 of 100 questions in August of 2004, my measure for August would be 1.2. Life is great up to this point.

Here is my problem. I can make a report based on this information. I show my goal as 5 and I got 1.2 in August. That is all I show though is August. I've been asked to show in the report every calendar month on the reportwith or without data. I thought it would be relatively simple to put 12 unbound text boxes on the report and go through a SELECT CASE routine to put data in the correct slots. It isn't going that simple. Can you guys suggest a way to show this? It doesn't seem useful to me, but it has been requested, so ... What I will end up with is a report that lists each month and only 1 or 2 months having data. Another reason I used twelve text boxes was to be able to split the list into 2 columns. Well, I hope this is a tiny bit clearer than a mud hole. Thanks for any advice.

Jon

 

[Jerry Stoner]

Use a crosstab query to get monthly breakdown. Prettty sure the wizard will guide you there.Then use a subreport based on the crosstab

 

[jon98548]

Ok, I've given this a try, but I end up in the same dilemma. Months where nothing was done, don't come out in the query.

 

[Jerry Stoner]

It should work. Just did a quick test with a table with 3 field one of which was a date. Here is the SQL:

TRANSFORM Avg(Table1.NUNM1) AS AvgOfNUNM1
SELECT Table1.num, Avg(Table1.NUNM1) AS [Total Of NUNM1]
FROM Table1
GROUP BY Table1.num
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Shows all 12 months and only 2 of them have data in them.
Jerry

 

[Rich]

You could put some dummy dates in another table and then use a Union query

 

[jon98548]

Thanks, Jerry and Rich. Here is what I ended up with. Jerry, I was missing that bit of code in the last line to force in the "empty" months.

TRANSFORM First(qryMonthCount.Units) AS FirstOfUnits
SELECT qryMonthCount.AssessmentYear, qryMonthCount.AssessYear, qryMonthCount.WorkUnits, Sum(qryMonthCount.Units) AS [Total Of Units]
FROM qryMonthCount
GROUP BY qryMonthCount.AssessmentYear, qryMonthCount.AssessYear, qryMonthCount.WorkUnits
PIVOT qryMonthCount.AssessMonth In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I had one other wrench to throw in the works too. I had to reformat the date again to show only the year. You can see why, I guess. If I did something in Nov of last year and finished in Feb of this year, the Nov number shows in the "future". Anyway, I threw in the year formating and it works fine. Splitting the year like that makes another list of months, but it does exactly what I need. Thanks again guys.

Jon