Create Expression in Validation section Prevent Duplicate values in a Field

[orange]

I have taken a copy of your database and made adjustments to it as follows:

Created new tables beginning with tbl.... using Create.. queries using your original tables.
Added unique Id fields (autonumber and PK) to each new table I created.
With tblPerson, I have also divided Person into atomic fields FirstName and LastName


Created Junction tables tblPersonWorkActivity and tblTeamOfPersons to resolve many To Many issues.

In tblPersonWorkActivity, I have added fields WorkDate and HoursWorked because it seems reasonable to me that a Person can Work an Various Activities BUT only One Activity at a time. And since you are concerned with the Quantity (HoursWorked) those Hours relate to a Person and an Activity on a Specific Date.

I have added a couple of Select queries to show who is on which team and How many people are on Each Team.
I have also added unique compound indexes on the 2 junction tables to prevent duplicate records.

I have attached a jpg showing your relationships and the revised relationships that I think are relevant to your issue.

I have attached a zip with the revised database and some screen shots.

I hope this is helpful. Good luck with your project.

[Haleakala17]

Orange,

I am speechless, thank you very much for structuring my data. I noticed you have placed tbl labels before each table name . . . this is an excellent idea. I am a novice in using Access so I will have to spend time looking at the relationships and other rules you used to structure the data. FYI - I used unique identifiers in the past however the data I am working with does not have any unique IDs. what makes it more challenging to me is that spelling convention of some peoples names changes every 2 months. We had a data analyst who recently resigned for my company (now I know the reason why) and I am taking over in compiling and structuring the data.

I noticed you had seperated the first and last names in the tblPerson table. I had used the concatenate function in Excel to seperate names and bring it back into Access. I had viewed the queries in design view and saw how you queried the fields from each table as well.

I will explore more into the Access file and I want to tell you how much I appreciate your help. As of now I am learning how to improve structuring data into tables and building relationships. Since viewing your modifications in my database, I know I have a lot of studying to do to be proficient in Access.

Haleakala17

[orange]

You're very welcome. I'm glad to help.

I think the key to database is to spend the time to understand the issue and the data involved. Getting the tables designed to represent the business is not necessarily easy, but well designed tables and relationships make all the other things much easier.

Too many people jump into forms or queries and get mired down in syntax issues and concoctions trying to get data out of non-normalized,poorly structured (not designed) tables.

Where are you located?

PM me if you have questions and I'll try to help/respond.

Good luck with your project.

PS: Here's an article that is well worth reading.
http://forums.aspfree.com/attachment...achmentid=4712

[Haleakala17]

Thanks once again for your support and the article. I will read the article tomorrow since I had a long day at work today. I was experimenting with the Duplicate queries and found a way to search for each person who has the same activity in multiple records. It looks like I am going to learn more Access techniques since I will be working with this program.

I really appreciate your help.

Haleakala17

[Haleakala17]

I also marked this thread as solved and I have selected an excellent rating. Thanks.

[Haleakala17]

If there is anything I can do for you, just send me a reply. By the way I am currently residing in Morgantown, WV.