Relatively basic design help please

[leezer3]

OK, so I've managed to give myself a nice headache and nothing much else

What I'm trying to do is create a simple database tracking a model railway collection. From this, I have four basic item types (Loco, wagon, coach, other), for each of which I've created a table.
I've also created a secondary lookup table to hold manufacturer details, which works as intended.

Where I'm falling down is in creating an overall index, and from there a cascading form for data entry. I was hoping (Assuming?) that I could create a 'root' table, holding a unique ID for each model, and from there create a 1-1 relationship between these and the secondary tables, but I can't get that working right

My current structure looks like this:
Table: Stock Records
StockID (Autonumber, primary key)
StockType

Table: Locomotives
StockID (Primary key)
Various secondary data fields, these work.....

Table: Coaches
StockID (Primary key)
Various secondary data fields, these work.....

Table: Freight
StockID (Primary key)
Various secondary data fields, these work.....

Table: Other
StockID (Primary key)
Various secondary data fields, these work.....

Basically then, I'm hoping to create a form that cascades so when I select the stock type, it'll automatically cascade and create an entry in the correct table.
I've defined 1-1 relationships between all instances of StockID, but I'm confused and stuck
(I can create forms or enter data into any one table at the moment, but not cascade between them)

 

[plog]

The ancillary tables should only contain the StockID plus any attributes that are unique to that StockType

What Pat said about his is very important. Does each item type table (Locomotives, Coaches, etc.) contain unique attributes to that type? Can you post the fields of those 4 tables? If they all have the same fields, you wouldn't need 4 tables, you would need only 1.

 

[leezer3]

What Pat said about his is very important. Does each item type table (Locomotives, Coaches, etc.) contain unique attributes to that type? Can you post the fields of those 4 tables? If they all have the same fields, you wouldn't need 4 tables, you would need only 1.

Some of the fields are unique, some aren't
Fields list:
Common:
StockID
Number
Livery
Weathered
Boxed
Coupler type
Notes
Catalog Number
Purchase Price
Model Value
Location
Image (Attachment)

Locomotives:
Locomotive type
Class
SubClass
Tractive Effort
Designer
Route Restriction
Weight

Coaching Stock:
Type
Designer
Passenger Capacity
Rake

Freight Stock:
Tare weight
Brake type
Wheel type
Intended Load
Removable Load Location

Other:
Powered
Weight
Restrictions

Basically, I've got a lot of unique fields for locomotives, and a few unique fields for everything else

Doing it with a single common table, and attributes for each single item was actually an initial thought, but I couldn't make this work correctly either. The attempt I made there was to add an autonumber primary key field to each of these subsidiary tables, but I couldn't figure out where/ how to link that to, so I shelved the idea.

In essence, how do I tell that a record is a locomotive & from there link the two together? I'm not well up on VB scripting, but are you suggesting adding something like this:
Add a new lookup wizard field to select the common choices, and in the form properties key an afterupdate event to trigger when this is changed.
Use the afterupdate to copy the autonumber StockID across into the appropriate table, and then use this to key back & retrive the data later?