Access Duplicate Records

[MHamid]

Hello,

I need some suggestions.

I have a Form that will add records to a table.
These are the fields that will need to be filled using the form:
Entered By
Date Entered
Cycle Month
Report Type
Date Reviewed
Reviewer Type
Reviewer
Reviewer Report Area
Main Section
Topic Section
Ownership-Individual
Ownership-Team
Count
Priority
Approved By
L1 (Quality Category L1)
L2 (Quality Category L2)
L3 (Quality Category L3)
Other (If Other is selected in L3)
Repeat Ask (check box)
Exception (check box)
Notes
Outliers
Additional Outliers

I have a RefID column that is an auto-generated number.
I need to ensure that when a record is added that it is not being duplicated. However, based on the columns mentioned above, there is no customer name or account number that will make this task easy to ensure that there are no duplicates.
In looking at each column, they can be duplicate in of itself, but the entire row cannot be the same.
How can I ensure there will be no duplicates added to the table by checking if all columns are the same?


Thank you

 

[mole999]

if you concatenate the columns, then before allowing the post concatenate the input and have it sent to not allow duplicates. Use only values that are exact but unique, so additional outliers as maybe a text field with no defined format should not be used, dates, owners etc are in unless free text

 

[Kyle123]

I think you need to normalize your table (and remove the spaces from field names). That will reduce the number of fields on the table and will make validation easier.

Tbh I think you may be fighting a losing battle trying to make these unique, for example the chances of someone putting exactly the same thing into a Notes field as someone else is extremely small

 

[MHamid]

Mole 999, so you're suggesting to concatenate all fields that are not free text, correct?

Kyle123, I was afraid that I might be fighting a losing battle here, lol

 

[mole999]

Mole 999, so you're suggesting to concatenate all fields that are not free text, correct?

Kyle123, I was afraid that I might be fighting a losing battle here, lol

or enough that will give you a unique key to compare against and achieve your aim

 

[MHamid]

Ok, I will try your suggestion and will let you know how it works out.

 

[xenou]

Just for the record it isn't precisely necessary to concatenate fields. Just that each field (which shouldn't be duplicated) isn't duplicated.

For example, if a record is:
1, 'abc', 'def'

you can check:

insert into MyTable values (2, 'abc','def') 
    where not exists (select * from MyTable where F2 = 'abc' and F3 = 'def')

or you can check:

insert into MyTable values (2, 'abc','def') 
    where not exists (select * from MyTable where F2 & F3 = 'abcdef')

In either case, nulls are a special case that must be handled appropriately (not shown here!) and in the latter case make sure you are assuming the right things about how conversions from non-text to text will occur (since you want to compare a concatenated string to a concatenated string).

 

[Marbles]

Indexing in the Design View of your table might be a bit easier.

An example: http://www.alphapresspublishing.com/AccessArticles/Article_CreateIndex_Multi-fieldIndexes.html

 

[MHamid]

Hello,

I am back to this issue. My brain must be fried at this point because I cannot make sense as to how to check if the record being added has already been added to the table.

I have the below so far, but can't seem to get a code started for checking if ConcatFormValues = ConcatTableFields then msgbox "Record has already been added".

Private Sub Form_AfterUpdate()

Dim ConcatTableFields As String
Dim ConcatFormValues As String
Dim strDupe As String

ConcatTableFields = [EnteredBy] & " " & [DateEntered] & " " & [CycleMonth] & " " & [ReportType] & " " & [DateReviewed] & " " & [ReviewerType] & " " & [Reviewer] & " " & [ReviewerReportArea] & " " & [MainSection] & " " & [TopicSection] & " " & [OwnershipIndividual] & " " & [OwnershipTeam] & " " & [Count] & " " & [Priority] & " " & [ApprovedBy] & " " & [L1] & " " & [L2] & " " & [L3] & " " & [RepeatAsk] & " " & [Exception] & " " & [Hyperlink]
ConcatFormValues = Me.EnteredBy & " " & Me.DateEntered & " " & Me.CycleMonth & " " & Me.ReportType & " " & Me.DateReviewed & " " & Me.ReviewerType & " " & Me.Reviewer & " " & Me.ReviewerReportArea & " " & Me.MainSection & " " & Me.TopicSection & " " & Me.Individual & " " & Me.Team & " " & Me.txtCount & " " & Me.PriorityLevel & " " & Me.Approved & " " & Me.L1 & " " & Me.L2 & " " & Me.L3 & " " & Me.Repeat & " " & Me.Exception & " " & Me.txtHyperlink

The reason I am doing it this way is because each field by itself can be duplicated. I need to know that the entire record being added has not already been added. Hopefully I didn't confuse anyone and hopefully I'm not overcomplicating an easy task

 

[xenou]

I would not do this with concatenation personally.
Just use regular fields in your sql.

For instance, to check if a record exists Table1 with values (1,2,3) for the first three fields, just:

select * from Table1 
where Field1 = 1
and Field2 = 2
and Field3 = 3

As alluded to, an index that excludes duplicates would also work (insertion would fail).