need serious help in restructuring table

[Guest]

I have a database with a table that has over 200 fields. It's worked fine for 3 years but now we need additional data fields and now I have a mess.... This is a tree of the fields and how they relate- (Database is for building inspections and results)

Facility #
Inspection type#1 Inpection type #2 Inspection type #3 Inspection type #4
Discrepancy 1-25 Discrepancy 1-25 Discrepancy 1-25 Discrepancy 1-25
workcenter (each discrep) same same same
est. labor hours (each disc) same same same
labor cost (each disc) same same same
material cost (each disc) same same same
total cost (each disc) same same same
service workorder (each disc) same same same
major work pkg (yes/no each disc) same same same
additional information (8 fields) same same same

record example
fac#, inspectype, 8 additional info fields, discrepancy1, discrepancy2, discrepancy3...to discrepancy25, wc1...to wc25, lh1...to lh25, lc1...to lc25, mc1...to mc25, tc1...to tc25, wo1..to wo25, pkg1...to pkg25
note: each wc, lh, lc, mc, wo & pkg is related to it's discrepancy (1-25)


now I need to add 6 new fields for each major work package that is related to a discrepancy. The 6 fields have set values. I've tried over the past 2 weeks to work around this by creating 2 new tables, subforms, combo boxes, update queries, append queries, make table queries but inevitably I get a too many fields defined messages. How can I reduce this monster into seperate tables and make it all work as one in forms, reports, etc.??? Please help!!!! My boss has told me to give it up and just hand write the additional info on the printed copy of the form but I never give up.....not my style!!

I know this is a big mess but I need some serious help!
Thanks in advance....

 

[Jeff Boyce]

From your description, I'll guess that this started out life as a
spreadsheet. Repeating fields ("Inspection type#1", "Inspection type#2",
.... or "Discrepancy 1-25", "Discrepancy 1-25", ...) are absolutely necessary
if the only tool you have is a spreadsheet.

Access, however, is a relational database. Your concern about how to split
the table and still make it work in forms, reports, etc. is well-founded.
Are you certain that you need anything more than a spreadsheet?

If you decide you need what a relational database can do for you, you'll
need to start over from scratch to resolve your problem. The current
design, as you are finding, causes terrible headaches, like having to
re-design tables, forms, queries and reports, not to mention any code
modules you've added.

The first place to look is to get some background on the subject of
normalization. Here's a link into the Microsoft Knowledge Base -- search
for article #100139:
http://support.microsoft.com
A well-normalized design will obviate most of the problems you are having.

The good news is you aren't the first person to have to confront this
"problem." The bad news is that this "problem" is usually self-inflicted.
Reminds me of a bumper sticker ...

'sheet happens

Good luck!

Jeff Boyce
<Access MVP>

 

[Guest]

Actually, it started as a dbase file with only the facility#, inspection type, compdate and 8 other fields. Everything else was hand written by inspections and kept in a file with NO computer records to access. I expanded on the dbase file then was asked to convert it to Access. I used to write custom programs in DBASE3 (self taught) so it was my preferred weapon of choice. I fumbled through Access with the conversion and setting up forms and reports and like I said everything has worked great for the past 3 years and everyone loves having all the data in the computer. I have 3 other people waiting on me to get this finished with the new data fields so they can start entering new discrepancies so I guess I'm going to have to let them hand write everything until I get it restructured from scratch. Thanks for the advice though!