Find days of mixed content

[Guest]

You guys have been great helping me understand datediff("d",[date1],date())
and it works great but I soon found that it was not totally what I needed.

tbl: Choco
fields: Date1, Milk, Dark, Vanilla...
The data I enter in any given record would be the nuts, caramel,
coconut...etc. It's easier for me doing it this way but I'm open to change.

The following is based on the fact that the Food & Drug Administration (FDA)
requires me to make ingredient labels by "Order of Predominance" meaning that
if there is more milk chocolate by weight than the sub ingredients i.e. nuts
then I must state milk chocolate ingredients first on the label followed by
nut ingredients.
With this in mind:

Say I made the following batches:
1/1/2005 Milk Chocolate with Nuts & Caramel
1/1/2005 Caramel with Milk Chocolate & Nuts
1/1/2005 Nuts with Caramel & Milk Chocolate
1/3/2005 Caramel with Dark Chocolate & Nuts
1/3/2005 Dark Chocolate with Caramel & Nuts
1/3/2005 Nuts with Caramel & Dark Chocolate
1/10/2005 Milk Chocolate with Nuts & Caramel
1/10/2005 Dark Chocolate with Caramel & Nuts
1/10/2005 Vanilla with Caramel & nuts
I know this seems redundant but necessary for the sake of the answer.

I need to be able to have my datediff("d",[date1],date()) show the amount of
days since the last time I made a given combination (for pull date reasons).

perhaps modifying my tables differently would help but I make chocolates and
my mind is not allowing me to visualize a transformation.
Does this make since?
Thank you for your time and response
Luke

 

[John Vinson]

You guys have been great helping me understand datediff("d",[date1],date())
and it works great but I soon found that it was not totally what I needed.

tbl: Choco
fields: Date1, Milk, Dark, Vanilla...
The data I enter in any given record would be the nuts, caramel,
coconut...etc. It's easier for me doing it this way but I'm open to change.

A better design would be to use a Many to Many relationship, which
needs THREE tables:

Batches
BatchID Autonumber <or maybe you have a manual batch number>
BatchDate
<any other info about the batch as a whole - name?>

Ingredients
IngredientID
IngredientName
<perhaps other info about the ingredient, cal/g or the like>

Composition
BatchID <<< link to Batches
IngredientID <<< link to Ingredients
Quantity

If a batch had six ingredients, rather than having six *fields* this
table would have six *ROWS*.

perhaps modifying my tables differently would help but I make chocolates and
my mind is not allowing me to visualize a transformation.
Does this make since?

Consider the normalized design above. It will indeed let you do this -
including sorting ingredients in descending order of amount.

John W. Vinson[MVP]

 

[Guest]

ThanK you John I will give it my best shot. you haven't given me a bad batch
yet. This may take me awhile to digest and complete. I'll let you know later
in a new thread.
Thank you
Luke

You guys have been great helping me understand datediff("d",[date1],date())
and it works great but I soon found that it was not totally what I needed.

tbl: Choco
fields: Date1, Milk, Dark, Vanilla...
The data I enter in any given record would be the nuts, caramel,
coconut...etc. It's easier for me doing it this way but I'm open to change.

A better design would be to use a Many to Many relationship, which
needs THREE tables:

Batches
BatchID Autonumber <or maybe you have a manual batch number>
BatchDate
<any other info about the batch as a whole - name?>

Ingredients
IngredientID
IngredientName
<perhaps other info about the ingredient, cal/g or the like>

Composition
BatchID <<< link to Batches
IngredientID <<< link to Ingredients
Quantity

If a batch had six ingredients, rather than having six *fields* this
table would have six *ROWS*.

perhaps modifying my tables differently would help but I make chocolates and
my mind is not allowing me to visualize a transformation.
Does this make since?

Consider the normalized design above. It will indeed let you do this -
including sorting ingredients in descending order of amount.

John W. Vinson[MVP]