Create Expression in Validation section Prevent Duplicate values in a Field

[Haleakala17]

All,

I am requesting if anyone could help me create an expression in the Validation section to prevent duplicate entries in a field. Please view Powerpoint file on slide 1 on attachment for More detail instructions. Instructions are in simple Text and images which makes it easy to understand. I am using Access 2003.

I will be waiting for your reply and thank you for your time.

Haleakala17

[orange]

To prevent duplicate entries -- make a unique index on the field involved.

Your question does not relate to validation section per se in my view. You need an index to prevent duplicate values,

[Haleakala17]

Orange,

Thanks for the suggestion . . . I have saved my Access file to the mdb format. Now you will be able to see the database. I am aware of creating a unique index however the table I am working on is a many table. Can you view the PPTX file?

[orange]

I can read the ppt and the mdb.

Do NOT use Lookups at the Table level!
see this link
http://access.mvps.org/access/lookupfields.htm

Remove the lookup in table MAIN --- see my attached jpg

What does table Main represent?
What does field Team mean in the Activity table?

Also some of your names are very misleading:
Main Subform There may be a Main form and there may be subforms, but Main subform is very confusing.

Write a 4-5 line description about your intended application.
What exactly are you trying to do -- plain English?

eg I'm sort of guessing at what you're doing.
There are People who have Titles/occupation
Each Person is a member of a Team
Each Person can perform 1 or more Activities

[Haleakala17]

In plain English, prevent a person from having duplicate activities.

in the Main table a person cannot have duplicate activities. for example Brynes, Kelly in the person field cannot have driving vehicle activity in multiple rows. look at rows 1 and 2 in the Main table.

Thank for the reply

[Haleakala17]

I apologize for not answering your questions, The values in the field team are just the team the person is in. It should be in the person table but the information was given to me in the activity table. The Main table represents the many table. I will get rid of the combo box, I was practicing using combo boxes earlier and I forgot to delete it. Thanks once again and I appreciate your assistance.

[orange]

Many errors with tables arise from using a lookup at the Table level (as mentioned in the previous post and the mvps link).
I understand that English may not be not your first language.

Many table means nothing in this context. The table represent peoplesActivities- the Table represents the ItemsBelongingToAnOrder etc. It could be the Many table in a 1 to many relationship, but I want to know what gets stored in table Main.

A Team can include 1 or more people/person.
A Person can belong to 1 Team only.
A Person can perform only 1 Activity

Team-------consists of--------> Persons

I can't provide much assistance until I understand what you are trying to do.

Preventing duplicates is not a validation expression - at least not here.

[Haleakala17]

if you open the Main table you will see I have people and their associate activities that are stored there. Also the quantity of how many times they perform their activities. By the way, English is my first language however I am a beginner Access user so my database vocabulary is not on a higher level as yours. That said, I am not concerned about the values in the team field for now, my concern is to prevent duplicate (same) activities from being entered for each person in the Main table. The user can have multiple rows with a person with different activities. view example below.

row 1 Roger Walker - fix car engine
row 2 Roger Walker - fix vehicle body.

what I am trying to find a expression that will prevent a user from selecting the same activity for a person. view below. I provided examples of this in the PPTX file I had sent.

Row 3 Roger Walker - Fix bumper
Row 4 Roger Walker - Fix bumper


FYI - a person can be in more than one team.

[orange]

Do you have a version of your database in mdb format after you removed the table lookup in Table main?

1-Can you describe what table Main represents in a line or two?
2-Can you post the mdb?

PS: My comment on your English was initiated by your not answering some of the questions.

[Haleakala17]

The purpose of the Main table is to collect the number of hours (quantity) for employees who are participating in an activity. I took out the lookup information in the Main Table.
Thank you

[Haleakala17]

I added the mdb access file in the compressed folder in addition which has the lookup info. extracted from the Main table.

[orange]

I see you edited the zip file in post 1 at 11:25PM but when I open the file, table Main still has the lookup???

Just a question for you, if Quantity represents Number of hours worked,
why don't you just call the field HoursWorked?? Isn't it easier?

[Haleakala17]

sorry about that, on attachment in the File compressed folder is the mdb file without the lookup info. I will change it to HoursWorked later, thanks for the suggestion.

[orange]

Activity is still a lookup in table Main.

It's late here. Ill look at it tomorrow.

[Haleakala17]

I am terribly sorry for this. I had uploaded the updated mdb in the compressed folder. I made sure the lookup in the activity field is taken out. Thanks and goodnight.