[AccessD] Multiple Tables

I don't see the usefulness of tblMain. I would model this:

tbl_Manufacturer (PK ManufacturerID, ...)
tbl_Model (PK ModelID, FK ManufacturerID, FK TypeID, ...)
tbl_Type (PK TypeID, ...)

As for UI, there are dozens of ways you could go. 

You could present a main form with a subform for models. Code a double-click in the subform to invoke a data-entry style form to make it easier to update.

You could create a pair of listboxes on the main form, one for Manufacturer and the other for Model. A single-click on the Manufacturer list refreshes the Model list. A double-click on either invokes the data-entry form.

Finally, I think the sample data you presented is inside out. You ought to begin with the manufacturer, then look at the models, and finally the type. (Admittedly, it might go Type first and then Model. I can see that, in some situations. Ford makes lots of trucks, for example, so the Type be be higher in the order of things than the Model.)

hth,
 
Arthur Fuller
Technical Writer, Data Modeler, SQL Sensei
Artful Databases Organization
www.artfulsoftware.com




----- Original Message ----
From: "Hollis, Virginia" <hollisvj at pgdp.usec.com>
To: accessD at databaseadvisors.com
Sent: Monday, February 19, 2007 11:11:36 AM
Subject: [AccessD] Multiple Tables


I always have trouble with this. I have to set up a form for updating 3
different tables, Type, Model, and Manufacturer.



tbl_Manufacturer

tbl_Model

tbl_type

tbl_Main (this would be a table that contains all the information) Would
I put ModelID, TypeID, etc into this table?



So do I create another table that joins them together? At first I put
the fields all as ModelID, TypeID in the main table & added each field
to the main form & when they selected the Model I used a Select query to
update the matching Type & Manuf.



I need to create a form where the user can update the fields across for
each Model & complete the Type and Manuf. If they are 3 separate tables
how do they join together? How do they join to the main table?



Ex: 

Model: F, Type: Truck, Manuf: Ford

Model: Silverado, Type: Truck, Manuf: Chevy



Virginia







-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com