Imported data has different ID

[cheyanne]

I have two tables named tblClients and tblPets which I imported from another database.



When I assigned it an autonumerical field ClientRef it had 1565 records on import.
However the imported file also had a field ClientID, the original "ClientRef" field which had 1629 records.
Clearly several records have been deleted over time.

The problem that I have is that the linked ClientRef field, in tblPets table, has records which no
longer match with the newly created Client ID from the imported table.

When I deleted the ClientRef autonumber field, and used the ClientID field as an indexed field,
the records matched, but it no longer generated autonumbers, which is essential.

How can I remedy this situation so that the numbers in ClientID become the indexed field but
also generates an automatic number starting at 1566 as the next record?

Any advice would be greatly appreciated.

Regards

Cheyanne

[Missinglinq]

This site gives a method of setting an Autonumber to a specified initital number. I think you can probably modify it to work for you:

http://www.mvps.org/access/tables/tbl0005.htm

Linq ;0)>

[June7]

Once table has records, can't change a field to an autonumber type. You can create another autonumber field in tblClients and let it number the records. If you are lucky the autonumber sequence will be the same as the existing ClientID and can just remove the original and rename the new (relationships, if established, will have to be removed and reset). If not, create another field in tblPets and update it to the new autonumber ID in an UPDATE query that joins the tables on the orginal pk/fk. After update, remove the relationship between the original pk/fk fields, delete old fields, rename new, and establish new relationship.

[cheyanne]

Hi

Thanks for your collective efforts to date but the problem is more complex than I first thought.

I have attached a screenshot, of a part of a database view of the Client file, showing where the problem lies.

The two fields in question are named ID and ClientRef (this is the old IDRef from an imported Excel file which holds
Client data from the old database.)

Field ID is an autonumber field which shows 1565 records as opposed to the 1629 records in the ClientRef field.

Over time users have clearly deleted some records, in the old database, and in reality it now contains 1565 Client records.

If I needed only a Client File I could simply delete ClientRef and rename ID as ClientRef. However as this is a
relational database the ClientRef numbers, in this database, also link to their pets in a table named tblPets.

The consequences are that if I use field ID, as the Primary key, the pets are not linked to the correct Client.
However, on the plus side the files auto increment. If I delete the ID field and use the Client Ref field, as the
Primary key, then I suspect that the pets may be linked to the correct owner, but the field will not autoincrement.

What I need to do is import all those 1565 records into a Client file (accepting that there will be around 64 "empty" records)
Have them retain their old ClientRef i.e. the last record would be 1629 (with and somehow have this ClientRef field as
an autonumber field which starts at 1630.

Apologies if my original post was not explained as well as it could have been.

I suspect that I shall have a similar problem in tblPets as well.

As before any suggestions, or assistance will be gratefully received.

Regards

Cheyanne

[recyan]

Have you tried joining the new Clients Table ( with the new ClientID) with the Pets table with the join on ClientsRef (old), in a make table query & then try using the new table as the Pets table with the new ID (ClientID) in it as ClientsRef.

Thanks

[cheyanne]

Hi Recyan

The short answer is no I haven't, but in honesty would not know how to do it and,
more importantly do not understand what such a technique would do.

Is there any chance you could outline exactly, and in detail what you think I need to do?

Regards

Cheyanne

[recyan]

Just see if below gives some guidelines :

Let us say, the original Clients Table imported in to access
tblClients
ClientRef - This is unique & theoretically the original PK
.....

After creating an Autonumber ID Field
tblClients
NewClientRef - PK - The Autonumber Field
ClientRef - This is unique
.....


The Pets Table
tblPets
PetID - PK
ClientRef
.....

Make a Make Table query :

SELECT 
    tblClients.NewClientRef, 
    tblClients.ClientRef, 
    tblPets.ClientRef, 
    tblPets.PetID,
    .......    
INTO 
    tblClientsPets
FROM 
    tblClients 
    INNER JOIN 
    tblPets 
    ON 
    tblClients.ClientRef = tblPets.ClientRef;

Now perhaps, if everything is OK, you should be able to have a new one-many relationship between tblClients.NewClientRef and the New table tblClientsPets.NewClientRef.

Discard the old table tblPets.

Perhaps, there could be a simpler way.

Thanks

[TG_W]

Just a quick suggestion (since this was posted in the "Forms" section), if you create new records from a form, why not set the default value for ClientRef to be =DMax("ClientRef","tblClients")+1 in the form? This should allow you to continue to "autonumber" the ClientRef field picking up where the old database left off.

[cheyanne]

Hi Recyan

Have tried as you suggested but I got an error message saying that the resultant table
cannot have more than one autonumeric field.

Stuck! but hey many thanks for the time you put into the reply - much appreciated.

Regards

Cheyanne

[cheyanne]

Hi

Many thanks for your suggestion which was much appreciated. I shall give that a go.

Regards

Cheyanne

[June7]

A MAKE TABLE should not result in two autonumber fields. You don't need to copy the old ClientRef fields into the new table.

There is also the method I suggested in post 3 but Recyan's might be better.

[cheyanne]

Hi again

I have just looked at your code again and wonder if I should have included all the other fields in the tblPets table
under this

tblClients.NewClientRef,
tblClients.ClientRef,
tblPets.ClientRef,
tblPets.PetID,

I have ended up with a table with just these four fields.

Regards

Cheyanne

[June7]

Yes, Recyan's suggestion was an abbreviated example. Include all fields you want to replicate in the new table. Try with the wildcard:

SELECT
tblClients.NewClientRef,
tblPets.*
FROM
...

[cheyanne]

Hi June 7

Many thanks for your comment. I have now done that and the table has been made.
The ClientRef field is now in synchronisation with the tblPets and the Pets now seem
to link to their reepective owners.

The only problem remaining is that of the inserting a new record. I need this to start at
1630 and do not think I can introduce an autonumber field to a table that already has records in it.

Off to consult the "books" again!

My original version of this database functions perfectly but that was when I had introduced all the records. I do not relish having to introduce
all the records that existed in their original database - hence my importing them. I am sure that I am not the first person to do this,
but importing records seems to create more problems that it solves.

Regards

Cheyanne

[recyan]

The only problem remaining is that of the inserting a new record. I need this to start at
1630

Are you telling that the next autonumber NewClientRefID should start at 1630.
If yes,
Why ?

If the numbers in your first post is still relevant, then you have 1565 records.
Hence the next autonumber ID for the NewClientRef would be 1566 ideally ( though not necessarily).
Your old ClientRef is no longer relevant in the new tables.

Thanks