How do you populate a number field with an incrementing number? (1 Viewer)

AccNovice · Aug 15, 2008

In an existing access database there is a macro with an action = TranferText that import a file into table A.
The last field on Table A is RECCOUNT (Number).
This field is not in the import file.
Is there a way that you can use the Default Value to populate this field with an incrementing number that always starts with 1 and increment for each record imported?
I cannot make this field an Autonumber because there is already such a field in Table A.

namliam · Aug 15, 2008

You can easily have multiple autonumber fields in one table... Tho why would you want to have 2 such fields?

Have a look at this thread...
http://www.access-programmers.co.uk/forums/showthread.php?t=143842&highlight=Autonumber

that should help you right along.

AccNovice · Aug 15, 2008

That is strange because when I tried to create a second AutoNumber field on the table I got an error saying "Microsoft Office Access allows only one autonumber field per table"

To answer your question of why:
Table A currently contain 395615 records. When I import the next 10420 records it accepted 10366 records and rejected 54 records saying the key (The current autonumber) is not unique. I removed all the records out of Table A and "Compact & Repair Database" to make sure that the autonumber starts from 1. I loaded the 395615 records again with the autonumber starting at 1. Once more when I loaded the next 10420 records it rejected 54 again for the same reason.

I then created Table B (that looks exactly like Table A) and imported the 10420 records into this table and all the records was accepted. I expected the same 54 records to be rejected.

Between you and me I am not really sure what my next step is going to be but out of ignorance I thought that if I add a field with a record count on both Table A and Table B I would be able to identify the 54 records that are rejected.

DCrake · Aug 15, 2008

Simple Software Solutions

To find out which records are duplicated create a new query grouped by the offending field and add another column cnt:1 and count this column. Sort the cnt column descending and enter criteria >1.

This will list all offending records that have a count value greater than 1 and the number of repeat offences encountered.

CodeMaster:

Pat Hartman · Aug 15, 2008

To clarify the autonumber business - Only one autonumber is allowed per table. If you want an additional sequential number, you would need to code it yourself.

namliam · Aug 15, 2008

Hmz,

Again to much oracle on the brain... Guess I should get less involved with these big ugly databases and get back down to Access

How do you populate a number field with an incrementing number? (1 Viewer)

AccNovice

Registered User.

namliam

The Mailman - AWF VIP

AccNovice

Registered User.

DCrake

Remembered

Pat Hartman

Super Moderator

namliam

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread