Autonumber Problem

[DaGeeza]

OK guys, I have a table in access like this

ClientID - Autonumber
ClientName - Text

Also I have a table like this

SportID - Autonumber
SportName - Text

And lastly, a table like this

ClientID - Number
SportID - Number

OK, I shouldnt have to explain that any more because its just a simple 2 tables design with a linking table.... anyways my problem is that I have a separate table in a different database like this

ClientID - Number
SportID - Number
ClientName - Text
SportName - Text

Now the data in this database I need to move over into the first database, the problem I have is that when I try to copy and paste append I cannot paste the ClientID because it upsets the autonumber - I can paste the data if I change the datatype from autonumber to number, but then it wont allow me to change the datatype back to autonumber after I have populated the tables.

In the second database the ClientID and the SportID are the same value and are basically the first record's value is 5000 and the last value is 6875.

I do appologies if the description is a little sketchy but I have had several pints and several whiskey's and everything is starting to look a little blurry.

Would be greatful if anyone has any ideas.

Oh yeah BTW - this isnt the actual table design, but if you answer the question then I will be able to implement it into my rather larger proper design. Cheers in advanced.

[RobDog888]

Which table are you trying to paste the data into that causes the ClientID to error?
You can only have one autonumber field in a single table.Do you have any restrictions on the ClientID field like Indexed (No duplicates) etc.

[DaGeeza]

Sorry wasnt too clear - If you open up access and create a table like this

ClientID - AutoNumber
ClientName - Text

and another like this

TestID - Number
TestName - Text

now enter data so Test table looks like this

TestID|TestName
5000|Fred
5004|Jim
5020|Paul
5026|James

I want to put that into the first table but when I copy and past I get this

ClientID|ClientName
1|Fred
2|Jim
3|Paul
4|James

I need the autonumber to remain the original 5000 odd number so that the linking table links to the correct data.

[RobDog888]

Then the ClientID should be a Numberic type because you want to numbering to follow the TestID values instead of creating new number values.

[DaGeeza]

So what do I do about creating a new Client? how should I give it a new ClientID?

[RobDog888]

Looking at your schema I think we would need to rethink it a bit.

Since the Client is the initial object that is where the id should be autonumbering from.

I dont think you can have a Test without a Client so the test would have its own ID.

How can a Client and Test be the same name?

[mikee_phil]

create a query

SELECT Table1.*, Table1.mike+5000 as client_id
FROM Table1;

from the query you can relate the client_id to the test_id

[RobDog888]

Probably have a design like so.

Clients 
---------------
ClientID *
ClientName 

Tests
---------------
TestID *
ClientID
TestName

Then you can have each field with the asterisk as an Autonumber field type. The ClientID fiels will be your fields to JOIN on.