Prev: validation for one field based on another
Next: Table Structure Help
From: TJ on 29 Mar 2010 18:33
Access 2007
I have a formulas table with the following fields:
Auto FormulaID Ingredient Percent
267 127 FlourA 51
268 127 FlourB 46
269 127 ProteinA 2
270 127 FlavorA 1
271 128 FlourA 51
272 128 FlourB 49
273 129 FlourA 25
274 129 ProteinA 25
275 129 FlourB 47
276 129 FlavorA 3

Each FormulaID can be tied to multiple products. There will be hundreds of
Formula, each possibly having a different number of ingredients and/or
different percentages. The percent for each formulaID will total 100%. I
would like to prevent duplicate formulas from being entered by a user when
inputting each of these fields. A multifield index for ingredient and
percent, set to unique, prevents duplicate ingredient/percent combinations
which may exist in 2 different formulas so this won't work. A unique index
for fields FormulaID, Ingredient, and Percent will not prevent duplicate
groups either as when entering a new formula the FormulaID will be different
than any already existing. Has anyone run into this or something similar?
If so, could you point me in the right direction to make this work? Thanks,

--
TJ
From: KARL DEWEY on 29 Mar 2010 19:23
Why would not a multifield index for ingredient and percent, set to unique,
prevent duplicate ingredient/percent combinations which can not exist in 2
different formulas?

If it is the same ingredients and percent then it is the same formula. the
only problem would be that you would not know until you were trying to enter
the last item.

So, maybe a recipe form that has trext boxes for all ingredients with box
for percent to be checked before loading new records.

Maybe a query to compare concatenated ingredients for each formula against
the concatenated text boxes in the recipe form.

--
Build a little, test a little.


"TJ" wrote:

> Access 2007
> I have a formulas table with the following fields:
> Auto FormulaID Ingredient Percent
> 267 127 FlourA 51
> 268 127 FlourB 46
> 269 127 ProteinA 2
> 270 127 FlavorA 1
> 271 128 FlourA 51
> 272 128 FlourB 49
> 273 129 FlourA 25
> 274 129 ProteinA 25
> 275 129 FlourB 47
> 276 129 FlavorA 3
>
> Each FormulaID can be tied to multiple products. There will be hundreds of
> Formula, each possibly having a different number of ingredients and/or
> different percentages. The percent for each formulaID will total 100%. I
> would like to prevent duplicate formulas from being entered by a user when
> inputting each of these fields. A multifield index for ingredient and
> percent, set to unique, prevents duplicate ingredient/percent combinations
> which may exist in 2 different formulas so this won't work. A unique index
> for fields FormulaID, Ingredient, and Percent will not prevent duplicate
> groups either as when entering a new formula the FormulaID will be different
> than any already existing. Has anyone run into this or something similar?
> If so, could you point me in the right direction to make this work? Thanks,
>
> --
> TJ
 | 
Pages: 1
Prev: validation for one field based on another
Next: Table Structure Help