Design Fundamentals Help Needed (1 Viewer)

[Astir]

Hi there,

I fully admit to being very new to Access, and may be being overambitious but I am hoping some kind soul can help, or even point me into the right direction for further research to help me create my database.

I am looking to create a database that, for the sake of simplicity, will hold a list of raw materials, a list of 'recipes' and a third table with details of each time the recipe is created. Although I am starting with just a log of the raw materials I want to eventually expand to include pricing elements; but for now the basics are enough!



I expect to have three tables:

• tblRawMaterials
  • This will be a simple list of raw materials/ingredients with an ID
• tblRecipe
  • This would hold the recipe name (and ID) and, ideally, a list of the raw materials needed by name and quantity to produce 1 finished item by selecting items from tblRawMaterials
• tblProduction
  • Each record would be a 'production run'. It should require the user to select the recipe and identify the number of items to be produced, and then automatically fill in the total required raw materials presenting the partially completed new record for completion by the user (e.g. dates started, dates completed etc.)
  • The raw materials amounts 'copied' into the record should be those present at the time the record is created, as the amounts may change over time.

So far I have had a look at things such a append queries, but this seems to require each recipe/production run has a field for every possible raw material? I am sure that there is a simpler and more elegant solution to this and one that would support further development of the tables, e.g. eventually for the raw materials to have a price associated, and therefore a production cost that could be calculated for each 'run'.

I have very limited Access experience, and likewise with any coding, so I am struggling with the basic principles at this stage (embarrassing though that is). I have looked for example databases of a similar nature, but those I have seen have generally been 'developed' for the end user to dive straight into and not easily 'deconstructed'.

Any advice would be most welcome, even if this is to put this project to one side for now and to work on understanding fundamental concepts first.

Many thanks,

Paul

 

[Galaxiom]

The RawMaterials used for each Production records should be held in another related table.

This table has a field for the PK of the Production record and another for RawMaterialID. A third field records the quantity of each material used. There is one record for each material used in the production run.

The data from this table is displayed in a subform of the Production form with the ProductionID as the LinkFields of the subformcontrol.

 

[Rabbie]

Also read up on Normalization. If your data is not normalized you will have lots of problems

 

[Pat Hartman]

but this seems to require each recipe/production run has a field for every possible raw material?

No. The append query in your case will copy the records of a "recipe" and append them to tblProduction. The query will calculate the quantity used by multiplying the qty required per unit times the number of units ordered so the records in tblProduction will contain the actual quantity used for that run.