CREATE TABLE alumni (
student int NOT NULL PRIMARY KEY AUTOINCREMENT,
name varchar NOT NULL,
middlename varchar NOT NULL DEFAULT 'unknown',
surname varchar NOT NULL DEFAULT 'unknown',
aliases varchar,
UNIQUE (name, middlename, surname)
);
CREATE TABLE bank (
receipt int NOT NULL PRIMARY KEY AUTOINCREMENT,
student int NOT NULL REFERENCES alumni (student) ON UPDATE CASCADE,
details varchar
);
Notes:
That's just an example and may not work in MS Access exactly as written, but that would be one way to do it. Basically, what we're doing is making an ID surrogate key called "student". This key has no relation to the actual data. But, we need to maintain some kind of data integrity. We give default values to middlename and surname so that we can enforce a UNIQUE constraint on the triplet. I used "bank" as a table name, because I have no idea what the receipts are actually for and just assumed that they were about money transfers. That table should reference the purpose of the receipts. The LEFT JOIN in the below SELECT example is because there might be students that do not have receipts. Then you select out of that table by whatever column you want after the join. In my example, it would be...
SELECT a.name, a.middlename, a.surname FROM alumni a
LEFT JOIN bank b ON a.student = b.student
WHERE
;
There's probably a way to do it through the MS Access GUI, but this one is best done using SQL, which I'm relatively certain you can do in Access. If you can't, just assign the surrogate keys to both tables and have the other one reference it. Yuck without the UNIQUE constraint, but it would work. Without the unique, you can have update and delete anomolies with a key that has nothing to do with the data.
Here's without the UNIQUE...
INSERT INTO student VALUES ('Barnie');
INSERT INTO student VALUES ('Barnie');
Now, I've got two Barnies and you lost information by adding it. You don't know if there are actually two Barnies or someone made a data entry error.
Edit... One last thing. Joe Celko, a rather smart database guy that writes books about it, says that sometimes it's easier to go with an ID that is confirmed by another, more substantial database, like a social security number for the person or some other national or international ID number. In that case, you wouldn't have to use the silly AUTOINCREMENT for the alumni table.