Many to Many Forms - Problems

[MichaelSmith]

Hi!

I'm working on a database for an archaeology project which is looking for the shipwrecks of transatlantic slavers between 1600-1880 around the world. We're working on a massive archival trawl of data at the moment and I've been tasked with building the database.

I've built very simple databases (flat form) with access before but since this project is quite large I've had to switch to a relational design and I'm encountering some headaches with the data

Below is an image of the relationships I have set up at the moment:

h t t p : / / i.picyou.com/g7eNLL.jpg

To explain how my database works:

Each Ship has it's own record which assigns it's primary key and is used to store the name of the ship. Each ship could have made multiple voyages and is linked by a one to many relationship to the voyages table. On each voyage there may have been up to 3 ships captains who either died or left the voyage at some point and each captain has the possibility of having been on many voyages.

I'm trying to create a form that can be used to update all of the tables at once.

I've started with a main form based off the Ships table and inserted a Voyages subtable which works absolutely perfectly, if I enter a ship into the main table I can then add all of the voyages it took part in and this updates the voyages table.

Where I'm encountering problems is the many to many relationship between the voyages and the captains. I can't seem to find a way of making one form where I can enter the names of the captains who took part in the voyage or select them from a list if they're already in the captains table. Anyone able to advise me on how I'd go about doing this?

Thanks!

Mike

PS: If you need more details please let me know and I'll do my best to update ASAP.

PPS: Sorry about the link format, I'm sure my post count will be above 10 fairly shortly as I'm banging my head against a wall with this but for now you'll have to manually edit the image link to be able to see it.

 

[jdraw]

A few points:

Don't have embedded spaces in your field or table names. Use only alphanumeric and "_". Embedded spaces or special characters will cause you headaches (and syntax errors).

Get your tables identified, nomalized and related to match your business rules. Forms are really for displaying or entering data into tables. Forms are based on tables or queries - so getting the tables right is critical.

Having one form to update all tables is not realistic in my view.

When you have a many to many set up, you resolve the issue by making a junction table.
Here is a link to junction table.

You can get by the post count requirement by using a zip file.

 

[CJ_London]

And a point about form design, have one form per table and use subforms to 'link' the data.

So for example your main form would be the ships table. On this form have two subforms, one for voyages and one for captains.

Providing your voyages and captains tables have populated DateFrom and DateTo fields and you have your relationships set correctly you can write a query which will show who were captain(s) for any voyage.

I've attached a demo db to demonstrate

 

[MichaelSmith]

Thank you both for your answers!

A few quick questions if you don't mind.

CJ_London - I took a quick look at your example database (thanks for that!) and definitely see what you're pointing at but I have one issue. A captain may (on extremely rare occasions) have been captain of two ships.

For example I have a ship called the Enterprize captained by Charles Wilson which sank off the Bahamas in 1795, the crew survived. He later went on to captain a replacement ship, also called the Enterprize, on a number of voyages (1797 & 1799). The second Enterprize also sank but under a different captain in 1803.

Any way to get around that issue?

jdraw - Thanks for your tips, I'll go through now and correct the fieldnames etc. I'll keep in mind the point you mentioned about multiple forms!

 

[CJ_London]

In the example, you could just post the captains name again for a different ship, however the better way would be to rename tblCaptains to say tblCommissions and remove the CaptainName field as below and have a separate table for captains:

tblCaptains
CaptainID auotnumber PK
CaptainName Text

tblCommissions
CommissionID autonumber PK
CaptainID Long FK
ShipID Long FK
CaptainFrom Date
CaptainTo Date

Then in your currently called frmCaptains, remove the captain name field, unhide the CaptainID field and change it to a combobox with a recordsource of tblCaptains - set the column widths to 0,3cm, columncount to 2 and bound column to 1

Edit - you'll also need to modify the summary form recordsource