Posting multiple Values or entries to one record

[Northern Lights]

Hello All,

I am trying to create a database for a company I work with which creates a food product which needs to be traceable from creation to finish. I have created a table with all of the necessary information for the creation of a batch. However, there are two aspects which puzzle me.
1. During this batch creation, we add water multiple times which we record each time, i.e., 2cups, 2 cups, 2cups, etc. I have a field for "water", and wish to record each instance and amount of water added, but only want one record for the complete batch. Also I only need the total water amount in the batch record, but want to be able to expand to see the individual amounts as needed.


2. Also, we need to record various readings (temp, etc.) throughout batch creation. Again, same problem as before.

What is the best way to do this? I want to be able to reference this info anytime I want to check creation specs for each individual batch.

I am using forms for data entry.

Thanks!

[ranman256]

The BEST way is NOT to use 1 record.
You want multiple records in the order needed. But all bound to the master record 'recipe'.
Water would just be another single record , but added many times. (1 to many).
(I worked for McCormick)

[June7]

Multi-value fields have issues and experienced database developers avoid them.

As ranman points out, in a normalized data structure, each addition of water would be a new record. Then queries would calculate the total quantity of water.

[Northern Lights]

ranman,
Do I need to split the water out into separate tables? I am including a sample table with all the info I need for 1 batch in the fields. We add hot or cold water 4 units at a time, and want to record each instance. When I look at Batch 193 I want to see the overall composition picture including how much water was added as it was added, but I don't necessarily need to see it broken out right away. Would a query be better for this?

[June7]

Your options are:

1. normalized structure - a related table with a record for each water addition

2. multiple fields - 4 water fields, 4 temperature fields, etc.

3. multi-value fields

[Northern Lights]

Correct, I agree. I was thinking of a subdata sheet or something.

[Northern Lights]

Thanks for your help, I think I'm getting it.