Results 1 to 6 of 6
  1. #1
    thekruser is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    93

    Arrow Display field as text box

    What I have:


    I have three tables with the following fields (not all inclusive):

    tblPERSONAL
    -EmployeeNumber (PK)

    tblTRAINING_DESC
    -CourseID (PK)
    -CourseName

    tblTRAINING_DATA
    -TrainingID (PK)
    -EmployeeNumber (FK)
    -CourseID (FK)
    -CompletionDate

    What I am trying to do:
    Not all employees have been to all courses (obviously). However, I am trying to include a text box for specific courses on a subform (subfTRAINING). Currently all are displayed in a datasheet.

    My reasoning for doing this? There are literally hundreds of courses available within my organization. Some of which are a one-time deal (complete the course once and you are done) and some require annual recertification. It is for the annual-recertification courses I am trying to do this, as all my organization is concerned with is the most current date.

    The problem:
    Duplicate entries are being created by my clerks in error. For example: John Doe goes to Course 1, which requires annual recertification. When John goes back to Course 1, it is erroneously entered as a second instance of the course instead of just updating the date in the table.

    If Course 1 appeared as a text box and not in the datasheet, the clerk would not be able to create a duplicate entry, thus solving the problem. I hesitate to have Course 1 as a field in tblPERSONNAL, as that would go against normalization rules.

    I am completely open to any help you would be willing to give.

    Thank you in advance for your time and help.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Why didn't you keep the history record? I think it's good to add new entry when the employee take a course or repeat a course.

    If you really want only one record in tblTRAINING_DATA for each people and course, you can create unique index on employeenumber and course id. this will avoid duplicates.

    you should not add a field course id to tblpersonnal table. if the people took 3 courses, how do you put 3 course ids in one field in the table?

  3. #3
    thekruser is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    93
    Quote Originally Posted by weekend00 View Post
    Why didn't you keep the history record? I think it's good to add new entry when the employee take a course or repeat a course.
    My main reason is size. A few thousand emplyees, a few hundred courses...we are talking several hundred thousand records annually.

    Quote Originally Posted by weekend00 View Post
    If you really want only one record in tblTRAINING_DATA for each people and course, you can create unique index on employeenumber and course id. this will avoid duplicates.
    Thank you for the idea. I have implemented it and it has solved my duplicate record issue. I would still like to find a way to show the data in a text box if at all possible.

    Quote Originally Posted by weekend00 View Post
    you should not add a field course id to tblpersonnal table. if the people took 3 courses, how do you put 3 course ids in one field in the table?
    Perhaps I did not explain correctly. I meant add Course 1 as a field with type: Date/Time within tblPERSONAL.

    Thanks again for your reply. I appreciate your help.
    Last edited by thekruser; 09-03-2010 at 03:01 PM.

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    I think the correct way to solve this, is to add an extra table: tblCourseSessions. Your tables could look like:

    tblPERSONAL
    -EmployeeNumber (PK)

    tblTRAINING_DESC
    -CourseID (PK)
    -CourseName

    tblTRAINING_SESSIONS
    - SessionID (PK)
    - CourseID (FK)
    - SessionDate
    (eventually extra fields for place, trainer ect.)

    tblTRAINING_DATA
    -TrainingID (PK)
    -EmployeeNumber (FK)
    -SessionID (FK)
    -Status (for instance: Invited, Accepted, Attended, Exused)


    greetings
    NG

  5. #5
    thekruser is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    93
    I love the idea. I definitely solves the duplicated data. Still will have a huge database after a couple of years, but I can purge old records every so often.

    However, is there not a way to show the data in a text box? Although, this solves the current issue, the text box question above was for more than one reason and I am still looking for a way to get it done. Any ideas? Thank you for the help.

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    to store some random information that will never be used in a search (for instance for personnel, a remark for people like me: 'never disturb before she had her morning coffee'), I have in most of my main tables a "Remarks" field, type memo.
    If you want to add some read-only information from other tables, you can add an unbound textbox and populate it through a DLookup function. However, be carefull, to many DLookups may slow down the loading of the form.

    greetings
    NG

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-05-2010, 02:52 PM
  2. Report control of a field display/no display
    By systems013 in forum Reports
    Replies: 5
    Last Post: 02-01-2010, 09:44 AM
  3. Replies: 3
    Last Post: 10-23-2009, 05:03 PM
  4. Replies: 2
    Last Post: 10-09-2009, 07:34 AM
  5. Replies: 1
    Last Post: 03-02-2006, 06:17 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums