Most of the time, spreadsheet data makes for a poor foundation for relational db's. We often tell spreadsheet minded people to forget what they know about Excel when it comes to designing a db so that it doesn't cloud their thinking. I gotta say, the nature of your business is such that I cannot help but marvel that they would task someone with your level of relational db knowledge to do this, and I don't mean that in a disparaging way. I'm saying it sounds like an enormous task for someone who says they don't quite get the concept of table relationships. I guess the best advice we can give is to read up as much as possible and make notes as you go, but the key thing is that if you don't understand what you're trying to learn, seek out another source since that "instructor" is not a good fit for you. That shouldn't be too hard as there must be thousands of sources for just about any db topic you can think of. I'll post some links that may help, or may not work for you. First, though, I question the wisdom of having your back end (tables) as an Access database, given the number of records you have now, which it seems will only get larger. While you could split these into "libraries" of data, to me, it just adds a level of complexity that you don't need. If keeping decision supporting documents (such as pdf charts) or validations (quality system types of documents such as SOP's, checksheets, etc.) as attachments in the db will be a requirement, then AFAIC, Access is a non-starter for your back end tables. You need a more robust database back end, which might require a db admin person who's proficient in SQL Server types of db's. Maybe you have that now, in which case I'd consult with that person. When it comes to fleshing out the design, I still figure there's no better substitute for large paper (like flip chart) and pencil so that you can diagram stuff, rough out forms, plan tables, etc. Anyway, here's what I have for links:
Normalization is paramount. Diagramming maybe not so much for some people.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/
Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html
How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html
Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.