Defining Tables & Relationships

[Greg2582]

I'm trying to design a Access database from a Spreadsheet. An example of it
is below. It's frozen at Column B and at Row 2. It can go on indefinite (As
Far as Excel will go) in both directions, depending upon how much data is
found. Column A contains the Company # for the Train Sets and the MCZ02,
etc. beginning in Column C are the engine model #. Where they interset is the
part number(A301, A302, etc.) for a wheel change. I'm trying a create a
database that allows me to query it and list any changes in part numbers for
a Company #/Engine Number, without havng to look at a huge spreadsheet.
1 A B C D E
F
2 Mode Style MCZ01 MCZ0 2 MCZ03 MCZ04
3 57111 00 A301 A301 A301 A302
4 57112 00 A301 A301 A301 A302
5 57121 00 A301 A301 A301 A302
6 57122 00 A301 A301 A301 A302
7 57131 00 A304 A304 A304 A303
8 57132 00 A301 A301 A301 A305
9 57133 00 A302 A304 A304 A305
10 57141 00 A301 A301 A301 A301
11 57142 00 A301 A302 A301 A304

For each of the Company #(57112, 57121, etc.) you have several Model #
(MCZ01, etc.) and vice versa. So you can have 500 Company #'s and 550 Model
#'s. Where the Company # and Model # intersect is a part # and they can for
any Model # or Company # be all the same or all different.

 

[mscertified]

For any database design you first decide what objects you need, then you
create a table for each object. You then relate the objects to one another by
defining table relationships. In your case it would seem that you need tables
for companies, train sets, parts. A company can have many train sets and a
train set can have many parts. If different train sets can have the same
part, you'll need a train set part table which links the trainsets and parts
tables. Each table can of course have an unlimited (within Access limits)
number of records.

-Dorian

 

[Greg2582]

I'm going with three tables. I have the Company table:.
Model
Style
I have the Engine Table:
EngineModel (MCZ01, etc.) and
I have the Parts Table
Model
Style
EngineModel
PartNumber

I guess my problem is the data. In the Parts Table would I list the Model#
ever time I introduce a new engine#. For instance, if I have 20 engines,
would I have Model # 57111 listed 20 times?

 

[mscertified]

Each table needs a primary key which you can make an autonumber. Tables are
linked via the primary key.
I don't know why you would have model and style in the company table, that
would have only company # surely?
You would have something like
COMPANY table:
CompanyID
Company #
CompanyDescription etc. etc.
ENGINE table:
EngineID
CompanyID (links to COMPANY table)
EngineDescription etc. etc.
PARTS table:
PartID
EngineID (links to ENGINE table)
PartDescription etc. etc.

-Dorian

 

[Greg2582]

I've tried it, but the data is giving me a problem. In the ENGINE table,
would you enter a Model# 50 times, if you had 50 Engines that could fit that
model? Also in the PARTS table, how would handle the fact that part # A301
for example, can work with Engines MCZ01, MCZ02, MCZ03, etc. You could have
several part#'s that work with mulitiple Engines, depending on the Model #.