Count - Ms access update Query

[MUKESHY12390]

Hi All,


I have two field ID and Frequency, I am to trying to create update query so that I can get "ID" count in "Frequency" field.

Is there anyway I can do it in access by running update query.


BS,
Mukesh Y

 

[stumac]

This isn't something you would normally store in the database, you would normally calculate it at run time. If you store it and then someone adds a record your data would then be incorrect... unless you need the count at a specific point in time.

The query to view the data would be:

SELECT ID, Count(ID) AS Frequency FROM [I]MyTable [/I]GROUP BY ID

If you need to store it (not sure why you would, but if you do) one way is using Dcount:

UPDATE MyTable SET MyTable.Frequency = DCount("*","MyTable","ID = " & [MyTable].[ID])

depending on size of table it may be quicker running a make table query and using that in an update query.

 

[MUKESHY12390]

Thanks Stumac. I just realized using Dcount slow down the whole process.

make table query and using that in an update query.

this works perfectly I wanted.