Trouble Creating Tables

[Guest]

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 indefinite9As
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 Model Style MCZ01 MCZ002 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

 

[Guest]

Set your table up like this ---
Engine Model Style PartNo
MCZ01 57111 00 A301
MCZ01 57112 00 A301
MCZ01 57121 00 A301
MCZ01 57122 00 A301
MCZ01 57131 00 A304
MCZ01 57132 00 A301
MCZ01 57133 00 A302
MCZ01 57141 00 A301
MCZ01 57142 00 A301
MCZ02 57111 00 A301
MCZ02 57112 00 A301
MCZ02 57121 00 A301
MCZ02 57122 00 A301
MCZ02 57131 00 A304
MCZ02 57132 00 A301
MCZ02 57133 00 A304
MCZ02 57141 00 A301
MCZ02 57142 00 A302
MCZ03 57111 00 A301
MCZ03 57112 00 A301
MCZ03 57121 00 A301
MCZ03 57122 00 A301
MCZ03 57131 00 A304
MCZ03 57132 00 A301
MCZ03 57133 00 A304
MCZ03 57141 00 A301
MCZ03 57142 00 A301
MCZ03 57111 00 A302
MCZ03 57112 00 A302
MCZ03 57121 00 A302
MCZ03 57122 00 A302
MCZ03 57131 00 A303
MCZ03 57132 00 A305
MCZ03 57133 00 A305
MCZ03 57141 00 A301
MCZ03 57142 00 A304

 

[John W. Vinson]

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 indefinite9As
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 Model Style MCZ01 MCZ002 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

That's a typical spreadsheet design... but not a correct relational
design. Spreadsheets are "wide-flat"; tables should be "tall-thin".

I'd suggest THREE tables:

CompanyNo
Model <Text 5>
Style <Text 2>
Description

Engines
EngineModel <Text 5>

PartNumbers
Model <Text 5>
Style <Text 2>
EngineModel <Text 5>
PartNumber <Text 4>

Adjust the text field sizes as appropriate.

The PartNumbers table would be related to the CompanyNo table by Model
and Style; and to the Engines table by EngineModel. Each cell in your
spreadsheet would correspond to one ROW in PartNumbers. It then
becomes very easy to search or filter the PartNumbers table by any
combination of Model, Style and EngineModel.

John W. Vinson [MVP]