Primary key (1 Viewer)

[sundaram]

I have 2 tables:
1 tblEmp, 2tblTime

tblEmp consist of empid empname where empid is primary key

tblTime consist of empid, empdte, empHours

Here When I am entering data tblTime it should be in on particular date empid and empdate should be allowed and it should not be dublicate date of the particular empid.

I have tried empdte as primary key in the tblTime. But it is not working properly. Please advise. I appreciate your kind reply. I hope i have cleared my query.

 

[gemma-the-husky]

select both empid and empdte - then make the joint selection, the primary key

personally i would make this a secondary key, still unique, and add a further autonumber key.

 

[sundaram]

Dear, gemma-the-husky
I have tried adding autonumber field and empdt
as primary key in tblTime
and tblEmp, empId as a primary key.
I have made a relationship with field -empId.

While entering data in the tblTime. It is showing error msg as: "The changes you requested in the table were not successful because they would create duplicate value in the index, parimary, or relationship. change the data in the fields that contain duplicate data, remove the index, or redifine the index to permit duplicate entries and try again."

In tblTime - I have enter data on daily basis as
Autonumber empId empdt empHours

 

[sundaram]

Dear, gemma-the-husky
I have tried adding autonumber field and empdt
as primary key in tblTime
and tblEmp, empId as a primary key.
I have made a relationship with field -empId.

In tblTime - I have enter data on daily basis as
Autonumber empId empdt empHours
1 1 01-06-09 8
2 4 01-06-09 9
3 2 01-06-09 8
4 1 02-06-09 7
While entering data in the tblTime. It is showing error msg as: "The changes you requested in the table were not successful because they would create duplicate value in the index, parimary, or relationship. change the data in the fields that contain duplicate data, remove the index, or redifine the index to permit duplicate entries and try again."

My question is that while entering the data it should accept empid field and empdt on a specified date, it should not accept duplicate date.

secondly it should accept empid on other dates or at on the same date same empid for all empid's.

Please help me out.

 

[gemma-the-husky]

and tblEmp, empId as a primary key.

this is wrong - because you are making the employeeid the primarykey, you can only have one record per employee

you HAVE to select BOTH the empid AND the empdate, and make BOTH of them the primary key. You should see the little key symbol against BOTH the fields

(or make them both a separate key, with allow duplicates false)

 

[sundaram]

Dear gemma-the-husky,

As per your adivse I have made empid as primary key in table tblEmp AND the empdate in table tblTime.

empdate as primary key (with allow duplicates false) which is not accepting in table design.

"It is showing that Removing or changing the index for this filed would require removal of the primary key."

Please suggest.

 

[sundaram]

Still it is giving trouble please help me out.

 

[gemma-the-husky]

i dont think you are trying to construct the primary key, in table tbltime in the way i suggested

read the thread again

you HAVE to have a COMPOUND (2 fields) primary key in the tbltime table

 

[sundaram]

Dear Mr. gemma-the-husky
Thanks for your reply

I am have set the empdte as primary key in the table tbltime.
General setting tab from drop down(No, Yes Duplicates OK & Yes (Duplicates OK). I am setting to Yes Duplicates OK. Then it is showing error "It is showing that Removing or changing the index for this filed would require removal of the primary key."

And in table tblEmp consist of empid empname where empid is primary key

Iam i not able express my question you properly or please let me know.

Please help me!