Months and Years as Headers in a Crosstab Query

Topic

New Reply

As a background, I’m creating a database of audits. These are supposed to be carried out by various departments every month, though the exact date within the month doesn’t matter.

One report I need to generate is a grid showing whether or not an audit has been carried out, for every department and month (over a variable date range). I have tried to create a crosstab query to do this, but with limited success. The SQL for my first attempt was as follows:

TRANSFORM Count(qryReportAudits.AuditID) AS [The Value]
SELECT qryReportAudits.Name, qryReportAudits.Description
FROM qryReportAudits
GROUP BY qryReportAudits.Name, qryReportAudits.Description
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May",
"Jun","Jul","Aug","Sep","Oct","Nov","Dec");

This lumps all audits into each month, regardless of what year they belong to. The SQL for my second attempt was as follows:

TRANSFORM Count(qryReportAudits.AuditID) AS [The Value]
SELECT qryReportAudits.Name, qryReportAudits.Description
FROM qryReportAudits
GROUP BY qryReportAudits.Name, qryReportAudits.Description
PIVOT Format([Date],"yyyy mm");

This correctly separates audits by both year and month, but misses out months in which no audit took place.

Can anyone think of a way I can show all years and months for the required range, without missing out any? (Hope all that makes sense!)

Reply | Quote

Replies

I’m just throwing in a guess here, but have you ried using an “IN” statement as you have in the 1st SQL statement. You must insert it as a string like (“2005 01”, “2005 02”, …)

Reply | Quote

Mark

I suggest you might need a new table containing one column which has the month and year stored as a date. You would then outer join your existing query to this table (assuming there is an audit date in there) and use Nz(AuditDate,””) to show blanks.

Hope I’ve understood your requirement correctly.

Nick

Reply | Quote

Wow, what a response! In the time I wrote my thoughts on the first two replies, two more came in!

I think the VBA route is probably the one to go for in this case, because it doesn’t require months/years to be pre-populated in a query or table.

Thanks for your help, everyone!

Reply | Quote

You can set the properties of a crosstab query so the header includes all the months of the year, but you will need to separate the the year and the Month out so you can do the Year as the Row source, and the Month as the Column source. Using the query designer will make this easier to see.

Reply | Quote

You can create the correct SQL including the column headings in code, and use the generated SQL string as record source for a report.


Dim strSQL As String
dim strIn As String
Dim datFirst As Date
Dim datLast As Date
Dim i As Integer

strSQL = "TRANSFORM Count(qryReportAudits.AuditID) AS [The Value] " & _
"SELECT qryReportAudits.Name, qryReportAudits.Description " & _
"FROM qryReportAudits " & _
"GROUP BY qryReportAudits.Name, qryReportAudits.Description " & _
"PIVOT Format([Date],'yyyy mm') In ("

datFirst = DMin("[Date]", "qryReportAudits")
datLast = DMax("[Date]", "qryReportAudits")

datFirst = DateSerial(Year(datFirst), Month(datFirst), 1)
For i = 0 To DateDiff("m", datFirst, datLast)
strIn = strIn & "," & Chr(34) & Format(DateAdd("m", i, datFirst), "yyyy mm") & Chr(34)
Next i

strIn = Mid(strIn, 2)

strSQL = strSQL & strIn & ")"


Reply | Quote

Thank you, Hans, for the code. It works perfectly.

I was planning that the user could simply view the crosstab query directly, but I’m intrigued by your suggestion that I use it as a source for a report. Is it possible to create a report from a source that doesn’t have fixed field names?

By the way, although I’m using Access 97, I probably will have Access 2003 within the next year, and I think that will give me many more options.

Thanks once more for your help!

Reply | Quote

It is possible to create dynamic crosstab reports, but you need VBA code. See for example the thread starting at post 365327. My first reply in that thread contains some links, and there is a demo attached to a reply further down.

Reply | Quote