Counting number of variables in a field?

[Tom K]

Is there a way in Access to count how combinations occur in a field? For
example: If the field name is "Fruit" and I want to know how many different
types are listed in the db, can Access figure it out? (Assume you do not
know what the entries are, so you can't do an individual search -- I know
Access can do that) In my example, let's say the field contains apples,
oranges, pineapple, and pears. So there is 4 types of fruit in the db. Can
I get Access to figure out the "4"? And if later someone entered in "plums"
- Access would tell me "5"?

 

[Rick B]

If you create a query and include that field in your query, wehn you run it
you will see all the records. If you have two records with "apple" you
would see two apples.

To make it only show one instance of each, turn on the totals in your query
(click the symbol in the menu that looks sort of like an "E"). This will
cause a new field "Total:" to appear. Use the drop down to select "Group
By" nad then run your query. You should see only unique entries. You will
also see the record count at the bottom of your query.

Rick B

 

[Tom Wickerath]

Hi Tom,

You can create a query that will count the number of each type of fruit,
similar to the sample shown below for the Northwind sample database to count
how many customers live in each city. You would simply substitute "Fruit" for
"City" and "Customers" for the name of your table:

SELECT City, Count(City) AS CountOfCity
FROM Customers
GROUP BY City
HAVING City Is Not Null Or City<>"";

However, it sounds like the query you really want is more along the lines of:

SELECT Count(City) AS CountOfCity
FROM Customers
HAVING Count(City) Is Not Null Or Count(City)<>"";

Again, substitute Fruit for City and your table name for Customers.


Tom
______________________________________

:

Is there a way in Access to count how combinations occur in a field? For
example: If the field name is "Fruit" and I want to know how many different
types are listed in the db, can Access figure it out? (Assume you do not
know what the entries are, so you can't do an individual search -- I know
Access can do that) In my example, let's say the field contains apples,
oranges, pineapple, and pears. So there is 4 types of fruit in the db. Can
I get Access to figure out the "4"? And if later someone entered in "plums"
- Access would tell me "5"?

 

[Tom K]

I tried this approach and do not get the new "Totals:" field to come up. It
only causes fields to shift to "Group By"?

 

[Rick B]

It is not a field (sorry) it is a new row in the query builder. A new
selection.

Rick B

 

[Tom K]

I tried creating a query as you instructed, but the results are a listing of
each type of "fruit" and how records contain that "fruit." For example:

FRUIT CountofFruit
Apples 5
Oranges 13
Pineapple 7

etc... I'm looking for 3 fruit as answer..Thanks!

 

[Tom Wickerath]

Hi Tom,

Did you try the second example I gave you earlier? This should return just
one record, which represents the total number of unique fruit types.

Open a new query in design view. Click on View > SQL View. Copy & paste the
following query into the SQL view. Substitute the appropriate name for your
table:

SELECT Count(Fruit) AS CountOfFruit
FROM Customers <----Your table name
HAVING Fruit Is Not Null Or Fruit<>"";


Tom
______________________________

:

I tried creating a query as you instructed, but the results are a listing of
each type of "fruit" and how records contain that "fruit." For example:

FRUIT CountofFruit
Apples 5
Oranges 13
Pineapple 7

etc... I'm looking for 3 fruit as answer..Thanks!
______________________________

:

Hi Tom,

You can create a query that will count the number of each type of fruit,
similar to the sample shown below for the Northwind sample database to count
how many customers live in each city. You would simply substitute "Fruit" for
"City" and "Customers" for the name of your table:

SELECT City, Count(City) AS CountOfCity
FROM Customers
GROUP BY City
HAVING City Is Not Null Or City<>"";

However, it sounds like the query you really want is more along the lines of:

SELECT Count(City) AS CountOfCity
FROM Customers
HAVING Count(City) Is Not Null Or Count(City)<>"";

Again, substitute Fruit for City and your table name for Customers.


Tom
______________________________________

:

Is there a way in Access to count how combinations occur in a field? For
example: If the field name is "Fruit" and I want to know how many different
types are listed in the db, can Access figure it out? (Assume you do not
know what the entries are, so you can't do an individual search -- I know
Access can do that) In my example, let's say the field contains apples,
oranges, pineapple, and pears. So there is 4 types of fruit in the db. Can
I get Access to figure out the "4"? And if later someone entered in "plums"
- Access would tell me "5"?

 

[Tom K]

Yes, I tried that SQL View and it returned the total number of records, not
number of unique types...:-(

 

[Tom Wickerath]

Hi Tom,

You're right....I wasn't paying attention. In the Northwind database, using
the second query I presented initially, I get a count of 91. I should have
gotten 69.

Okay, try creating two queries. The first one will serve as a source of
records for the second query. I am showing an example for Northwind, using
the Customers table with the City field. You'll need to make the appropriate
substitutions:

First query saved as qryCities (or qryFruits in your case)
SELECT City
FROM Customers
WHERE City Is Not Null Or City<>""
GROUP BY City;

Second query
SELECT Count(qryCities.City) AS CountOfCity
FROM qryCities;

Your second query would likely be something like:
SELECT Count(qryFruits.Fruit) AS CountOfFruit
FROM qryFruits;


HTH better than my first attempt!

Tom
______________________________________

:

Yes, I tried that SQL View and it returned the total number of records, not
number of unique types...:-(
______________________________________

:

Hi Tom,

Did you try the second example I gave you earlier? This should return just
one record, which represents the total number of unique fruit types.

Open a new query in design view. Click on View > SQL View. Copy & paste the
following query into the SQL view. Substitute the appropriate name for your
table:

SELECT Count(Fruit) AS CountOfFruit
FROM Customers <----Your table name
HAVING Fruit Is Not Null Or Fruit<>"";


Tom
______________________________________

:

I tried creating a query as you instructed, but the results are a listing of
each type of "fruit" and how records contain that "fruit." For example:

FRUIT CountofFruit
Apples 5
Oranges 13
Pineapple 7

etc... I'm looking for 3 fruit as answer..Thanks!
______________________________________

:

Hi Tom,

You can create a query that will count the number of each type of fruit,
similar to the sample shown below for the Northwind sample database to count
how many customers live in each city. You would simply substitute "Fruit" for
"City" and "Customers" for the name of your table:

SELECT City, Count(City) AS CountOfCity
FROM Customers
GROUP BY City
HAVING City Is Not Null Or City<>"";

However, it sounds like the query you really want is more along the lines of:

SELECT Count(City) AS CountOfCity
FROM Customers
HAVING Count(City) Is Not Null Or Count(City)<>"";

Again, substitute Fruit for City and your table name for Customers.


Tom
______________________________________

:

Is there a way in Access to count how combinations occur in a field? For
example: If the field name is "Fruit" and I want to know how many different
types are listed in the db, can Access figure it out? (Assume you do not
know what the entries are, so you can't do an individual search -- I know
Access can do that) In my example, let's say the field contains apples,
oranges, pineapple, and pears. So there is 4 types of fruit in the db. Can
I get Access to figure out the "4"? And if later someone entered in "plums"
- Access would tell me "5"?