Counting occurences of entries from a single field

[betheball]

I looked for a forum titled "Help for Idiots", but couldn't find one. I guess I'll have to post my question here. I have a DB for troubleshooting a particular process. One of the fields is titled "Cause" and is a lookup field with 10 choices. I want to generate a report that will show how many occurences of the each choice are in the the main table. I am at best a novice when it comes to Access, so please make any answers a simple as possible.

 

[Agism]

I am a novice as well, compared to all the knowledge on this board.

My guess would be to use DCount().
I think the code would be: DCount("FieldName", "TableName", "FieldName = 'Criteria' ")

 

[WayneRyan]

betheball,

First experiment with a query ... you want to use group by and
count. What you are looking for is:

Select Distinct(Cause), Count(*)
from YourTable
group by Cause;

In the query design grid:

Select your table (Menu bar, table with + sign),
Close the table dialog box,
Double-click on the Cause field twice,
Choose the Sigma Sign,
In the first column - group by
In the 2nd column - Count
Hit the ! on the menu bar to run it.
Save your new query.

Base a report on that query.

Wayne

 

[betheball]

Wayne,

That was absolutely the clearest, easiest to follow answer I have ever seen. So, of course you had to know I would ask another question. I would like to keep a count of the volume of each cause in the "Cause" table. I created a field (column)called "volume". Can I somehow use the query to populate the volume field.

 

[WayneRyan]

betheball,

No, you don't want to store the count. You do not want to
store information that you can easily calculate. If you do you
will be investing a lot of coding time to keep this number
valid. Consider for example, a quantity on hand in an inventory
applicaton.

If you put the code in to maintain this number each time a
shipping order was amended, cancelled, etc. That's a lot
of code.

Wayne

 

[betheball]

Makes sense. So, what additional steps would I need to take to have my report or query show percentages. For example:

Cause 1= 38%
Cause 2 =27%

etc.

I then want to create a chart that would show this data.

Feel free to tell me to stop if I am asking too much. I just discovered this forum today and have been struggling with Access for a long time.

 

[WayneRyan]

betheball,

Now that's hard to explain.

Basically you make a simple query with just the count that you
used previously. There is no group by this time, it will just return only one field. You see that its Name is CountOfCause.
Save that query - Q1.

Now design your first query (from earlier - Q0). Click on the
Table with a plus sign and add your query, Q1. In the third
column type:

Expr1: [Cause]/[CountOfProj_uid]

Then run it with the ! sign at the top.

Have fun experimenting.

Wayne

 

[betheball]

Do I type this: Expr1: [Cause]/[CountOfProj_uid] verbatim? When I do that and then run the query a dialog box pops up asking me to enter a value for "CountOProj_uid".

 

[WayneRyan]

betheball,

My mistake,

Expr1: [Cause]/[CountOfCause]

Whenever it brings up the dialog box and asks you for a
parameter it means that something in your query can't
be resolved. CountOfProj_UID was my example, in yours
it should be as above.

Wayne

 

[betheball]

That got rid of the error message, but the results are not correct. Isn't what this is trying to do is divide each result in the first query by the result of the 2nd query? I am wondering if [Cause]/[CountOfCause] isn't working because 'Cause' is a text field, not a value? Let me know if I can post additional info or email you something if I am not being very clear.

Curiously, I can create a separate query with just the following field: Expr1: [Cause_Summary]![CountOfCause2]/[Cause_Summary2]![CountOfCause] and I get the percentages I need, but since it is the only field, I don't have the Causes to identify which cause each percentage belongs to. Unfortunately, if I try to include this field in my first query I get :

You tried to execute a query that does not include the specified expression <name> as part of an aggregate function.

I know I have to be close, just not quite there yet.