Primary key

[Guest]

My data base has one large table with around 120 fields, the primary key
being a job I.D.. I need to start tracking more detailed information
pertaining to the material needs of each job. I will probably need to add
another 80 or so fields. If I start a new table is it o.k. to have the
primary key be the same job I.D. as it is in my main table? The only unique
datum for each job is its job I.D.

Thank you
Greg Snidow

 

[Jeff Boyce]

Greg

From your description, you have a well-designed ... spreadsheet! A check
through the tablesdbdesign newsgroup will show that a well-normalized table
in a relational database (Access) will rarely need more than 30 fields.

Is there a reason you can't simply use a spreadsheet, if that model works
better for you?

If you want to use an Access database, you'll get much more out of it if you
spend the time to normalize your data.

Regards

Jeff Boyce
<Access MVP>

P.S. In case my 'between the lines' was too opaque, DON'T add the extra
fields. Turn off your computer and spend the time normalizing your data
structure before continuing in Access.

 

[Rick Brandt]

My data base has one large table with around 120 fields, the primary
key being a job I.D.. I need to start tracking more detailed
information pertaining to the material needs of each job. I will
probably need to add another 80 or so fields. If I start a new table
is it o.k. to have the primary key be the same job I.D. as it is in
my main table? The only unique datum for each job is its job I.D.

Thank you
Greg Snidow

Your building a spreadsheet, not a database table. A properly designed database
will rarely have any tables with even 50 fields. The most common mistake is
embedding a one-to-many relationship that should be built with two tables inside
of a single table. If you have any fields like Material1, Material2, etc.. or
columns for weeks, months, or years then this is what you have done.

Access tables and queries have a hard limit of 255 fields and 2KB of data per
row. With you current design you will likely hit this limit even if the new
fields are in a second table since you might need to combine them in a query to
perform certain activities.

Read up on the topic "database normalization" for more details.

 

[Guest]

Thanks to both of you guys. I think I have created a monster, and now I do
not know how to fix it. Is there one good book for an Access biginner?

 

[John Vinson]

Thanks to both of you guys. I think I have created a monster, and now I do
not know how to fix it. Is there one good book for an Access biginner?

There are several; one of the best I've seen is John Viescas'
_Building Microsoft Access Applications_. _The Access Bible_, _Running
Microsoft Access <version>_, and others are available; learning styles
and backgrounds differ, so you might want to go to a good bookstore
and look through three or four books to find one that suits you.

John W. Vinson[MVP]