Total clients ages by year

[larryo]

I am relatively new to Access, and am attempting to develop a Client database
for a non-profit. So far I think I've done fairly well with tables and input
forms. Now I am starting to develop Reports.
One of the inputs on the form is date of birth. I have created a query
that, by using an expression, enables me to calculate age from date of birth.
(Ages range from 21 through 93, but not every one is populated - for
example, there might be three clients aged 41 but none age 42, and then 2
aged 43, etc.) Now I'd like to create a report that I can eventually put in
graph (bar chart) showing those ages on the x axis, and the number or
quantity on the y axis. I have spent about three hours going over Q&A in the
discussion group and I guess if I poked around for another day or so, I might
be able to figure it out but I'm hoping someone can point me in the right
direction. At this point, after reading all those Q&A's, I'm pretty much
confused. Do I do another query to do this, or a report, or what?

 

[John W. Vinson]

I am relatively new to Access, and am attempting to develop a Client database
for a non-profit. So far I think I've done fairly well with tables and input
forms. Now I am starting to develop Reports.
One of the inputs on the form is date of birth. I have created a query
that, by using an expression, enables me to calculate age from date of birth.
(Ages range from 21 through 93, but not every one is populated - for
example, there might be three clients aged 41 but none age 42, and then 2
aged 43, etc.) Now I'd like to create a report that I can eventually put in
graph (bar chart) showing those ages on the x axis, and the number or
quantity on the y axis. I have spent about three hours going over Q&A in the
discussion group and I guess if I poked around for another day or so, I might
be able to figure it out but I'm hoping someone can point me in the right
direction. At this point, after reading all those Q&A's, I'm pretty much
confused. Do I do another query to do this, or a report, or what?

A Crosstab query might be the ticket here. Try creating a new Query using the
crosstab query wizard; use your calculated Age field as the column header.

Or you may instead need a Totals query, grouping by the Age field and
selecting Count on the primary key field. You may be able to base your chart
on this query.

John W. Vinson [MVP]

 

[Ken Sheridan]

If you want the chart to be representative of all ages you need to fill in
the gaps. This is done by creating a new table, Ages say, with one column
Age with values for every age. You could have rows with values from 21 to 93
or you could extend this up and/or down to allow for any new clients outside
this range.

You'd then return one row for each age in a query based on the Ages table
and count the rows in your current query for each age value in a subquery.

SELECT Age,
(SELECT COUNT(*)
FROM YourQuery
WHERE YourQuery.Age = Ages.Age)
AS NumberOfClients
FROM Ages
ORDER BY Age;

This would return zero number of clients for the unrepresented ages.

If you only want the ages represented among your clients represented in the
chart then you don't need the Ages table so the query would simply be:

SELECT Age, COUNT(*) AS NumberOfClients
FROM YourQuery
GROUP BY Age;

Whichever you use you can use the chart wizard to insert a chart of whatever
format you wish from the options available, based on one of the above
queries, in a report.

Ken Sheridan
Stafford, England

 

[larryo]

John,
Your suggestions are very much appreciated. I was able to do the
"cross-tab" query, and it did provide me with a sum all the clients of the
same ages. That was great progress However, I wasn't able to create a graph
from the result. I think that's because I need to read/learn more about
graphs. I wanted to be able to produce a bar chart, but it kept giving me a
chart with quarters as the x-axis, and North, East, and West for the y-axis.
Whew, this program is tough! As for the Totals query, I had absolutely no
luck when I attempted to create that. When I go in to the Query Wizard, my
only options are Simple or Cross-tab. I also attempted the second option
suggested by Ken Sheridan in his response to my question, and that, too, gave
me the summary I was hoping to get. Now I need to work on the graph/chart
aspect. Again, thanks so much for responding. I don't know where I'd be
without the assistance from experts like you on this forum.

 

[larryo]

Ken,
Thank you so much for your comprehensive response. I opted for your second
suggestion, and I'm not sure I could duplicate what I did, but it worked,
giving me the number of clients of the same age. I then attempted, about 10
times, to get it to display in a graph/chart - no luck! That's not your
problem, it's because I am not skilled at this. What I got was a bar chart
showing four quarters in the x-axis, with three different colored bars in
each and a legend indicating the bars were North, East, and West. The
elevation of the bars did seem to represent the ages as one of them seemed to
be about 93, which is the age of our oldest client. Again, thanks so much
for your response. If I live long enough, I just might master this.

 

[Ken Sheridan]

The north, east etc which you see in the chart in report design view is how
Access, somewhat confusingly, shows a chart in design view, not a
representation of the data itself. Provided you've got just the two columns,
age and number of clients returned by the query, it should show correctly
when the report is opened in print preview or printed. You'll probably have
to play around with the size of the chart in report design view to get the
best fit for your data. You can choose whether to have a legend or not as
you work through the steps in the wizard.

Another approach is to export the query's results to Excel and create a
chart in the workbook. I find Excel charts give more scope for formatting
than inserting a chart into an Access report.

Ken Sheridan
Stafford, England

Ken,
Thank you so much for your comprehensive response. I opted for your second
suggestion, and I'm not sure I could duplicate what I did, but it worked,
giving me the number of clients of the same age. I then attempted, about 10
times, to get it to display in a graph/chart - no luck! That's not your
problem, it's because I am not skilled at this. What I got was a bar chart
showing four quarters in the x-axis, with three different colored bars in
each and a legend indicating the bars were North, East, and West. The
elevation of the bars did seem to represent the ages as one of them seemed to
be about 93, which is the age of our oldest client. Again, thanks so much
for your response. If I live long enough, I just might master this.