3 FIELD should be unique (1 Viewer)

abshmo5

New member
Local time
Today, 16:59
Joined
Jun 25, 2018
Messages
7
Hi all again,
Good day.
I need help in Ms Access, i dont know where this should be place, at the table? or at the code?

Say i have 5 fields and all of those fields can be duplicated but, once 3 fields from those 5 fields be duplicate the system will popup an error.

Field1 = Duplicate OK
Field2 = Duplicate OK
Field3 = Duplicate OK
Field4 = Duplicate OK
Field5 = Duplicate OK

But Field1 & Field4 & Field5 in a row should not be duplicated.

Am I thinking possibilities on this?

Thank in advance.
Cheers
abs
 

isladogs

MVP / VIP
Local time
Today, 08:59
Joined
Jan 14, 2017
Messages
18,186
Create a composite non-duplicate index for those fields
OR if preferred make the fields a composite PK field
 

abshmo5

New member
Local time
Today, 16:59
Joined
Jun 25, 2018
Messages
7
Create a composite non-duplicate index for those fields
OR if preferred make the fields a composite PK field

Thanks Ridders,
But i really a newbie on this cant figure out what was your advise.
Please enlighten me or show an example, is it in a table to be done or in a code?
Thanks again.
Cheers
abs
 

isladogs

MVP / VIP
Local time
Today, 08:59
Joined
Jan 14, 2017
Messages
18,186
Thanks Ridders,
But i really a newbie on this cant figure out what was your advise.
Please enlighten me or show an example, is it in a table to be done or in a code?
Thanks again.
Cheers
abs

Both methods are done in the table design

If you have an existing primary key field, create a composite index as shown below



OR create a composite PK field by holding the control key down as you select each of the fields then click Primary Key



I do hope your fields have meaningful names rather than Field1, Field2 etc
 

Attachments

  • CompositeIndex.PNG
    CompositeIndex.PNG
    58.6 KB · Views: 233
  • CompositePK.PNG
    CompositePK.PNG
    39.6 KB · Views: 219

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:59
Joined
May 21, 2018
Messages
8,463
Say i have 5 fields and all of those fields can be duplicated but, once 3 fields from those 5 fields be duplicate the system will popup an error.
@Ridders,
I think you need to reread the OP's post. There is no way to make a "conditional" key where you can duplicate any 3 of 5.

@abshmo5,
Can you provide an example? Are you really talking fields or are you talking records? My guess based on your question is that your table is not designed correctly. It is likely not normalized.
 

sonic8

AWF VIP
Local time
Today, 09:59
Joined
Oct 27, 2015
Messages
998
Please enlighten me or show an example, is it in a table to be done or in a code?
Ridders has already posted an excellent reply.



If you want to deepen your knowledge, you might be interested in a very extensive video on Indexes in Access, which I quite recently recorded. It includes multi-column-indexes as well.
 

sonic8

AWF VIP
Local time
Today, 09:59
Joined
Oct 27, 2015
Messages
998
There is no way to make a "conditional" key where you can duplicate any 3 of 5.
[...]
My guess based on your question is that your table is not designed correctly. It is likely not normalized.
Thank you MajP, very attentive, I missed that as well.
I agree, twice. There is no way to do this in any index/key constraint.

And I too think, this is a strong indicator of a problem with the database/table design.


If there actually is a legitimate reason for this, the only option to insure "integrity" with this condition would be a Data Macro. - But before we delve deeper into this, we should think about the requirements.
 

isladogs

MVP / VIP
Local time
Today, 08:59
Joined
Jan 14, 2017
Messages
18,186
@Ridders,
I think you need to reread the OP's post. There is no way to make a "conditional" key where you can duplicate any 3 of 5.

Hmm....I've re-read the OP and still think I'm correct

Please have a look at this & tell me what you think is wrong with it
Trying to duplicate record 1 in record 12 has been correctly prevented using a composite PK field
Each field has several duplicate values but only in records 1 & 12 would all of fields 1, 4 & 5 be identical



I've also rechecked and the values are blocked using a composite index.
However, my original screengrab was incomplete for the index - complete version below



Am I missing something obvious here?
I agree totally about bad table design ....
 

Attachments

  • Proof.PNG
    Proof.PNG
    22.5 KB · Views: 194
  • CompositeIndex.PNG
    CompositeIndex.PNG
    36.7 KB · Views: 181
Last edited:

sonic8

AWF VIP
Local time
Today, 09:59
Joined
Oct 27, 2015
Messages
998
Am I missing something obvious here?
After reading MajP's remark, I understand the question as follows: abshmo5 want's to have a 5 field index, of which any 3 fields must not contain duplicates.

After thinking about this some more, I think it might actually be achievable by creating multiple 3-field-indexes with every possible combination of fields out of the 5.

This would be 10 different indexes, wouldn't it? Still well below the 32 index limit per table of Access. Nevertheless, this certainly is not a good practice for table design.

...this kind of math makes my brain hurt. :(
 

isladogs

MVP / VIP
Local time
Today, 08:59
Joined
Jan 14, 2017
Messages
18,186
Ah!
There were two important sentences in post 1

Say i have 5 fields and all of those fields can be duplicated but, once 3 fields from those 5 fields be duplicate the system will popup an error.
....

But Field1 & Field4 & Field5 in a row should not be duplicated.
[/QUOTE]

I focused entirely on the latter in which case both my solutions are fine.

If the condition is that ANY three fields cannot be duplicated, then obviously a composite PK won't work.
As Phillip says, 10 different indexes each containing 3 fields would work IF you can use fields in multiple indexes. I've never tried so don't know if that's possible. Even if it is possible, it isn't a good idea.
An alternative would be a UDF checking every combination of triple field values.
I'm not sure how fast that would be in operation.

@abs
What did you actually mean?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:59
Joined
May 21, 2018
Messages
8,463
I interpreted this differently as across fields and not amongst fields. The OP will need to specify.

I can build a 5 scoop ice cream Sunday, but not more than 2 of the same flavor allowed
Chocolate, Chocolate, Vanilla, Strawberry, Cherry
but not
Chocolate, Chocolate, Chocolate, Strawberry, Cherry

Generic function to test for any max repittion

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim MaxSeq As Integer
  MaxSeq = MaxSequence(Me.colOne, Me.colTwo, Me.colThree, Me.colFour, Me.ColFive)
  If MaxSeq > 2 Then
    MsgBox "Can not have three of same choice please update you have " & MaxSeq & " of one item"
    Cancel = True
  End If
End Sub

Public Function MaxSequence(ParamArray Choices() As Variant) As Integer
  Dim I As Integer
  Dim J As Integer
  Dim selection As Variant
  Dim LocalMax As Integer
  Dim GlobalMax As Integer
  For I = 0 To UBound(Choices)
    LocalMax = 0
    selection = Choices(I)
    For J = 0 To UBound(Choices)
       If Choices(J) = selection Then
          LocalMax = LocalMax + 1
          If LocalMax > GlobalMax Then GlobalMax = LocalMax
       End If
    Next J
  Next I
  MaxSequence = GlobalMax
End Function
 

Mark_

Longboard on the internet
Local time
Today, 01:59
Joined
Sep 12, 2017
Messages
2,111
@ OP,

Can you clarify what your business rules are? The "No duplicates" can either mean you are saving multiple instances of the same data in a single file which indicates an issue with data normalization OR are you trying to verify that the same pattern is not repeated in different rows of the same table?

Both are useful but for different reasons.
 

abshmo5

New member
Local time
Today, 16:59
Joined
Jun 25, 2018
Messages
7
Hi sorry for not being clear here. here is my example:

Thanks

by the way i attached image, but i cant view on my post
 
Last edited:

abshmo5

New member
Local time
Today, 16:59
Joined
Jun 25, 2018
Messages
7
Hi all,
Upon reading thoroughly on your replies, i figure it out.
I set it on my table as what @ridders suggested and i am happy on the result.
My table now is setup properly.
Thanks a lot guys.
Cheers
 

isladogs

MVP / VIP
Local time
Today, 08:59
Joined
Jan 14, 2017
Messages
18,186
Hi abshmo5

For some reason, the image link worked viewing this on my phone
As I'm sure you have realised your original post could be interpreted in several ways
Glad that my solution worked - I hit lucky by focusing on what turned out to be the crucial sentence.
 

Users who are viewing this thread

Top Bottom