A complicate update query...please help

[polina]

Here is my problem:

The table looks like that:

Name Count

Joe

Polina

Joe

Nick

Polina

Nick

Nick

Joe 2
Polina 2
Nick 3

The last three lines represent the count of how many times each name appears in the table.

So, what I need is UPDATE QUERY to fill the rest of the COUNT column with the count of how many times this name has appeared so far

The result should look like that:

Name Count

Joe 1

Polina 1

Joe 2

Nick 1

Polina 2

Nick 2

Nick 3

Joe 2
Polina 2
Nick 3



PLEASE HELP ME WITH THIS....I AM DESPERATE

 

[The_Doc_Man]

You realize, of course, that this table is not properly normalized as you showed it...

If your table has only two fields, the only way to do this - particularly for unnormalized tables - depends on the fields being null/blank/zero in some cases.

In essence, you can do this from a query grid (i.e. Design view, not SQL view). To keep it consistent, you need two queries.

Let's say for the sake of argument that the table is

tblTable
- strFName
- loRefs

First, build a query that resets loRefs to 0 for ALL records. But this is optional and should only be used if your intent includes to reset ALL records.

Second, build a query that does a sort by strFName (using the Sort row in the query grid). Make it update the loRefs field with the value...

1 + DMax( "NZ([loRefs],0)", "tblTable", "[strFName] = """ & [strFName] & """" )

Make the update criterion depend on NZ([loRefs],0) being 0. This might be a separate column in your update query. You might not wish to reset counts that were set earlier, in which case the update query DEFINITELY needs a selection criterion.

 

[Rich]

You SHOULDN'T store these counts at all. A totals query will give the count per person and a running sum on a report will give the second answer you require