Update query for total occurrences of a string

[BFP]

Hi All.

My main dB table is based on a linked Excel spreadsheet.

In the table, I have a column called FILE_NAME that contains a web page file name. Each file has one or more unique IDs that identify a comment. I also have a column called "# Coms".

I would like to
1) Calculate the number of times each file string name occurs
2) Using an update query, I would like to populate the "# Coms" with the number calculated in 1 for every row in the table.

Example:
FILE_NAME ID # Coms
1516.html 5327 2
1516.html 6195 2
4185.html 2999 1

Note: currently the table is sorted by the FILE_Name String

Thanks in Advanced,

BFP

 

[PHV]

1)
SELECT FILE_NAME, Count(*) AS NumOfComs
FROM yourTable
GROUP BY FILE_NAME;
2)
UPDATE yourTable SET [# Coms] = DCount("*", "yourTable", "FILE_NAME='" & FILE_NAME & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BFP]

That did the trick...in fact I only needed the update query.

Thanks PH.