Adding Field to a table from a form

[Chimp8471]

i am in the process of putting together a database for a friend, he has given me a rough spec for the program but he has thrown up an interesting point.

in my DB i have the following tables

tblClassID - (this is to keep a log of the various type of class boat that they fall in to)

ClassID-(PK)
Class
Class_Description

tbl_RadarInfo - (This stores a list of various radars that are available and brief description)

RadarID-(PK)
Radar_Name
Radar_Description

Tbl_MainData - this is where all the main info will go.

ID-(PK)
Boat Name
Class - looks to the class table for it's info
Length
Radar - looks to the radar table for it's info

the problem starts when boats can have more than i radar, what i want to do is to be able to offer my friend the option to add in another radar if required.

so in the form it would have all the boats info, a list of the current radars and an option to add another, then in future another..

could anyone suggest the best way for me to go about this please.

I know it's a little long winded but i have tried to explain it best i can.

Andy

 

[llkhoutx]

You have stumbled onto a one to many relationship. One boat, many radars.

Create a juntion table betwen the boat table and the radar table, e.g.

tbl_BoatRadar
ID (Boat primary key)
RadarID (Radar primary key

In this table, ID and RadarID can be duplicated. e.g.

ID RadarID
1 1 - two radar's for boat 1
1 2
2 1 - one radar for boat 2
3 1 - 3 radar's for boat 3
3 4
3 5

Radar's are shown as a subform on a boat form. You'll have to use VBA to build the junction table as radars are added to a particular boat.

 

[Mile-O]

You have stumbled onto a one to many relationship. One boat, many radars.

Many-to-many.

One radar type can be fitted to many boats;
One boat can have many radar types.

 

[Chimp8471]

see now i am just getting confused.....but yeah.

so i should have

1 table with the radarinfo
1 table with the boat info

and this new table......which is gonna be a many to many joined table ???

 

[Mile-O]

Yes, the new table, call it: tblRadarsToBoats


It's fields will be:

RadarID
BoatID

these are both foreign keys, one to the radar table primary key, one to the boat primary key.

Highlight both fields in the design view and right click, select hem both as the primary key of this table.

 

[Chimp8471]

ok will try to put something together and see what i come out with....

If i get any problems do ya mind if i upload my db to have alook at....


Andy

 

[Mile-O]

No probs.

Here's an example of a many-to-many: click here