Would appreciate some experienced eyes

[Watergirl]

I have been working on this database for several months now - in between all of the other jobs I am responsible for - using what I've learned from this forum, from the 'Access 2002 Bible', and lots of hit-and-miss, restarts and frustration. I may have OVER normalized . . .
I'm running into trouble creating the reports I want, and have now learned enough to think I may need to create a 'fresh' database with what I know now. Before I do, I would appreciate some experienced eyes that will see what mistakes I have made and how I can improve this database before copying mistakes and taking it further.

Background: I was asked to create a database to replace an outdated and undocumented dBase program to keep track of our cost of goods and recipe base. We manufacture waste water treatment chemicals, primarily for municipalities. Each of our products is a recipe containing various raw materials in differing amounts. One concern I have is that some of our end-product recipes are also raw materials used to create other products. I may be able to enter each one as both an ingredient and a finished product as each will be assigned a different primary key.

We need to be able to create reports that tell us how much a specific recipe costs to make in various quantities, a printed 'recipe' listing each ingredient, amount, product type, etc. as well report groups on each product type. I plan to change the raw materials cost every time a new shipment of raw materials comes in using an update query.

Thank you in advance for any direction you can give. I am an Access newbie, but I am willing to do the homework!

And if I am very lucky, maybe Doc will give it a look?!
Pam

 

[KeithG]

The Doc man dosen't like to download files so I don't think he will take a look unless you post your ER digram. I see some issues with your table structures. For example in the Recipe Ingredents table you are have several fields in the table that are also in other tables ( Recipe Name, Price etc). You dont need these field in that table because you can link to the other table to retrieve the data. Also spaces in table and field names is a bad idea.

 

[Watergirl]

The table 'Recipe Ingredients' is populated when I fill in the 'Recipes' form for each recipe. I'm pulling info from different tables but storing by each product, thinking I could retrieve it that way in reports, etc.

Removing the space between the words is definitely one of the things I will change with the 'new and improved' version.

How do I get a snapshot view of just the relationships in a form that is not a download?

And thank you for answering!
Pam

 

[KeithG]

Instead of putting the fields in the Recipe Ingedients tables (recipe name, price) what you need to do is add a field for the FK (Foreign Key).

 

[Watergirl]

The foreign key field would be placed in the Recipe Ingredients (for my details) table and the duplicate information columns deleted?

 

[CraigDolphin]

Well, I'm definitely not in the league of the Doc Man, but it seems to me like this is somewhat of a recursive db issue. You have currently have two tables, Recipes and Ingredients, which I think may be named more appropriately as 'Products' and 'RawIngredients'. A product has a unique recipe which is a collection of raw ingredients (hence your intermediate table), and sometimes a product is also a raw ingredient....which indicates to me that products and raw ingredients really belong in the same table. Let's call it 'Materials'.

Some materials have a recipe, and some don't.

Materials
MaterialID (auto, pk)
RecipeID (fk)

Recipes
RecipeID(auto,pk)
RecipeName

Recipes are a collection of materials, hence...

RecipeMaterials
RecipeMaterialID (Auto; pk)
RecipeID (fk)
MaterialID (fk)

Have a look at the attached db to see how these work together in queries to provide a list of primary ingredients (materials that are listed in the recipe for the product) and secondary ingredients (materials that are listed in the recipes for primary ingredients). You could expand the concept to get tertiary ingredients (another step removed) and so forth.

Then I've created three different ways of listing ingredients for two hypothetical manufactured products

The first lists primary ingredients in one field, and secondary ingredients (if any) in an adjacent field.

The second lists primary and secondary ingredients together in one field. So if product X is made up of Y and Z, and Z is made up of AA and BB, this would show Y,Z,AA,BB for Product X

The third lists primary ingredients that have no secondary ingredients, or secondary ingredients, but does not show the intermediate product. Thus, the above scenario would return Y,AA,BB for Product X.

I think that this last one is probably the approach that you would need to determine your raw materials cost.

In the attached example db, 3 materials (A,B,C) are combined to make material D. Material D & E are then combined to make Material F.

Hope it provides some food for thought.

 

[Watergirl]

I so appreciate your input, Craig. I would love to simplify the structure I have now, and to combine the tables. Your explanation makes a lot of sense.

Thank you for taking the time to explain things so well, and to give me an idea db as well. I will spend as much time as I can today 'playing' (although in truth, this is no longer 'fun') with your concepts, but I wanted to thank you right away for taking the time and lending your brains.

I really want to be over this - I think I have made it way harder than it needs to be, and in trying to teach myself and to learn by doing, I've gone down more rabbit trails than I care to count.

Thanks - I'm sure I'll be back!
Pam

 

[CraigDolphin]

The good thing about going down 'rabbit trails' is that you learn stuff that, though unrelated to your present problem, may aid you with next week's burning issue.

Don't stress. It'll all work out.

And incidentally, you asked earlier about how to post relationships etc. The method I use is to open the relationships window and use ctrl-Print Screen to save a screenshot to the windows clipboard. Then I open Start>Programmes>Accessories>Paint and paste the clipboard into the Paint program. I usually edit the image to remove the extra stuff that I don't want, and save the image as a jpeg (it defaults to bmp but bmp makes too large a file). I usually just upload the image as an attachment to the post and folks can open the attached jpgs in their browser without downloading them.

If you want to embed the image in the post you need to upload your image to a host server on the web, and use the insert image button available in the advanced editor. Then you specify the url of the image when prompted to.

 

[Watergirl]

Thanks for the instructions, and the encouragement. Guess I'll keep chasing those bunnies . . .