Multi-Field duplicate prevention

[theaybaras]

Hi All,

I am creating a databse of articles and related information. I need to find a way to prevent the user from inputting duplicate information in a few forms.

In one of these forms I can just set my table not to accept duplicate values (Journal Title) because no two journals will have the same name.

In another, I am working with author information. I think to allow only unique records i will need to check last name, first name, middle name(s) and maybe one other field. More than one individual may have the same first last and middle name but would have (most likely) a different affiliation or other piece of information that distinguishes a duplication vs a similar entity.

In my final table (this is the most important to not have duplicate records) I have all info about an article. I think the best way to avoid duplicates is that no two articles can take up the same space in the same publication. So I have, 3 fields (at minimum) that distinguish one article for another
Journal Title, Volume, and Page Range.

My question is this, how can I prohibit the entry of duplicates by comparing the contents of all three fields to the rest of the records in my table. And then have a message pop up letting the user know that they've tried to enter in duplicate information, that sends them to the pre-existing record upon "okay".

Thanks!

[email removed]

[MMcCarthy]

You have posted this question in the Articles section. I am moving it to the Access forum.

ADMIN

[ADezii]

Hi All,

I am creating a databse of articles and related information. I need to find a way to prevent the user from inputting duplicate information in a few forms.

In one of these forms I can just set my table not to accept duplicate values (Journal Title) because no two journals will have the same name.

In another, I am working with author information. I think to allow only unique records i will need to check last name, first name, middle name(s) and maybe one other field. More than one individual may have the same first last and middle name but would have (most likely) a different affiliation or other piece of information that distinguishes a duplication vs a similar entity.

In my final table (this is the most important to not have duplicate records) I have all info about an article. I think the best way to avoid duplicates is that no two articles can take up the same space in the same publication. So I have, 3 fields (at minimum) that distinguish one article for another
Journal Title, Volume, and Page Range.

My question is this, how can I prohibit the entry of duplicates by comparing the contents of all three fields to the rest of the records in my table. And then have a message pop up letting the user know that they've tried to enter in duplicate information, that sends them to the pre-existing record upon "okay".

Thanks!

[email removed]

What are requesting are known as Multiple Field Indexes. Here is how you create them:

1. Select your Table ==> Design View.
2. View ==> Indexes.
3. Add up to 10 Field Names for a given Index.
4. Select your Sort Orders for each Field, if so desired.
5. Add a Name to comprise the New Index. This Name must reside on the 1st Index Field and so will apply to the entire Index.
6. In Index Properties, set Unique = YES.

[theaybaras]

Hi there :)

Thanks so much for the help! Your guidance got this working for me awhile ago, I thought I had posted thanks, but just found I hadn't... so


THANK YOU!!! :o)

theAybaras

[ADezii]

Hi there :)

Thanks so much for the help! Your guidance got this working for me awhile ago, I thought I had posted thanks, but just found I hadn't... so


THANK YOU!!! :o)

theAybaras

Not a problem, gald it worked.