Preventing Duplicates

Preventing Duplicates (Full Version)

All Forums >> [Web Development] >> ASP and Database

Message

SerenityNet -> Preventing Duplicates (10/22/2002 0:39:42)
I have an Access DB. There are two tables that have a relationship, TimeCard and Tasks. TimeCard contains three fields: ID, Person, Date. Tasks contains four fields: ID, Category, Time, Notes. I simply want to make it so that there can only be one time card per person per date. I' m stumped. [:@] (I enter the information on a TimeCard form with a Tasks subform. The Tasks subform allows many tasks per day.) Help! (and thanks in advance) Andrew

J-man -> RE: Preventing Duplicates (10/22/2002 1:21:06)
What is you promary key (if any) in each table and why are those the primary keys?

SerenityNet -> RE: Preventing Duplicates (10/22/2002 1:49:07)
The ID field in each table is the primary key. The Task table' s ID is primary simply to keep each entry unique when referenced on the TimeCard table. I' m not sure why the TimeCard table' s ID needs to be unique. What I want is for the combination of Employee and Date to be unique. Below I' m pasting the actual tables and relationships in my DB. I still don' t have a clue. [:o] [image]http://www.frontpagewebmaster.com/upfiles/2898/Ca82134.jpg[/image]

J-man -> RE: Preventing Duplicates (10/22/2002 2:05:02)
hang on, let me create your situation on my computer, your want seems twisted in your situation for some reason ?

SerenityNet -> RE: Preventing Duplicates (10/22/2002 2:16:52)
Thanks J-man. I' m heading to bed now, but I' ll check for your help in the morning. And again, so you don' t waste your time, I just want to: Have an employee table. Have a work code (category) table. Then I want a time card, where each employee for a given date can make multiple entries of tasks (task category, hours worked, & work description). I only want one time card per employee per date. I really appreciate the help. Andrew

J-man -> RE: Preventing Duplicates (10/22/2002 2:34:35)
make the prime key of the timesheet table Id/Date

J-man -> RE: Preventing Duplicates (10/22/2002 2:52:43)
hmm... just a thought ... are you alseep yet ? If the primekey is Id/Date then Access will not allow you to have more than one record for the same Id/Date combination. I think you will need the date in the tasks table as well. What you have is the classic parent/child relationship, that is you have one timecard record per day (the parent) and one of more task records for that day (children records). You need to be able to ' relate' the children to the parent. So if the unique id of each parent is Id/Date, then if the children also contain id/date you can identiy which children ' belong' with each parent.

SerenityNet -> RE: Preventing Duplicates (10/22/2002 10:23:37)
Well this works, but it isn' t friendly. The user has to enter the date again on every task record. It seems like there should be some way of avoiding that. Also, when I create a new TimeCard record then the subform carries over the values from the previous TimeCard' s subform. It is not saved, but it is very confusing. I' m still open to ideas? Andrew

hhammash -> RE: Preventing Duplicates (10/22/2002 14:59:11)
Hi, You have to make the ID and Date as primary keys in the table. The combination will prevent the following: John Smith 10/22/2002 (OK) John Smith 10/22/2002 (Not OK will not be accepted) John Smith 10/23/2002 (OK) Best regards Hisham

SerenityNet -> RE: Preventing Duplicates (10/22/2002 15:42:25)
Right now I have set the table relationships as in the illustration below. The ID and Date are the primary keys in the time card table. The combination does the following: John Smith 10/22/2002 (OK) John Smith 10/22/2002 (Not OK, is not accepted) - This is good. John Smith 10/23/2002 (OK) Jane Smith 10/22/2002 (Not OK, is not accepted) - This is not good. I want to constrain so that each employee can have one (and only one) time card per date, so I' m still struggling. [image]http://www.frontpagewebmaster.com/upfiles/2898/Vt58835.jpg[/image]

hhammash -> RE: Preventing Duplicates (10/22/2002 16:07:45)
Hi, ID and Date are not the fields in your illustration. Can you please give me the same name fields as in your database and from which table? Jane should be accepted. Wait a sec. Jane Smith " Is it a full name or first name and last name fields" ? I meant Jane is a field and smith is a field? Best regards Hisham

SerenityNet -> RE: Preventing Duplicates (10/22/2002 18:53:12)
Thank you both so much for the continued effort to help me out on this issue. Below is my current setup. As you see, Jane Smith isn' t really a name, but a nameID (for the first/last combination). There shouldn' t be any conflict between John Smith and Jane Smith. Andrew [image]http://www.frontpagewebmaster.com/upfiles/2898/He97044.gif[/image]

