By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,573 Members | 3,017 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,573 IT Pros & Developers. It's quick & easy.

enumerate in ms-access

P: n/a
hello,

I would like to know if there is a way in ms access 2003 to add
consequtive numbers to a new column to 'count' the records. This count
should start over by every by variable. for example:

I have a table:

field1 field2 field3
1 AA BB
1 AB BC
1 AC BS
2 AA BC
2 AN BS
3 DD EE
3 EE FF

In the final situation a new table or query should be created looking
like this:

field1 field2 field3 field 4
1 AA BB 1
1 AB BC 2
1 AC BS 3
2 AA BC 1
2 AN BS 2
3 DD EE 1
3 EE FF 2

A fourth column is added showing a list of consequtive numbers
starting over again when in field1 a new value is found.

I hope anyone can help me!

Thanx in advance,
erik

Sep 19 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Wed, 19 Sep 2007 07:48:11 -0000, de**********@gmail.com wrote:

Assuming that's done, tell us what you really want to do. What should
the end result be? Some report? Some exported data?
Often people ask for advice about some step of a whole process, but in
the big picture that step is not needed. Give us the big picture.

-Tom.

>hello,

I would like to know if there is a way in ms access 2003 to add
consequtive numbers to a new column to 'count' the records. This count
should start over by every by variable. for example:

I have a table:

field1 field2 field3
1 AA BB
1 AB BC
1 AC BS
2 AA BC
2 AN BS
3 DD EE
3 EE FF

In the final situation a new table or query should be created looking
like this:

field1 field2 field3 field 4
1 AA BB 1
1 AB BC 2
1 AC BS 3
2 AA BC 1
2 AN BS 2
3 DD EE 1
3 EE FF 2

A fourth column is added showing a list of consequtive numbers
starting over again when in field1 a new value is found.

I hope anyone can help me!

Thanx in advance,
erik
Sep 19 '07 #2

P: n/a
On Sep 19, 8:51 am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Wed, 19 Sep 2007 07:48:11 -0000, dekker.er...@gmail.com wrote:

Assuming that's done, tell us what you really want to do. What should
the end result be? Some report? Some exported data?
Often people ask for advice about some step of a whole process, but in
the big picture that step is not needed. Give us the big picture.

-Tom.
hello,
I would like to know if there is a way in ms access 2003 to add
consequtive numbers to a new column to 'count' the records. This count
should start over by every by variable. for example:
I have a table:
field1 field2 field3
1 AA BB
1 AB BC
1 AC BS
2 AA BC
2 AN BS
3 DD EE
3 EE FF
In the final situation a new table or query should be created looking
like this:
field1 field2 field3 field 4
1 AA BB 1
1 AB BC 2
1 AC BS 3
2 AA BC 1
2 AN BS 2
3 DD EE 1
3 EE FF 2
A fourth column is added showing a list of consequtive numbers
starting over again when in field1 a new value is found.
I hope anyone can help me!
Thanx in advance,
erik- Hide quoted text -

- Show quoted text -
Access does have an autonumber field that can be added to any table.
It is not guaranteed to be consequetive, as someone could delete one
of the records. DCOUNT( ) is a simple procedure that counts records
matching the supplied filter, if counting is all that you want.

Sep 19 '07 #3

P: n/a
de**********@gmail.com wrote:
I would like to know if there is a way in ms access 2003 to add
consequtive numbers to a new column to 'count' the records. This count
should start over by every by variable. for example:

I have a table:

field1 field2 field3
1 AA BB
1 AB BC
1 AC BS
2 AA BC
2 AN BS
3 DD EE
3 EE FF

In the final situation a new table or query should be created looking
like this:

field1 field2 field3 field 4
1 AA BB 1
1 AB BC 2
1 AC BS 3
2 AA BC 1
2 AN BS 2
3 DD EE 1
3 EE FF 2

A fourth column is added showing a list of consequtive numbers
starting over again when in field1 a new value is found.
You'd typically do this in a query - the numbering is then re-evaluated
each time the query runs so your numbering will always reflect changes.

You want to count the number of records that are "less than or equal" to
the current unique value:

SELECT field1
, field2
, field3
, (SELECT COUNT(*) FROM yourTable WHERE Field1 = y.field1 and (field2 &
field3) =< (y.field2 & y.field3) As seqCount
FROM yourTable As y

It's late; this is *air* code so proceed with caution...

It assumes that field2 and field3 are *allways* 2 character length and
that the Access alphanumeric sorting method is OK for you.
--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft Access MVP
'--------------------------
Sep 20 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.