Showing Distribution of Values in Access?

[Paperclipper]

Hi all! I was wondering if it was possible to use Access 2000 to do the following:

1)Select all the records in a database based on values in one column...
2)Spit out a table that shows how many of those records have A) value of 0 or Null, B) 1, C) 2 or more...

thank you!

 

[Joe4]

Sure, you should be able to do this using queries. Below is an example:

Let's say we have a table with two fields, ClientName and PlanType. If we wanted a table to give us a breakdown of the total count of plan types for clients that start with the letter "B", we could do the following:

In a query, create an expression to capture the first letter of the ClientName, i.e. Letter: Left([ClientName],1). Put criteria on this field of "B".

Now add the PlanType field to the query twice. Then click on the Totals icon (picture of a sigma). In the second PlanType field, change the Totals option from "Group By" to "Count".

Now if you view the query, it will count up all the different plan types for clients that begin with the letter B.

Notice the other Totals function, such as SUM, which would be good if you wanted a count up something like total number of employees for a plan type.