Counting Query 1

[ElectronikBean]

Hello all,

I am attempting to run an update that counts the number of occurances of a value and then updates the result in another table. My problem is with counting the occurances...

for the sake of example...

Table1
------

UniqueID|GroupID|Full_Name
| |
1 | 21 | Joe Bloggs
2 | 21 | Harry Smith
3 | 21 | James Last
4 | 21 | Brian May
5 | 21 | Steve Tyler
6 | 43 | Axel Rose
8 | 43 | Ringo Star
10 | 43 | Joe Black
12 | 43 | Paul Smith
13 | 43 | Freddie Mercury
14 | 92 | Harry Stamper
15 | 92 | Obi Wan Cenobi
16 | 92 | Yoda Dagoba

On this table, I can run the equivillent of the following simple query that runs a count of the existing records where the valueid = 43.

SELECT Count(table1.UniqueID) AS NumberInGroup
FROM table1
WHERE (((table1.GroupID)=43));

This will return the expected single record result, NumberInGroup, 5.

My question is, how do I count the number of occurances of the GroupID for all of them, to achive the following view...


qryGroupTotals
--------------

GroupID|NumberInGroup

43 | 5
21 | 5
92 | 3

... from which I will be able to update my other table...

I would much appreciate your help...

 

[Maquis]

Set up a group-by query. (Use the greek sigma symbol in the toolbar). 2 fields, Group by GroupId and Count GroupId

Or if you prefer, paste this into your SQL view and it should work out right, renaming Table1 to your table name of course:

SELECT Table1.GroupID, Count(Table1.GroupID) AS NumberInGroup
FROM Table1
GROUP BY Table1.GroupID;

Next you can turn this query into an append query and append the rows to the appropiate table.
Maq
<insert witty signature here>

 

[LittleSmudge]

You need

SELECT GroupId, Count(GroupId) AS NumberInGroup
FROM tblCountGroups
GROUP BY GroupId;

QED.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D