Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    22

    Unanswered: tblDesign --no duplicates

    am dealing with 2 tbls 1-m;
    tblEmpl(empID(pk), Fname,Lname, phon#) &
    tblTimeReport(TimeReport(pk), empID, Date, startTime, EndTime)

    how can i avoid 2 duplicate on tblTimeReport; i mean an employee can come & work the same day at differnt time, but i can enter same day, same times which is duplicate?

    am using acess so i cant use trigger, but how can i mk empid, date, stTime & end time unique?
    i added PK to all this col..didnt work.
    under tbl design on index.. i had yes, no duplicate...but still it allows duplicate value..
    any suggestion

    thanks

    mark

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    How is the Date field populated in tblTimeReport? If you are using
    the =Now() function on your form, versus having the user key in a date,
    you could end up with "10/07/02 12:31pm" vs "10/07/02 4:18pm".
    As far as access is concerned, that's 2 different values, and your PK
    constraints are met.

  3. #3
    Join Date
    Nov 2002
    Posts
    22
    NO someone will collect the papers one time then enter latter during the weekend....
    so any other way to go around?

  4. #4
    Join Date
    Jul 2003
    Posts
    26
    does changing the value of indexed to Yes(No Duplicates) in your table setup for each of the Field Names help?
    Life's a bowl of punch, go ahead and spike it.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Let me make sure I have this straight...
    You want to make sure that duplicate:

    empID, Date, startTime, EndTime

    are not entered to tblTimeReport?

    What format is in your table design for
    Date
    startTime
    EndTime

    Is your user keying the date in manually, or does
    your form autofill this field?

  6. #6
    Join Date
    Nov 2002
    Posts
    22
    all are in dateTime format. User types in dates, time etc. since they want to have manual paper work as back up. And they will be loading data to access at the end of the wk.

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I created a table, like yours, with the following fields:

    TimeReport AutoNumber-PrimaryKey
    EmpId Text
    Date Date/Time
    StartTime Date/Time
    EndTime Date/Time

    Added an index called DateEmp containing the EmpId,Date,StartTime, and EndTime; and set it's Unique property to Yes. Worked like a charm.
    Is that how your table is set up?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •