Counting data from a look up table field 1

[Volk359]

Hello all,

I've got a field named coffee that I'd like to count the number of times people drink it black or with cream, etc. The data is selected from a look up table and the query creates a variable which will then populate a report.

My lookup table called coffee_flavor contains one field (flavor) with the following data:

Black
Cream
Sugar
Cream & Sugar

In the query I use the following formula to count them:

black_count: Count([coffee]=Black)
etc.

The problem is I think Access is assuming the data Black is a field and is giving me a Enter Parameter Value window and encloses it with brackets. If I enclose Black with quotes it gives me the number of records where this field has data in it.

Is the format/syntax wrong or should I be using a different function?

 

[lespaul]

Ok, so you must have a table somewhere that lists drinking preferences:

tblPreferences
PersonID
Flavor (where flavor is a FK to the lookup table)

write a query:

SELECT Flavor, Count(Flavor) As CountOfFlavor FROM tblPreferences GROUP BY Flavor

you'll get:
Flavor CountOfFlavor
Black 16
Cream 30
Sugar 24
Cream & Sugar 115

HTH






Leslie

 

[Volk359]

OK, I first typed this in:

SELECT Flavor, Count(Flavor) As CountOfFlavor FROM coffee_flavor GROUP BY Flavor

and got an error: The syntax of the subquery is incorrect. Check the syntax and enclose in parenthesis

I assumed the syntax was correct so I enclosed it in parens the got the following error:

You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

It also stuck an expression in front of it: Expr1:

 

[lespaul]

You typed that in where? You should have created a new query, switched to SQL view and pasted it.

There's no sub query in what you have shown above.

Isn't coffee_flavor the lookup table? Didn't you say you only have one field in that table? How are there going to be more than one of each flavor in that table? You need to be counting the table that has MULTIPLE instances of the lookup information.


the query you have above is going to return:

Black 1
Cream 1
Sugar 1
Cream & Sugar 1

because that's how many times each type of flavor appears in this table.



Leslie

 

[Volk359]

Oops, I feel like a dope... :~/ pasted it in the wrong area.

Yes, coffee_flavor is the look up table and Beverages is the main table. You're correct, by pasting the above code in (where it's supposed to be) I do get 1 for each type but if I use Beverages as the table it asks for a parameter and when I type one in, black for example, it returns a count of total records.

 

[lespaul]

what is the query you are running against Beverages when it asks for the parameter?


Leslie

 

[Volk359]

Yes

 

[PHV]

Have you tried something like this ?
SELECT coffee, Count(*) As CountOfFlavor
FROM Beverages
GROUP BY coffee
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Volk359]

Thanks PHV, that gives me exactly what I'm looking for.

My thanks to you too, Leslie!

 

[lespaul]

wow PHV, you must have done well in that mind reading course!


Leslie

 

[Volk359]

Again, my thanks to both of you. Any way to get percentages from these results?

 

[PHV]

Like this ?
SELECT B.coffee, Count(B.coffee) As CountOfFlavor, Format(Count(B.coffee)/T.Total, "percent") As Percentage
FROM Beverages B, (SELECT Count(*) As Total FROM Beverages) T
GROUP BY B.coffee, T.Total
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Volk359]

You're batting 1000, PH. My hat's off to you, thanks!

 

[Stolman]

Excellent thread and superb advice! I have a similar and slightly more complicated opportunity. Rather than give my complete scenario, I will use the "coffee" example.

Assume that each BEVERAGE record had room for 4 coffee orders instead of 1. Each of the 4 flavor fields in BEVERAGE references the same Flavor field in the COFFEE_FLAVOR lookup table. So, there are multiple flavors in each BEVERAGE record. Assume no duplicates (e.g., no single BEVERAGE record would have more than one "black").

Given this, can some bright soul please advise how to code the Most Efficient count of each flavor? Assume 30,000 BEVERAGE records and assume 200 unique coffee flavors.

Many thanks in advance.
Bill
Scottsdale, AZ

 

[PHV]

First, normalize your BEVERAGE table with an UNION query for each of the 4 flavor fields and then apply the grouping query to the normalized one.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Stolman]

Thanks PHV. Alas I am still a novice with no mentor to query save the useless books I purchased and of course, this forum. Might I prevail upon your expertise to kindly provide a simple example of how I might accomplish this?

Many Thanks, -b

 

[PHV]

Say you have a table Beverages with this columns:
PersonID, coffee1, coffee2, coffee3, coffee4
The normalization query would be something like this:
SELECT PersonID, coffee1 As coffee FROM Beverages WHERE coffee1 Is Not Null
UNION SELECT PersonID, coffee2 FROM Beverages WHERE coffee2 Is Not Null
UNION SELECT PersonID, coffee3 FROM Beverages WHERE coffee3 Is Not Null
UNION SELECT PersonID, coffee4 FROM Beverages WHERE coffee4 Is Not Null
ORDER BY 1, 2;
Save it as, say, qryNormalizedBeverages
And then you can use something like this:
SELECT coffee, Count(*) As CountOfFlavor
FROM qryNormalizedBeverages
GROUP BY coffee
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Stolman]

Thanks PH! Yes, it does indeed help. But I might have missed something as the count results are ONE for each Flavor, when in fact there are thousands. Any suggestions?
Thanks,
-bill

 

[Stolman]

Hi PHV,
Please disregard previous post. It works perfectly now that I added ALL to each UNION. MANY thanks for your excellent advice AND your prompt posts.

Most Gratefully,
-b