Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Months and Years as Headers in a Crosstab Query

    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:

    <pre>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");
    </pre>


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

    <pre>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");
    </pre>


    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!)

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Months and Years as Headers in a Crosstab Query

    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", ...)
    Regards,
    Rudi

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Months and Years as Headers in a Crosstab Query

    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

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Months and Years as Headers in a Crosstab Query

    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.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Months and Years as Headers in a Crosstab Query

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

    <img src=/w3timages/blueline.gif width=33% height=2>
    <code>
    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 & ")"
    </code>
    <img src=/w3timages/blueline.gif width=33% height=2>

  6. #6
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Months and Years as Headers in a Crosstab Query

    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!

  7. #7
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Months and Years as Headers in a Crosstab Query

    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!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Months and Years as Headers in a Crosstab Query

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •