A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Prevent duplicate groups of records in a table



 
 
Thread Tools Display Modes
  #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
  #2  
Old March 30th, 2010, 12:23 AM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Prevent duplicate groups of records in a table

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.