Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: MS Graph Chart for Report using Query Data

    Hi Everybody,

    I need to create charts for reports in Access 2003, and I also need them to run off of data from a query/queries. The idea here is that I make a query, use it as the source data for a chart, and just print the chart whenever I need the latest data from the query.

    Currently, I'm using the crosstab below which I lifted from another developer online and modified to use my data:

    [CODE]TRANSFORM First(qryChartSrcTOTAL.OralCareTotal) AS FirstOfOralCareTotal
    SELECT qryChartSrcTOTAL.DescriptionOCT
    FROM qryChartSrcTOTAL
    GROUP BY qryChartSrcTOTAL.DescriptionOCT
    PIVOT qryChartSrcTOTAL.[Date By Month];CODE]

    And the data comes out like this:

    Click image for larger version. 

Name:	crtProb1.PNG 
Views:	85 
Size:	11.0 KB 
ID:	10043

    I then use that query as the Row Source, and I get this nice output:

    Click image for larger version. 

Name:	crtProb2.PNG 
Views:	122 
Size:	14.7 KB 
ID:	10044

    So, now I want to have multiple lines on my graph, in order to do better comparisons between different sets of data. I can't get the crosstab to add a second row so that it looks like so:

    Click image for larger version. 

Name:	crtProb3.PNG 
Views:	60 
Size:	5.1 KB 
ID:	10045

    Which is the only way I can see to get my multiple lines going in my chart. So, am I way off and trying to automate this graph the hard way, and/or is there a way to structure the query so that I get the output I'm looking for?

    Thanks for taking the time to read this, and please take pity on a total Access newbie!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    How are you populating the second line of criteria differently from the first? That is to say, assuming the SQL that you have listed generated the first line of data, what was the code that generated the second line of data?

    The advantage of a crosstab query is that, as long as you set it up right, you can have lots of rows and lots of columns, great for graphing and the like as you have found out. Check out this article for more help on Crosstabs.
    Me.Geek = True

  3. #3
    Join Date
    Sep 2009
    Posts
    3
    Thanks for the welcome nckdryr!

    That's the thing, I don't know how to generate the second line, at least so that the first column remains descriptive and the rest make up the monthly dates, with the percentages sandwiched between.

    I think you're right on the money, that I have no idea how to set up a crosstab correctly, but part of that is probably because I'm relying on this borrowed SQL rather than the wizard or design mode. When I tried to use those tools though, I ended up failing to get the output I wanted, so maybe I'll just play around with it a little more tomorrow and see if I can make any headway.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I will often use a "temp" table. This is a table where I setup "grouping" type fields to later be used in my crosstab table. Then in some event to display the data (ie. onClick of a button), I'll delete the data in the "temp" table and then run X number of queries to re-populate that temp table and then show the results in crosstab query.

    The trick is to setup your temp table correctly with the "grouping" type of fields you'll need to use in your final crosstab query. Then you can use expressions in your queries to populate the grouping fields when running the append queries to append the data to the temp table.

    For example, my temp table might have a field called YearsDataSet. When my first query runs to append to the temp table, I may have an expression in that query which populates the YearsDataSet field to "Year 1". My 2nd query might then have an expression to populate the YearsDataSet field with "Year 2". And so on.... Once my temp table is populated correctly running X queries, I then show the final crosstab query (which is based off this temp table) to display the data like I want it to. Then it's a simple matter in vba code to automate and run all these queries in the sequence they need to be run (where my first line of code in the OnClick event of a button might be docmd.openquery "DeleteAllTempData", then my queries to append data to the temp table, and the final line of vba code to open the crosstab query.)

    If you try to do too much totalling in a single crosstab query, you can often end up with inaccurate grouping/totalling. Therefor you break it down by using a temp table as described above.
    Last edited by pkstormy; 09-17-09 at 23:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    I've taken similar steps PK, but I would warn about bloating.

    But back to you benvarone, how about you explain the logic that goes into making those numbers. The WHERE criteria, the calculations, anything else.
    Me.Geek = True

  6. #6
    Join Date
    Sep 2009
    Posts
    3
    Basically, I have a query that contains monthly totals (group by date) for a variety of fields that I enter data on daily. They're basically Yes/No boxes, so I do a -sum() and divide by the count() to get the percentages, which is a seperate query. I then query that query (probably a sloppy way to do it), and then it's the transformation you see in the first post.

    The code for the "source" query looks like this:

    Code:
    SELECT DISTINCTROW DateValue(Format(tblBundleCompliance!Date,"yyyy/mm")) AS [Date By Month], FormatPercent((-Sum(tblBundleCompliance![Oral Care 0400])-Sum(tblBundleCompliance![Oral Care 0800])-Sum(tblBundleCompliance![Oral Care 1200])-Sum(tblBundleCompliance![Oral Care 1600])-Sum(tblBundleCompliance![Oral Care 2000])-Sum(tblBundleCompliance![Oral Care 2400]))/(6*Count(tblBundleCompliance!OID))) AS OralCareTotal, FormatPercent(-Sum(tblBundleCompliance![Head of Bed Elevated])/Count(tblBundleCompliance!OID)) AS HOB, FormatPercent(-Sum(tblBundleCompliance![DVT Prophylaxis?])/Count(tblBundleCompliance!OID)) AS DVT, FormatPercent(-Sum(tblBundleCompliance![Peptic Ulcer Prophylaxis])/Count(tblBundleCompliance!OID)) AS PUP, FormatPercent(-Sum(tblBundleCompliance![Daily Wake Performed?])/Count(tblBundleCompliance!OID)) AS DailyWake, FormatPercent(-Sum(tblBundleCompliance![Weaning Readiness Assessed?])/Count(tblBundleCompliance!OID)) AS WeaningReadiness, FormatPercent(Sum(IIf(tblBundleCompliance![Oral Care 0800]=True And tblBundleCompliance![Oral Care 1200]=True And tblBundleCompliance![Oral Care 1600]=True And tblBundleCompliance![Oral Care 2000]=True And tblBundleCompliance![Oral Care 2400]=True And tblBundleCompliance![Oral Care 0400]=True And tblBundleCompliance![Head of Bed Elevated]=True And tblBundleCompliance![DVT Prophylaxis?]=True And tblBundleCompliance![Peptic Ulcer Prophylaxis]=True And tblBundleCompliance![Daily Wake Performed?] And tblBundleCompliance![Weaning Readiness Assessed?]=True,1,0))/Count(tblBundleCompliance!OID)) AS ACM
    FROM tblBundleCompliance, [qryACS Query]
    GROUP BY DateValue(Format(tblBundleCompliance!Date,"yyyy/mm"));
    Though I don't know how helpful that will be. I'm going to try working with the crosstabs a little more today and see if I can make some headway on it, and I'll let you guys know if it works out. Thank you so much for your help!

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by nckdryr
    I've taken similar steps PK, but I would warn about bloating.

    But back to you benvarone, how about you explain the logic that goes into making those numbers. The WHERE criteria, the calculations, anything else.
    Bloating is never an issue for me since I use the vbs script in the code bank to always clone from a source mdb (thus the user gets a new clean copy every time when they open the mdb via the script.)

    Regarding the logic (for benvarone),
    1. Your first query is to clear out (ie. delete query) the data.
    2. You then simplify it by appending data in "groups" to append to the temp table. How you define those groups can be in many, many ways, but the end goal is to have a table whereby a final pivot or crosstab query can be done which produces the end results you want. Again, the tricky part is how to set up the temp table to produce those results you want. For example, I may use an expression in my calllogToTempTable Append query to get the first day of the week (based on the calldate field). I populate a "FirstWeekDay" field (along with perhaps the CallOutcome and a CC:1 (to sum the calls)) to the temp table and I can then create a nice crosstab of totals with a "First of The Week" column heading. My row heading of "CallType" (ie. Hung up, Left message, Declined, No Answer, etc...) and then the sum of the counts (CC: 1) is throughout the crosstab.

    You sort of have to work "backwards" when figuring out the fields/design for your temp table. Once your temp table is setup to produce a nice crosstab, then it's a matter of developing the queries to simply populate the temp table correctly from the dataset. Usually the most difficult part is working backwards from the crosstab to designing the temp table. After that, writing the append queries is usually fairly easy.
    Last edited by pkstormy; 09-20-09 at 03:39.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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