Create a new field that counts another field

[rockell333]

I want to create a field that counts how many times a specific id appears in a table. It will be helpful after I cleanse to see if things are wrong

[pbaldy]

Typically done with a totals query. For a specific ID

SELECT ID, Count(*) AS HowMany
FROM TableName
WHERE ID = 123
GROUP BY ID

[NTC]

since you self describe yourself as a novice; in query design mode - you have your table in the upper section, and bring down the field to be counted (or summed) into the lower section.... if there is to be a criteria such as ID 123 you need that field too in order to enter the criteria.

then look for the sigma symbol in the ribbon - like a big E - and this is generically called an aggregate query; your 2 fields will default to 'Group' and they may be ok for the criteria field...but your counted field you want to change that to either count or sum as appropriate....

[rockell333]

@pbaldy

This will just be in a view correct? I am hoping for an actual field. Sorry for the delayed reply I went around it this time, but would love to know for next time

[pbaldy]

If you want a saved field you'll need to do it yourself. There's nothing built-in that will count other records. Most of us would counsel against trying to save that type of thing though.