Unique problem with Numbering Records 1

[mellay]

Hi! I am developing a data base that will need each record numbered, but will require each record number to change when tables are merged. I am working with a total of approximately 7000 case records per year. Each month I will receive aproximately 5 or 6 databases to merge into one database with approximately 100 to 150 records per database. At the end of the year, I must merge all databases into one database for end of the year reporting. I am looking for a way to have the numbers of each record change when databases are merged. For Example: 1st database will be numbered 1 to 100 and the second database would be numbered 1 to 150. I want the numbers to change, when merged, to read 1 through 250. My numbering system will need to be part of the underlying table, in order to sort ascending. I have already developed such a database under an old DOS program in WORKS 3 for DOS, which works fine. The auto number will not work, as my numbering system must change with each record when tables are merged. I do NOT need a number to be permanently assigned for each record. Thanks so much for any help you may give!! I really enjoy working with ACCESS and really enjoy this forum!! mellay

 

[jfhewitt]

Can't you just delete the autonumber field after remerging, and create a new autonumber field for the merged databases?

 

[mellay]

Thanks jfhewitt for your prompt response. I am a new user and I found that I could not merge tables with the autonumber. Perhaps I was doing something wrong. Do you know any other way or perhaps tell me why I could not merge with the autonumber?

Also, when people in my unit record data in records, they routinely delete records before submitting the final database to me. The autonumber retains the original number for each record. I need a system that will allow numbers to change on each record after records are deleted. I work in state government, each reviewer will send reports to various agencies prior to sending the database to me for merging. Reports will end up with missing numbers for records, for example, 1, 2, 4, 5, 6 etc. I realize that Access is a relational database and numbering in this manner is not recommended, but this is the problem I am facing.

Thank you for your help! mellay

 

[tempclerk]

I don't understand, Why do you need your records numbered?

 

[UncleJack]

It sounds as if you want to assign new numbers as data is input to you. That is, there's nothing sacred about the numbers you receive; you just want to put them in one table in a contiguous, ascending order.

Write a routine that creates a table then use a data definition query to index a primary key as autonumber. (Or manually create such a table) Then append the records from table1 (Being the one that you already have) to the new table. Next append the records from table2. Now delete the other two tables.

You end up with a single table with contiguous records. You can change the autonumber field to number if you want, but you can't change back to autonumber if you do. (I recommend you use autonumber for appending only then change to number.)

When you append records to an autonumber table, omit the reference numbers in the append query. Access will automatically add the numbers as the records are appended.

Uncle Jack

 

[mellay]

Thank you for your prompt response! I am sorry I am late in responding, had a long week-end!! I will try your method this week! Thanks again, I really appreciate it! mellay