Dual Column Graph

[jschneiter]

Hello,



I am going to try and describe my situation the best I can.

One of my clients is a non profit agency that does Alcohol and Tobacco compliance checks. Basically they have an under21 age user walk into an establishment and try to order something.

If they don't sell to them its a PASS and if they do its a FAIL

Each entry has a Date, Time and Pass or Fail

I need a graph to show how many passes and how many fails they had each year.

I know how to extract the year from the date so I use that as my X Axis
On the left Y Axis is 0 - whatever who number

What I cant figure out is how to get two bars on each year. One for Pass and one for Fail. They always get combined into one

No matter if I use the Classic Graph or the new Modern Graph I cannot figure out how to get the separate Pass and Fail bars for each year.

Suggestions?

Thanks in advance

[June7]

I use Access2010 so only have Classic MSGraph available.

Is Pass or Fail a text field? What are you using as chart RowSource?

Build a CROSSTAB query object.

TRANSFORM Count(*) AS Cnt
SELECT Store, Year([Date]) AS Yr
FROM Ratings
GROUP BY Store, Year([Date])
PIVOT Score IN ("Pass", "Fail");

Set chart RowSource to: SELECT Yr, Pass, Fail FROM Query1 WHERE Store = [tbxStore];

tbxStore is textbox on form (or report) bound to field with establishment name or ID. Make sure textbox name is not same as field name - hence the 'tbx' prefix.

Date is a reserved word and advise to not use reserved words as names.

[CarlettoFed]

If you attach a sample file it is easier to show you how to do it than to describe it.

[jschneiter]

Attached is a VERY stripped down version with all the queries and tables for this particular issue

This is all based on a query
When you open the query it will ask for an ID

Enter either 30 or 40 as those are the ID's for the Stores

(Normally it grabs it from the open form but that form is not included)

If you want to permanently add that store so it doesn't ask anymore, you can change the criteria on the Qry_storeLookup Query to either 30 or 40

Thanks for the Help!!!

[June7]

Opening form triggers error message that object cannot be found.

I don't use dynamic parameterized queries. I prefer to apply filter to form or report.

I don't see that you attempted my suggestion in post #2. Is there something you did not understand?

[jschneiter]

June7,

I apologize. I I will implement it tonight when I get home. You shouldn't get any errors, You should be able to open the qry_passfail and get a prompt to enter an ID, weird your getting errors.

After I try what you suggested tonight I will touch base. I thought I would upload the data I have now and see what you thought.

Again, sorry for the misunderstanding, but I will try to work on that tonight.

Thanks again

[June7]

I can open queries but I wanted to just open form and that errors because form referenced in query does not exist. Shouldn't have to open queries. And with the changes I outlined, that will be the case. Again, I don't use dynamic parameterized query objects.

[jschneiter]

I figured it out!! With thanks to June7


Since I now officially despise the Modern Chart system, I went back to using the Classic. I honestly think the Classic looks so much better as well.

[CarlettoFed]

In the attachment you will find the example.
CCCC_AccessForums.zip