Correction:
Far as the redesign... from our previous communication it seemed to me that I was incorrect by having the long table. So it seemed only logical to break up the different materials and processes into individual tables.
Just the opposite. You have many tables because tall tables are better than wide tables--although a wide table is not "
necessarily" bad--it just depends on what data you are keeping.
=========================
Problem (and this is neither nit-picking nor eye-poking): I think you have a very general misunderstanding or lack of understanding about basic DB prinicples of table design and structure. We've all been there, so try not to be offended. A corollary of that is that folks often try to start building their forms and reports and output products before they have figured out how things are to be designed first. Then to fix the form or report that doesn't work because things are broken, they try to change something else which breaks something else, and now something else which used to work (by some miracle) now no longer works and they try to rearrange something else (or everything else) and turn the crank and are amazed that now, nothing works. Again: we've all been there at some point--especially if we are self-taught.
So, let me talk broad strokes, big picture, 30,000-foot view and describe what you ought to be thinking about for your DB.
First, what are you really keeping in your DB? A
series of production runs, yes? So, your
[highlight5]MAIN TABLE[/highlight], upon which pretty much everything else (somehow) should be related is
tblProduction.
That table should have the basic information about that particular production run: date, operator, start time, stop time, etc. For the most part, I think you have this.
What you
don't have is tables that are
properly related to that main table. I will use the Aggregate table from one of your earlier threads, because "
I thought" we had gone over this and "
I thought" you understood the principle and "
I thought" you would have gradually implemented that
same principle into the rest of your DB design. However, I find it incredibly odd that the one thing that "
I thought" we had working is conspicuously absent from the current DB.
SO, using the principle of the Aggregate, you have up to 10 different types of aggregate (with Percent, used and recieved for each). Rather than using 30 additional fields for your production table--which was your initial design--we broke all the aggregate out into a separate table of only a few fields: ProdID, AggType (because we made a spearate table for that) and then Percent, Used and Received. Notice how the ProdID was the foreign key in the table.
So, use that as your paradigm for your Lime table--just as an example. Let's use two scenarios:
1) You always use lime in every production. You only add it once for any production (I still don't know what the Starting, Ending, Used, Received, Percent means,
but it is immaterial, because it should matter at this point). In this scenario, you "
could" add all these fields to
tblProduction. Remember, this is jsut a scenario.
2)You may (or may not) add lime to your production
OR you may add lime several times during a production run
OR you may add it every time, but only once. In this scenario (which, by the way, includes the first scenario), it is best to have a separate table for Lime,
simply because it gives you greater flexibility in your Lime management (and if you had "Lime reports" you could do essentially all that from one table).
Now, if you are going to have a lime table, based upon the paradigm of the Aggreaget table--which worked--how would you go about designing that table and its appropriate relationship to
tblProduction?
ProdID, LimeBlah1, LaimeBlah2, LimeBlah3, etc.
Do you see how ProdID is always the FK in all the tables directly descended from your production activity? In the end,
tblProduction (when you view all your relationships) begins to looks like the center of a spider web, with all the other tables coming off that one table, many of which are related via ProdID.
This is big picture stuff, and this is how you need to start thinking about your DB.
But don't just take this advice and change
every table all at once. You need to understand these principles first, then once the table structure is built for one aspect of your design, now try to create a form that will work with this design. As I mentioned to you earlier, a Tab Control is a good design for this: You've done that.
BUT, each Page on that tabl control should have sub-form specific to each particular aspect of the production. One subform will be about Lime, one about Aggregate, etc.
ONE
STEP
AT
A
TIME. 
174
