Re: assign numbers to the occurence of data

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks a lot, Ken. It works like a charm. Can you suggest a good book which
has lots of examples on the different SQL queries?

"Ken Snell [MVP]" wrote:

> Slight typo corrected below:
>
> UPDATE Tablename SET NewFieldName =
> DCount("*", "Tablename", "DataFieldName='" &
> [DataFieldName] & "' And PrimaryKeyFieldName<=" &
> [PrimaryKeyFieldName]);
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
> "Ken Snell [MVP]" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in message
> news:OPlW36hXFHA.3760@xxxxxxxxxxxxxxxxxxxxxxx
> > Is ther a primary key on the table? Assuming that there is (and that it's
> > just one field), you can use an update query to write "ranking" values to
> > the new field that you've added. Something like this (note: use of DCount
> > will make the query run a bit slowly):
> >
> > UPDATE Tablename SET NewFieldName =
> > DCount("*", "Tablename", "DataFieldName='" &
> > [DataFieldName] & " And PrimaryKeyFieldName<=" &
> > [PrimaryKeyFieldName]);
> >
> > --
> >
> > Ken Snell
> > <MS ACCESS MVP>
> >
> >
> >
> > "sharman" <sharman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:F5F45F3C-AB2B-41F8-BB5E-A129E2DB7DBB@xxxxxxxxxxxxxxxx
> >>I have a database that contains a field having duplicate data. I have
> >>created
> >> another column and I want to insert numbers in the other column that will
> >> start at 1 and increment up to the count of the data. For example if
> >> "ABC"
> >> occurs 3 times in the first column then in the second column it will be 1
> >> for
> >> the first occurence of "ABC", 2 for the second occurence of "ABC" and 3
> >> for
> >> the third occurence of "ABC". Similarly, if the data occurs 100 times
> >> this
> >> count will increment up to 100 for that data.
> >>
> >> Can anyone please let me know how this can be accomplished either
> >> programatically or by a query? Thanks in advance.
> >
> >
>
>
>
.

Relevant Pages

  • Re: assign numbers to the occurence of data
    ... UPDATE Tablename SET NewFieldName = ... you can use an update query to write "ranking" values to ... > <MS ACCESS MVP> ...
    (microsoft.public.access.macros)
  • Re: Append to table
    ... The problem appeared to be that I had not set the key fields increment ... I have an append query that adds a record to a Table. ...
    (microsoft.public.access.forms)
  • Re: Calculate Query and Checkboxes
    ... the desired RecordID. ... Update Tablename Set Fieldname = False ... > the checkboxes are to 'tag' items to be printed. ... > ability to change the check value from the form or query. ...
    (microsoft.public.access.queries)
  • Re: Plus One Button on Form
    ... "John W. Vinson" wrote: ... Dont get me wrong, of course there are tables in my DB and this is where all ... I was thinking of making a query for the 'plus ... What value do you want to increment? ...
    (microsoft.public.access.forms)
  • Re: Plus One Button on Form
    ... "John W. Vinson" wrote: ... Dont get me wrong, of course there are tables in my DB and this is where all ... I was thinking of making a query for the 'plus ... What value do you want to increment? ...
    (microsoft.public.access.forms)