How to make a 1-1 relation in Access?

[gerard1979]

Maybe this is a very simple one, but I never did this before.
I have 2 tables
Table1 with PrimairyKey = MP_id (autonumber)
Table2 with PrimairyKey = leadnumber (autonumber)
MP_id is as foreignkey in table2. The relation between the tables must be 1-1 or better 1-0,1. How to realize this?

Thanx in advance,
Gerard

 

[danvlas]

One-to-One relationship between two tables: unique index from table1 joined with unique index from table2.

I really don't understand 1-0,1? Is that a record from table 1 should match only one tenth of a record from table2?

Dan

 

[gerard1979]

one to zero-one relation maybe is the same as 1-1?
OK, so I only need one primairy key for 2 tables?

Thnx a lot,

Gerard

 

[danvlas]

Not necessarily primary key. A unique index will do. But can be done with primary keys joined together as well.

Dan

 

[GHolden]

Sorry to butt in....

If you have a true one - one relationship, ie. there is only ever one item from table A that links to one item in table B, why have you got two tables?

There are two ways to write error-free programs; only the third one works.

 

[danvlas]

No problem.
Normally, you have all fields in one table.
However...
I know two instances when you have to use two tables:
1. the total number of fields exceeds 255 (cases are rare, but not impossible)
2. when you want to simulate column level security (actually you set different access rights on each table)

Probably there are some other cases, but I haven't encountered any so far...

Regards,

Dan

 

[GHolden]

Good points Dan, I was aware of point 1 but have never considered point 2.

I was just concerned that Gerard may be overcomplicating his db without realising it.

There are two ways to write error-free programs; only the third one works.