Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstabs as recordsoucre for Reports (Access 2k)

    Looking for the best approach to creating a report who's recordsource is a crosstab query - where the column headings and number of objects on the report will change each month.

    Looking for either VBA or other solutions. My report needs to grow each month to display the fiscal months and year as the column headings, also using other fields for the calculation of YTD totals and totals at the bottom of the report.

    My row headings will consists of the clinic name and the various insurance types (total of 6 rows of data per each clinic)

    Any suggestions are appreciated.

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

    Re: Crosstabs as recordsoucre for Reports (Access 2k)

    You probably need VBA to do this. If you search for dynamic crosstab report in this forum, you will find several threads dealing with this subject. For example, you might have a look at the databases attached to <post#=35485>post 35485</post#> and <post#=134439>post 134439</post#>. They demonstrate two different approaches to creating reports based on crosstab queries with dynamic column headings. Another example can be found in <post#=248210>post 248210</post#>.

  3. #3
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstabs as recordsoucre for Reports (Access 2k)

    Thank you for the great suggestions, however, I need more columns (1 for each month of the year,
    Prior YTD Totals, and a calculated field for Variance between YTD and Prior YTD.

    Also I need the months to be displayed in a certain order for fiscal year ( Jul thru Jun)

    How do I need to modify the code to accommendate these changes.

    Thanks again.

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

    Re: Crosstabs as recordsoucre for Reports (Access 2k)

    If you can create a query that returns the data you need, it may be possible to use one of the existing examples with a modest amount of tweaking.

    If you would like more assistance, it would be helpful if you could post a stripped down version of your database:
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
    <LI>Attach the zip file to a reply.[/list]Otherwise, the problem remains too abstract.

  5. #5
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstabs as recordsoucre for Reports (Access 2k)

    Attached is a stripped down version of mdb - Please help with repairing some of the code.

    First problem is that in the header "txtheader1" should equal "Ins Carrier" - right now it is blank. - Please run the report first to see what I mean.

    2nd - in the 1st column it displays 1 - 6 - I wrote a function to correct the data - which should display the type of insurance, unfortunately I can't figure out where in the code I should call the new function ( Public Function InsIDConv()) -

    3rd - I need to have the last displayed column calculate the variance between the PrYTD and the YTD totals ( equation should be similar to the Excel counterpart of (=ROUND((YTD -PrYTD )/PrYTD ,6)) - How would I include this in the code so that it will always be in the last column.

    Also, is there a way to change the "Const conNumColumns = 16" to use a variable based on the recordcount of the base table - this way only columns with data in them will be visible.


    Thanks again the crosstab system you created is great.
    Attached Files Attached Files

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

    Re: Crosstabs as recordsoucre for Reports (Access 2k)

    I have tried to do what you want, but I am not sure about the calculation of the YTD sum. As it stands, it includes the PrYTD amount. Is that what you intend? Please check carefully.

    Some notes:
    1st - txtHeader1 has to be treated separately in the code.
    2nd - the InsIDConv function must be included in the control source for the text boxes.
    3rd - the calculation is done as you describe here, but see above.
    4th - I re-added the code to make the unused columns invisible.

    Modified database in Access 97 format is attached.
    Attached Files Attached Files

  7. #7
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstabs as recordsoucre for Reports (Access 2k)

    Hans,

    Thanks for all your help - your sugesstions work great, however, I am still having diffculty with PriorYTD - I would like it to be displayed in the second to last column of the report. Right now it displays in the first calculated field on the report - Note this value is calculated in the underlying query. I changed the YTD to be calculated per your suggestions. Also I am not sure were in the code I can put the Variance calculation. The Variance calculation needs to follow the PriorYTD value. To refresh your memory the variance = Sum(YTD-PriorYTD)/PriorYTD this should be a percentage.

    Any suggestions is greatly appreciated.

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

    Re: Crosstabs as recordsoucre for Reports (Access 2k)

    I don't understand. In the database I attached to my previous reply, PriorYTS *is* displayed in the second to last column, and variance is already calculated, albeit not displayed as a percentage; to obtain that, change the code as follows (changes in bold):

    ' Set caption of next available label in page header to "Variance".
    Me("txtHeading" & (intColumnCount + 2)).Caption = "Variance"

    ' Set control source of next available text box in detail section to row variance.
    Me("txtColumn" & (intColumnCount + 2)).ControlSource = "=([txtColumn" & _
    (intColumnCount + 1) & "]-[txtColumn" & intColumnCount & "])/[txtColumn" & intColumnCount & "]"
    Me("txtColumn" & (intColumnCount + 2)).Format = "Percent"
    Me("txtColumn" & (intColumnCount + 2)).DecimalPlaces = 2

    ' Set control source of next available text box in group footer to row variance for group.
    Me("txtSubtotal" & (intColumnCount + 2)).ControlSource = "=([txtSubtotal" & _
    (intColumnCount + 1) & "]-[txtSubtotal" & intColumnCount & "])/[txtSubtotal" & intColumnCount & "]"
    Me("txtSubtotal" & (intColumnCount + 2)).Format = "Percent"
    Me("txtSubtotal" & (intColumnCount + 2)).DecimalPlaces = 2

    ' Set control source of next available text box in report footer to total variance.
    Me("txtTotal" & (intColumnCount + 2)).ControlSource = "=([txtTotal" & _
    (intColumnCount + 1) & "]-[txtTotal" & intColumnCount & "])/[txtTotal" & intColumnCount & "]"
    Me("txtTotal" & (intColumnCount + 2)).Format = "Percent"
    Me("txtTotal" & (intColumnCount + 2)).DecimalPlaces = 2

  9. #9
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstabs as recordsoucre for Reports (Access 2k)

    Sorry, I got my wires crossed - I used your latest version and everything looks great except for the Prior YTD - You were right I had to rethink how to display the Prior YTD so that it is not included in the YTD totals.

    I created another query to display the Prior YTD and linked the two queries together - however, Prior YTD wants to display in the first column of values and I needed it to follow the YTD and not be included in the totals - Got any ideas. I attached the latest version which includes your changes and my changes to the query. I also tried to use a recordset to pass the value for the Prior YTD column without any luck.

    Here is my revised code attempt - Please let me know if I am on the right track.

    'Prior YTD Values & Totals"
    Set dbs = CurrentDb()

    strsql = ("TRANSFORM Sum(Users) AS SumOfUsers" & _
    " SELECT ClinicID, InsCar, Sum(Users) AS [Total Of Users]" & _
    " FROM" & _
    " (SELECT ClinicID, InsCar, CountOfDOV AS Users, FiscalMonthID, Year" & _
    " FROM tblFQHC_Clinics_FYTD_Data" & _
    " GROUP BY ClinicID, InsCar, CountOfDOV, FiscalMonthID, Year" & _
    " HAVING (((FiscalMonthID)<=9) AND ((Year)=2003)))" & _
    " GROUP BY ClinicID, InsCar" & _
    " PIVOT FiscalMonthID")

    Set rst = dbs.OpenRecordset(strsql)

    Me.txtHeading15.Caption = "PrYTD"
    Me.txtSubtotal15.ControlSource = "[rst!SumofUsers]"
    ' Calculate row total for group.
    strGroupTotal = strGroupTotal & " + [txtsubtotal15] "
    ' Set control source of text box in report footer to sum of corresponding field.
    Me.txtTotal15.ControlSource = "=Sum([rst!SumofUsers])"
    ' Calculate grand total.
    strGrandTotal = strGrandTotal & " + [txtTotal15]"
    Me.txtColumn15.ControlSource = "=" & Mid(strRowTotal, 4)
    ' Set control source of next available text box in group footer to row total for group.
    Me.txtSubtotal15.ControlSource = "=" & Mid(strGroupTotal, 4)
    ' Set control source of next available text box in report footer to grand total.
    Me.txtTotal15.ControlSource = "=" & Mid(strGrandTotal, 4)


    Thanks so much for all your help. You are a lifesafer.

    Karen
    Attached Files Attached Files

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

    Re: Crosstabs as recordsoucre for Reports (Access 2k)

    You dragged the PrYTD field to the right in the query, but that is cosmetic only. It is still the 3rd field in the recordset. So you have to handle this field apart from the rest. There was also a problem with the calculation of the variance if the PrYTD was 0, I have made it blank in that case. See new version.
    Attached Files Attached Files

  11. #11
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstabs as recordsoucre for Reports (Access 2k)

    THANKS, THANKS, THANKS FOR ALL YOUR HELP.

    Have a great weekend.

    Karen <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

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