autonumber in relationship database

[Guest]

I work for a small newly established company and am setting up a database in
Access on Office 2000 Premium to store client information and account
information. I have set up the system to create an autonumber for each new
entry, but the database needs to be relational so that I can recall data
about customer accounts from various tables/forms, you know the drill. My
stumbling block is the autonumber function in the relational database. I have
designed the database with four files; client details, course details,
uncleared payments and closed accounts. The client details file has been
created with the client reference as the primary key and with an autonumber
function. My problem is when creating a relationship between the client
details and course details files it has somehow changed the number of the
client references, so the clients have different refernces in the course
details file than they do in the client details file. The client reference is
an autonumber in both the client details file and the course details file. I
don't want to pursue with the database when it fails to work competently at
this stage. Can anyone shed some light on what I appear to have done wrong??

Thanks in advance
Natasha

 

[peregenem]

You can't create a relationship between two autonumber fields in two tables.

CREATE TABLE Test1
(key_col INTEGER IDENTITY(1,1) NOT NULL UNIQUE);

CREATE TABLE Test2
(key_col INTEGER IDENTITY(1,1) NOT NULL
REFERENCES Test1 (key_col));

INSERT INTO Test1 VALUES (1);

INSERT INTO Test1 VALUES (1);
--success

INSERT INTO Test1 VALUES (2);
-- fails, FK constraint bites

 

[Guest]

I've changed it and it works.

Thanks Roger
Katharine