Help with Database Structure

[scottay]

Good day all,



I work for a company that designs ceramic capacitors and am creating a database that will record the ingredients that go into making a ceramic slurry for quality control purposes. I've been messing with different structures and keep running into issues. I have designed many databases for this company and am fairly fluent with MS Access. We use 2003.

So, here's the data I will need to be working with:

Date: The date the slurry was mixed.

Batch #: A unique number that will be assigned to each batch created.

Powder Type: We use different types of powder for different parts. A couple of examples would be LF262 and NPO.

Ingredient: There will be multiple ingredients going into one batch. The number is not constant, and will differ between batches. Ingredients will include the Powder from above, plus water and a binder. Some will have other ingredients, but those are the main 3.

Weight: The weight of each of the ingredients going into the slurry.

Comments: Any comments on the batch as a whole.
------------------------------
This all sounds so simple, but for some reason I'm having troubles with getting the database structure working correctly for me. Mainly in the area of creating a form that will show a batch number, then list the ingredient data for it. What I started with was a form with the Batch#. In that for I put in a subform that contained the ingredient data as a continuous form. This worked ok, except when it came time to input new data.

Any help would be appreciated. Thanks!
Scott

[ConneXionLost]

Hope this helps:

Cheers,

[scottay]

Thanks That's a good start. I'll play with it a little bit and see if it will work for me.

[Rawb]

You didn't say you're having any problems with your structure, but this is how I'd put together the tables (a little rough because I'm going off the top of my head here). . .

Ingredients:
IPartNo (Text, Primary Key) - The "part number" of the ingredient being used. Examples would be your "LF262" and "NPO"
IDesc (Text) - A short(ish) description of the part.

Batches:
BID (Long Integer, Primary Key) - The unique number of each batch. Possibly autoincrement but that depends on how you number your batches.
BIngredients (Integer) - The number of different ingredients in this batch.
BDate (Short Date) - The date the batch was made (if batches have different steps, this may need to be split into separate fields like BDateStart, etc.)
BComments (Memo) - Comments on the batch.

Mixes: (Primary Key is the combination of MBatchID and MIngredientNo)
MBatchID (Long Integer) - The BID (above) of the current batch.
MIngredientNo (Integer) - The current ingredient number of the batch (cannot be more than BIngredients, above). Think of this as if MBatchID was an order number and MIngredientNo were the individual line items on that order).
MPartNo (Text, Index) - The IPartNo (above) of the current ingredient.
MWeight (Double) - The quantity/weight of the ingredient that was added to the batch in PPM. Standard percentages or total weights can be used instead.

Assuming you have a similar table setup, I can't see why your idea of a Batch Form/Mixes Subform wouldn't work. I can't open ConneXionLost's DB though, so you may already be way ahead of me.

[scottay]

Thank you Rawb, I appreciate your input.

I've been tinkering, and I've gotten things working - much thanks to you both!

However, I just remembered that each ingredient will need a lot # for traceability... I think I'll be able to make it work, but while I'm fussing with it, any suggestions on how to include lot #'s? Water obviously won't have a lot #, but I'm thinking I'll just use N/A for items that don't have lot #'s... Unless there are some better suggestions

[ConneXionLost]

The most basic method would be to add "LotNo" to tblSlurry as an attribute.

[scottay]

Ok, I've gotten everything working as I needed from my original post (thanks again for all who offered help!); however, I'm now stuck with a new issue.

I've added cascading combo boxes so lot #'s will be matched up with their corresponding ingredient. It's working great on the main form, but on my continuous subform every time a combo box updates its corresponding friend, it wipes out the values for all the other combo boxes in the subform.

Example:
Box1 Box2
---- -----
Water N/A
LF262 (selecting 2010-TV7795 clears Water's 'N/A')

I've attached the database for anyone who wants to play with it or needs further clarification. Sorry, I had to put it into a ZIP file because it was bigger than the 500K size limit.

Thanks!

[scottay]

Problem solved!

Thank you all for your help. I've attached the fixed database so someone with the same problem can look at my solution.

In short, I placed a text box bound to the the same as the combo. In the after enter event I did a Me.IngredientLotID.SetFocus. I made the text box slightly smaller than the combo box (due to my color formatting this worked best) and placed it on top of the combo box.

Hope this is helpful.

[rayhawk]

Maybe I don't fully understand your program but instead of covering one box with another you should be able to use the visible property of the combobox you want to hide from view. You can set the .visible property to false when you don't want the user to see it and true when you do want them to see it.