Enter data in mother table using data from child tables

Question

Hi all,
I have 3 tables in an access 2010 database:

1. Crew: CrewID; Name; Adres;...
2. Voyage: VoyageId; Voyage name; Departure harbour; Arrival harbour
3. Crewlist: CrewlistId, VoaygeId, CrewId, Rank

The VoaygeId and CrewId from the Crewlist table are linked (relation) to the autonumber ID's from tables 2 and 1.

My first and main question is: Upon boarding everyone has to ‘sign in’ selecting the voyage and there name, and assign them a roll (of to be donde by the responsible officer). How can I make a form that lets the users browse through the voyagenames and crewnames in stead of the ID’s uses in the ‘mother’ table (table 3: Crewlist)

2nd question: how can I make sure that someone isn’t enrolled twice for the same voyage (adding same voyagenumber and same crewId number in crewlist). This would preferably be blocked upon trying to add the same person a second time on a voyage.

Answer 1

To prevent duplicates in Crewlist, add a unique index to the table on both CrewId and VoyageId

It would be a good idea to add relationships and enforce referential integrity

You are now in a position to use the wizards to create a form based on Voyage and a subform based on CrewList with a combobox based on Crew

There are a number of refinements you could add.

Make sure you do not use reserved words like Name and do not put spaces in field names. You will thank yourself later.

See also create form to add records in multiple tables