Count with a twist

[Guest]

Hi all,

I need a query that will count and place that counted number into the field
I provided. Example:

My table has five fields:
Catergory V Factor Countfield Action VP
3A1 Tax Done George
3A2 UnTax Pending Joe
4B1 Tax Done Jack
4B1 UnTax Done Tim
4B1 Tax Done
Jim
3A2 Tax Pending Tom

I want to count all the catergories that are the same and add the count to
'Countfield' . The table should now look like this:

Catergory V Factor Countfield Action VP
3A1 Tax 1 Done George
3A2 UnTax 2 Pending Joe
4B1 Tax 3 Done Jack
4B1 UnTax 3 Done Tim
4B1 Tax 3 Done Jim

3A2 Tax 2 Pending Tom

Is this possible? Thanks

 

['69 Camaro]

Hi.

Is this possible?

Yes. Use a correlated subquery for the count. Try the following example
syntax:

SELECT Category, [V Factor],
(SELECT COUNT(Category)
FROM tblCategAndFactors AS CF
WHERE CF.Category = C.Category) AS Countfield,
Action, VP
FROM tblCategAndFactors AS C;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.