|
| |
|
|
SerenityNet
Posts: 1391
Joined: 6/12/2001
From: Allen, TX, USA
Status: offline
 |
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
_____________________________
There are some ideas so preposterous that only an intellectual will believe them. - Malcolm Muggeridge
|
|
|
|
J-man
Posts: 936
From: Canada
Status: offline
|
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?
| |
|
|
|
J-man
Posts: 936
From: Canada
Status: offline
|
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
Posts: 1391
Joined: 6/12/2001
From: Allen, TX, USA
Status: offline
|
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
Posts: 936
From: Canada
Status: offline
|
RE: Preventing Duplicates - 10/22/2002 2:34:35
make the prime key of the timesheet table Id/Date
| |
|
|
|
J-man
Posts: 936
From: Canada
Status: offline
|
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
Posts: 1391
Joined: 6/12/2001
From: Allen, TX, USA
Status: offline
|
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
Posts: 1064
Joined: 8/19/2002
Status: offline
|
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
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
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
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
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
< Message edited by hhammash -- 10/23/2002 12:31:06 AM >
| |
|
|
|
SerenityNet
Posts: 1391
Joined: 6/12/2001
From: Allen, TX, USA
Status: offline
|
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
Posts: 1064
Joined: 8/19/2002
Status: offline
|
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
| |
|
|
|
SerenityNet
Posts: 1391
Joined: 6/12/2001
From: Allen, TX, USA
Status: offline
|
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
Posts: 1064
Joined: 8/19/2002
Status: offline
|
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
Posts: 1391
Joined: 6/12/2001
From: Allen, TX, USA
Status: offline
|
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
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Preventing Duplicates - 10/23/2002 17:11:05
Hi,
Why don' t you remove the subform that displays related records.
Regards
Hisham
| |
|
|
|
SerenityNet
Posts: 1391
Joined: 6/12/2001
From: Allen, TX, USA
Status: offline
|
RE: Preventing Duplicates - 10/23/2002 18:30:09
I can' t. It is the subform where they enter their tasks.
_____________________________
There are some ideas so preposterous that only an intellectual will believe them. - Malcolm Muggeridge
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
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
Posts: 1391
Joined: 6/12/2001
From: Allen, TX, USA
Status: offline
|
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
< Message edited by SerenityNet -- 10/25/2002 2:12:05 PM >
_____________________________
There are some ideas so preposterous that only an intellectual will believe them. - Malcolm Muggeridge
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Preventing Duplicates - 10/25/2002 7:02:42
Hi Andrew,
Good to hear that.
Hisham
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
