autonumber in relationship database

[Katharine Jansen]

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

 

[Roger Carlson]

You can't create a relationship between two autonumber fields in two tables.
In terms of a One-To-Many relationship, only the primary key of the One-side
table should be an autonumber. The related field (foreign key) of the
Many-side table should be Long Integer.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[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

 

[Katharine Jansen]

I've changed it and it works.

Thanks Roger
Katharine