Summarizing data using queries

[jgnasser]

Summarizing data

I have a field in which data is entered by a combo box thereby a record can take one of the following: lion, elephant, baboon, leopard etc. Typical values for the field would be;

Lion
Lion
Elephant
Giraffe
Leopard
Lion
Etc

I need to summarize this to know the number of times each species is recorded et for the above sample I want to end up with:
Lion - 3
Elephant - 1
Giraffe - 1
Leopard - 1

While I have managed to do for a single species by creating a query whose SQL looks like this,

SELECT Count(Mytable.Field) AS Animalcount
FROM Mytable
WHERE Mytable.Field="elephant";

I wonder if its possible to have one query that summarizes all species at once since it would be tedious to do a query for each species.

 

[ColinEssex]

In the query grid select the "GroupBy" option. You will only get one example of each species showing with the count.

Col

 

[Lister]

Hey Mate
If I have read and understood your question correctly this is pretty simple.
What you have to do is turn your SELECT query into a SUM query. You do that by writing a query the same way you would with a SELECT query and then click on the revers E on your tool bar and you get the GROUPING and SUM options.
I have attached a sample of what I think you are looking for.

Hope this is of some help.

 

[jgnasser]

Sorry Lister but I did not seem to get this correctly especially about changing from SELECT query to SUM query. Unfortunately I cant view the sample you attached, whenever I download it, my winzip just cant open it!

ColinEssex, I did just that and what I'm trying to avoid is to create a query for each unique item to be counted and I also ran into a problem pulling data from several queries onto one report.

 

[Lister]

Hmmmmm, hit Download.com for a sharware version of WinZip.

Failing that see if you can follow this SQL code to do the same thing.

SELECT tblAnimals.AnimalName, Count(tblAnimals.AnimalName) AS CountOfAnimalName
FROM tblAnimals
GROUP BY tblAnimals.AnimalName;

Notice the Count(enter table and field name) and GROUP BY (enter table and field name) statments?

So what the query dose is group all the animals together then cound each group.

Lions, 3
Kiwis, 4
Kangaroos, 0

And so on etc

See how you get on.

 

[jgnasser]

Lister, it worked! This is excellent. I know there had to be a way to do it simply but had no idea. I did it a long way; created a function which would accept an animal name as argument and respond with the count. I then created an unbound form and inserted individual controls which displayed the counts - cumbersome - eh! Thanks for this.