Making a graph/chart using DCount

[Currancchs]

Hi guys,



I have a query where I use the DCount function to give me four separate fields. I want each of the field titles to become a portion of a pie chart. My problem is that I need to have two fields only for the pie chart, one with the DCount field names, and the other with the values. I cannot figure out how to rearrange the data. This can't be as hard as I'm making it, any advice (including you're doing it wrong, do it this way) would be appreciated.

Thanks!

[June7]

Sounds like need UNION query to rearrange the data in normalized structure.

SELECT "field1" As SourceField, field1 As CountData FROM queryname
UNION SELECT "field2", field2 FROM queryname
UNION SELECT "field3", field3 FROM queryname
UNION SELECT "field4", field4 FROM queryname;

There is no query wizard or designer for UNION, must type in the SQL View of query builder.

[Currancchs]

Thanks for the information, once I get the data better organized, do you know how I would get it from being a pivot chart into a report. I assumed that you would be able to set default view to pivot chart and drag into a report as a subreport, but that is not working for whatever reason (I tried with another set of data I was able to graph). The option for default view as pivot chart is no longer there once dragged into the report.

[Currancchs]

This is what I wrote, but I'm sure I got the syntax wrong somehow...

SELECT "Licensed Marks" As SourceField, Licensed Marks As CountData FROM [Pie Chart - Base]
UNION SELECT "Protected and Licensed Marks", Protected and Licensed Marks FROM [Pie Chart - Base]
UNION SELECT "Registered Marks", Registered Marks FROM [Pie Chart - Base]
UNION SELECT "Unlicensed Registered Marks", Unlicensed Registered Marks FROM [Pie Chart - Base];

I tried the field names with and without quotes/brackets... keep getting errors...

[June7]

Names that include spaces must be enclosed in [].

SELECT "Licensed Marks" As SourceField, [Licensed Marks] As CountData FROM [Pie Chart - Base]
UNION SELECT "Protected and Licensed Marks", [Protected and Licensed Marks]FROM [Pie Chart - Base]
UNION SELECT "Registered Marks", [Registered Marks] FROM [Pie Chart - Base]
UNION SELECT "Unlicensed Registered Marks", [Unlicensed Registered Marks] FROM [Pie Chart - Base];

[Currancchs]

Thanks again for bearing with me. I played around with it some more with a simpler query and figured out the proper syntax. However, I could not base my Union Query off of another query for some reason, I had to convert the original query to a make table query, then base the union query off of the table. Is this normal functionality or should it be possible to base a union query off of a query? I kept the syntax exactly the same, just created a table with an additional character in order to test the theory that syntax was OK but the query would only work when based off of a table...

[Kirsti]

Hi, seems strange - for what it's worth, I have never based a Union Query off a table, I always base them off Select Queries.

[June7]

I can best analyze graph issue by working with data. If you want to provide project, follow instructions at bottom of my post.

[Currancchs]

Unfortunately there is some proprietary information involved in this database and I cannot provide the database itself. If that means you cannot provide further assistance, I understand, and am grateful for the help you've already given. The SQL for the Union Query is pasted below though. Further, I made a table with just the name table added to the end of this, but otherwise the same syntax that gives no errors and works as anticipated. The query that the union query is based off of is a dcount query.

SELECT "Acknowledged Actions Due" AS [Action Items], [Acknowledged Actions Due] AS [Number] FROM [Actions Due Comparison]
UNION SELECT "Actions Due", [Action(s) Due] FROM [Actions Due Comparison];

[June7]

Building UNION of other queries should be possible. Maybe the DCount is a complication. Don't know, never tried before. Why is DCount necessary? Maybe a GROUP BY aggregate query is possible. Again, would have to work with data to better understand issue.

Don't need real info, dummy records would serve. Would only need objects relevant to this issue.

If you don't want to scrub the data and provide db for analysis, don't think I can help further.

[Currancchs]

Thanks for the help guys, just needed to play around with it long enough. Wound up getting it to work, but locking my recordset in the process, so then I made two separate count queries and that did the trick, along with the below SQL union statement:

SELECT "Acknowledged" AS [Action Items], IIF(ISNULL(sum([CountOfApplicationNumber])),"0",sum([CountofApplicationNumber])) AS [Number] FROM [AcknowledgedRenewalCount]
UNION SELECT "Unacknowledged", sum([CountOfApplicationNumber]) FROM [ActionsDue];

Marking as solved.