hhammash -> RE: Preventing Duplicates (10/23/2002 0:30:34)
Hi, How you are you combining both first and last name fields? What happens if you make the combination field as PK with Date? How are you filling the data in the table? Is it through first and last name fields or a combination of both? If the combination is derived from two fields, you can make both fields in addition to date primary keys. Make FirstName and LastName and Date as primary keys. Whic field in the structure you posted is the combination of both? Anyway, your problem is very easy to solve once we know wherethe bug is. Regards Hisham

SerenityNet -> RE: Preventing Duplicates (10/23/2002 2:15:57)
Whoa! Now I' m really going around in circles! [:j] I' m sending you a private message with a hyperlink to a copy of the DB. If you have the time to look at it then that would be great. I have to hit the sack, so I can be half-way alert at work tomorrow. Let me know what you think - and thanks again, Andrew

hhammash -> RE: Preventing Duplicates (10/23/2002 3:02:46)
Hi, Thanks Andrew, I received the database. I will get back to you. Best regards Hisham

hhammash -> RE: Preventing Duplicates (10/23/2002 5:59:23)
Hi, Here you go. I found a solution that will not allow the same person more than once on the date date. In the table desing create an index and call it NoDups, then on the right select EmployeeID, the Select WorkeDate, then clik in NoDups field then down select Unique. See below Regards Hisham [image]http://www.frontpagewebmaster.com/upfiles/5992/Pn36484.jpg[/image]

hhammash -> RE: Preventing Duplicates (10/23/2002 6:00:14)
Hi, See the data entered. [image]http://www.frontpagewebmaster.com/upfiles/5992/Ay75134.jpg[/image]

SerenityNet -> RE: Preventing Duplicates (10/23/2002 14:58:31)
Okay. This is great! I had to add an autonumber taskID column to the subform and make a one-to-many relationship from the TimeCardID on the timecard table to the TimeCardID on the tasks table. Otherwise, I couldn' t have more than one task per time card. But now it works fine. One last question (but no promises): When I create a new record, all the fields are blank. This is good. But as soon as I select an employee then the subform fills out with the values from the last record viewed. How can I keep this from happening? The subform should remain blank until users start putting in more data. Thanks once again, Andrew

hhammash -> RE: Preventing Duplicates (10/23/2002 15:44:51)
Hi, Go to the form properties, Click All tab then go to, Data Entry property and select Yes. Regards Hisham

SerenityNet -> RE: Preventing Duplicates (10/23/2002 16:24:54)
Hi, When I do that, it works, but if I leave the record and return then I don' t see the previous entries for that time card. Any other ideas? Andrew

hhammash -> RE: Preventing Duplicates (10/23/2002 17:11:05)
Hi, Why don' t you remove the subform that displays related records. Regards Hisham

SerenityNet -> RE: Preventing Duplicates (10/23/2002 18:30:09)
I can' t. It is the subform where they enter their tasks.

hhammash -> RE: Preventing Duplicates (10/24/2002 7:54:04)
Hi, Ok, Remove any kind of indexing in the fields. This way you will display the records as entered. If the fields are indexed, the indexing will be done automatically, the record will go in it' s place in the index. Just remove the field indexing in the table design. Hope it helps. Hisham

SerenityNet -> RE: Preventing Duplicates (10/24/2002 10:44:33)
Ugh! It is still doing it. I removed all the indexes from the tasks subform. I can' t remove all the indexes from the parent time card form, since that is how we avoided having duplicate time cards per employee per date. I' m at work and can' t upload the latest version to the web (at the hyperlink I sent you earlier). But the version there does the same thing. I' m not sure what to do next. Andrew Never mind !!! It is solved !!! I originally used the wizard to create this DB, then extensively modified. I re-created the DB and made no changes. This one too has the problem. So I scrapped both and created the DB from scratch. It works just as I would expect it to work - when I add a new record then the subforms initially appear blank. There must be something in the VB script created by the wizard that causes this problem. Anyway, now it is solved. Using the tips you gave above, I can constrain the time cards to one card per day per user. Thanks for all the help. When I get the thing totally done then I' ll make another post where people can grab a copy. It seems there is some interest. Thanks again, Andrew

hhammash -> RE: Preventing Duplicates (10/25/2002 7:02:42)
Hi Andrew, Good to hear that. Hisham

Page: [1]

Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI

9.277344E-02