Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Haleakala17 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    36

    Create Expression in Validation section Prevent Duplicate values in a Field

    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
    Attached Files Attached Files
    Last edited by Haleakala17; 09-11-2012 at 10:27 PM. Reason: I have attached mdb file

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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,

  3. #3
    Haleakala17 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    36
    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?

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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
    Attached Thumbnails Attached Thumbnails LookupInTableDesign.jpg  

  5. #5
    Haleakala17 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    36
    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

  6. #6
    Haleakala17 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    36
    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.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  8. #8
    Haleakala17 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    36
    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.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  10. #10
    Haleakala17 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    36
    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

  11. #11
    Haleakala17 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    36
    I added the mdb access file in the compressed folder in addition which has the lookup info. extracted from the Main table.

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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?
    Attached Thumbnails Attached Thumbnails LatestDatabaseInfo.jpg  

  13. #13
    Haleakala17 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    36
    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.

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  15. #15
    Haleakala17 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    36
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dcount prevent duplicate
    By ayamali in forum Programming
    Replies: 20
    Last Post: 04-16-2013, 09:31 PM
  2. Replies: 6
    Last Post: 06-20-2012, 06:42 AM
  3. Replies: 7
    Last Post: 12-30-2009, 11:03 AM
  4. Prevent Duplicate Values on Combination of Two Fields
    By LornaM in forum Database Design
    Replies: 8
    Last Post: 05-05-2009, 11:16 PM
  5. Replies: 0
    Last Post: 09-25-2006, 07:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums