View Single Post
  #1  
Old March 29th, 2010, 11:33 PM posted to microsoft.public.access.tablesdbdesign
TJ
external usenet poster
 
Posts: 184
Default Prevent duplicate groups of records in a table

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