Count occurence of a value in a field

[Jon von der Heyden]

Hello again

Today I have a table with a column called 'Concat'. This column contains multiple values. I have an update query and I want to update another column with the occurence # of the corresponding 'Concat' value. What do you think is the best approach?

Just to demonstrate this is how I would do it in excel:

Excel Workbook
	A	B
1	Concat	Occurence
2	1700000023/2006-5160	1
3	1700001149/2006-5160	1
4	5106264863/2006-5160	1
5	5106264863/2006-5160	2
6	5106266720/2006-5160	1
7	5106267549/2006-5160	1
8	5106367910/2006-5913	1
9	5106367910/2006-5913	2
10	5106367910/2006-5913	3
11	5106367993/2006-5913	1
12	5106367993/2006-5913	2
13	5106368651/2006-5913	1
14	5106368652/2006-5913	1
15	5106368653/2006-5913	1
16	5106368654/2006-5913	1
17	5106368656/2006-5913	1
18	5106368656/2006-5913	2
19	5106368658/2006-5913	1
Index

Excel 2003

Cell Formulas
Range	Formula
B2	=COUNTIF($A$2:A2,A2)

 

[SydneyGeek]

Hi Jon, the quickest way to do this in Access is to create a Totals query.

1. New query, using the table of interest
2. Add Concat to the grid twice. Click just before the second and type Occurrences: (with the colon). This aliases the field, giving it a different name.
3. Click the Totals icon (the big Sigma). You'll see a new row in the grid. Keep the first as Group By; change the second to Count.
4. Run the query to get your summary. If you want to filter for duplicated values only, go back to design view and type >1 in the Criteria row for the field.

If you like you can update another table; alternatively, just run this query each time you need an update. Pushing the data elsewhere effectively snapshots it, but if the underlying data changes you need to re-run the Update query.

If I missed the point, let me know

Denis

 

[SydneyGeek]

If you're looking to create a frequency distribution I came across this cool technique...
Look about halfway down the page.

Denis