create table based on existing data

[LVL]

Hello,
Hope I am in the correct forum!
Problem:
I have an access table containing +- 3000 records (call it table 1).
One of the fields (call it ID) contains data (short text) formatted as follows: xx-yy-zzz or xx-yy-zzzz where xx, yy and zzz(z) are figures.


I want to recreate a table (table 2) where field 1 (call it Ident) is formatted as autonumber [with format xx-yy-zzz(z)] and then fill this table in the way that the data of table 1 are transferred to table 2 with ID = Ident.
In other words: in table 2, if Ident is 01-01-001 then the record should contain the data of the record in table 1 where ID is 01-01-001

To be complete: in table 1, not all ID figures are used: example: 01-01-002, next is 01-01-004 (no 01-01-003!).

I am NOT a whizzkid, so if possible, step-by-step would be appreciated!!

thanks ever so much for help!!

LVL

[ranman256]

make your sub table (table2)
then make an append query to add data from table1 to table2

[LVL]

Ranman 256,

thanks for the reply.
How will I define the query?
Taking into account that autonumber is just that autonumber and that not all numbers are present in table 1...

thanks,
LVL

[orange]

Lvl,

See this re Autonumbers.
You will also find that relational databases work very well when using an old principle
--one fact in one field.

Good luck with your project.

[LVL]

Hello Orange,

thank you very much for this clear and enlightening answer! Point taken

Then, let me take you to the origin of the problem:
In fact, I am talking about a database with info related to "ID numbers" (the famous xx-yy-zzz(z) )
Up to now, the info comes in via Excel worksheet that has a macro to assign new ID numbers that are imported into the database afterwards.
The Excel macro uses info from the database to run ("highest ID number used").

Where things get annoying is here:
when importing the new ID numbers and their info into the database, a check is done to see if the info (the person) already exists in the database. If this is the case, the info is NOT imported.
However, this means I "loose" ID numbers in the series.
Example:
import from excel:
01-01-010 info1 info2 info3
01-01-011 info1 info2 info3
01-02-012 info1 info2 info3

when adding to table via query, it seems that the person given ID 01-01-011 by the Excel macro already exists in the database under 01-01-007 and will therefore not be imported.
I am looking for a way to avoid the loss of ID's as the info comes in via Excel sheet and the ID numbers need to be attributed and are exclusive...

sorry for the complications...

LVL