Chart grouping in a report

[james28]

Hello everyone!




I am running into a small issue when trying to create a chart in a report in access 2010.


I have a simple table that I am running a simple query off of. The query is pulling the birthday from everyone in the table as well as the date that they signed up. From there, I created a calculate field called "SignUpAge" which uses the birthday and signup date to calculate a sign up age.

So far, everything seems to be going just as I would like.


The problem is, I want to create a bar chart that displays "SignUpAge" from the query. I can create a chart with no problem, but how the chart is displayed is what is giving me trouble. For instance, I want it to be grouped in chunks. Age 1-10 would be one bar, 11-20 would be the next, etc.

I can only get it to display grouped by each individual age, not by each age range.


I feel like I need to create a summary table of some sort , to somehow count the number of "SignUpAges" that fall between each age band.

How I would go about doing that is beyond me though.


Any help that could be given would be extremely appreciated.


Thank you !

James

[June7]

Create a field in query with expression to calculate age groups.

Switch([SignUpAge]<11, "Age 1-10", [SignUpAge]<21, "Age 11-20", [SignUpAge]<31, "Age 21-30", [SignUpAge]>=31, "Over 30") AS AgeGroup

[james28]

June,

Thank you for the reply. You seem to be saving the day every where today (and every day, based on the help I have seen from you on threads by other people)!


I am new to this, but I think I see where you are going. Although I was not able to follow completely.

I go into my query, into design view, and then what? I clicked on a new column and entered the expression you provided, exactly as you provided it, and it did not like it.

I then went into criteria, right clicked, and went to builder, and entered it there, also with no success.


Your example then sparked the thought that maybe I could make each age group its own field. So I created AgeUnder20: 20.01>[Age]>10 and all of the other groups independently. Although this did not give me any errors, when I run the query I just get a bunch of 0s for everything, even though there are entries that meet the criteria.

I would appreciate any further guidance you can give.

Thank you again,
James

[June7]

You did this in the query that already calculates the SignUpAge? Didn't you say the calculated field name is SignUpAge? Why are you using Age?

What happened - error message, wrong results, nothing?

Post the SQL for analysis.

[james28]

June,

Thank you again.

You are right. I am using SignUpAge. I free handed my example and forgot that is what I called it.


I understand now!!!!!!! I was pasting your entire line, including the "as agegroup".


For anyone that may find this later and that happens to be as dense as I am.... you will want to paste: Agegroup: Switch([SignUpAge]<11, "Age 1-10", [SignUpAge]<21, "Age 11-20", [SignUpAge]<31, "Age 21-30", [SignUpAge]>=31, "Over 30") into the "field" section of a new column in your query.




Two more questions,

Although everything is working fine now, if I had to "Post the SQl for analysis", what would that entail? Changing "design view" to "Sql view" and pasting everything that shows up?

Also, is there an easy way to show the Average age of the people in my chart, in my report? I have added a "total" row to my query, (the one that contains the SignUpAge) and set it to "average". This is perfect and displays the proper value. I am just not sure how to get it into my report.


Thank you!

[June7]

Yes - SQL View.

If you use the query as report RecordSource, a textbox in report footer could have expression: =Avg([SignUpAge])

[james28]

June,

Thank you for the reply. Is there anyway to do it if I used a blank report? I used a blank report because I needed to create charts from 12 or so queries.

If not, I guess I could always use a blank report with 12 sub reports, based on queries, in it. I would like to avoid that way if at all possible though.


Thanks again!

[June7]

Yes, can use domain aggregate function expression in textbox, like:

=DAvg("Age", "queryname", "filter criteria here if needed")

[james28]

June,

I really appreciate all of the time you have spent helping with my various issues today.

The example that you gave worked great, with just a small tweak. I had to add in [].

For anyone that finds this later, I used =DAvg("[SignUpAge]","birthdayquer")

~Marked as resolved.


Thank you again!