New to Access, and stumped...

[jakbauer]

I have a table (Table1) that looks like this:

ID Name FruitCode
1 Name1 14
2 Name2 11
3 Name3 11
4 Name4 8
5 Name5 11

FruitCode represents each student's favourite fruit, i.e.

Fruit FruitCode
Apple 8
Pear 11
Grapes 14

My goal is to create a report with a pie graph that charts Fruit categories (Apple, Pear, Grapes, etc.) by count of corresponding FruitCode from Table1. Using Table1 as an example, pie chart would look like this...

Apple 20%
Pear 60%
Grapes 20%


How would I go about making this happen? What combination of queries, tables, and formulas would be most efficient? I am very new to Access, and I've learned most of what I know through trial and error. This is my first project, and all of the queries that I've created thus far have been created using the builder (I'm not comfortable with SQL at this point...)

I would greatly appreciate any guidance/direction you guys could give me.

Thank you

 

[pr2-eugin]

jakbauer, Welcome to AWF..

You need to use a combination of JOIN and TOTALS query to get this result.. Something along the Lines of.

SELECT fruitTable.fruitNameField, Count(studentTable.fruitCode) As CountOfFruits
FROM fruitTable INNER JOIN studentTable ON fruitTable.fruitCode = studentTable.fruitCode
GROUP BY fruitTable.fruitNameField;

To use the SQL query.. Just change the Query Builder to SQL View..

Change the table names and the field names as per your table design..

 

[jakbauer]

Thanks a tonne Paul!

That worked perfectly for me.