Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Design to prevent duplicates? (AXP 10.2627.2625)

    In an Attendance database, i present a form for entry of "Today's Absences" in which the child's name is a lookup.

    This works well until such time that a record is changed on the same day: a kid marked 'absent' will show up, so his record must be changed to 'tardy'.

    This could be done easily on the form, by searching for the kid and fixing the record. BUT, what the users are doing is just making a new record, so now there are both an absent and a tardy record for the same kid for the same day.

    Which is the better design choice: silently deleting the earlier record? Giving a message to the operator that there are duplicates? Something else?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design to prevent duplicates? (AXP 10.2627.2625)

    The way I have handled similar situations is OnExit the name text box (or drop down), check that there is not all ready a record for that person on that day. If there is, I display it for modification. If there is not, I add a new record. Will that work?

  3. #3
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design to prevent duplicates? (AXP 10.2627.2625)

    It might work, but my find duplicates query for the day only works partially !?The lLookup is the result of a sorted query, and i have had problems with these before. i will work on the query and get back to you.

    When you say you display the record, do you mean you display it in another modal form, or do you delete the current record and move to the previous entry?

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design to prevent duplicates? (AXP 10.2627.2625)

    Does each student have a uniqueID in a "students" table? If so, can you create a compound index for studentID and date in your "absentee" table, and then trap for the error when a duplicate is created? The error trap could cancel the Insert action and find the already-existing record.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design to prevent duplicates? (AXP 10.2627.2625)

    yes, each child is unique. i will look at the table and see how i can fit this scheme into place.

    When you say "find", do you mean "move the active record to the found one"?

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design to prevent duplicates? (AXP 10.2627.2625)

    Your reply makes it sound as if the duplicate record is first created. I am suggesting (as Jackson does) that first you determine whether a record for that StudentID and Date exists and only insert a new one if it does not exist. If it does exist, you can display the existing record for modification (that is, for example; the user can change Absent to Late and enter an arrival time).
    This will eliminate trying to find duplicates or creating, then deleting records. Is that reasonable?

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design to prevent duplicates? (AXP 10.2627.2625)

    Perhaps reasonable but I am winding myself around the axle here.

    The design i have is a continuous form. Each line has date, name, grade, absencetype, notes. Date defaults, and is not a tabstop. Name is a lookup. Grade is filled in.

    In the proposed design, ... I need a lookup for the child first in todays absences. If the kid is there, show that record. If he's not there, i should present a blank record? This sounds more like a master-detail form design than continuous form. Am I understanding you guys (finally)?

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design to prevent duplicates? (AXP 10.2627.2625)

    Yes - that's what I mean -- cancel the insert, and go to the record of interest.

    following up on your reply to Thomas' comment -- Yes, I would use at it as a parent-child form. The parent would be the particular date, and the child form would be, well... the children ;-) sorry, couldn't resist....

    You can use your continuous form as the child subform.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  9. #9
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design to prevent duplicates? (AXP 10.2627.2625)

    Thanks for help and the pun <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

Posting Permissions

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