Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I don't even know if this is possible, but I figure if anyone would know, the Woody's Loungers would.

    I have a form used to tracking training. Field 1 is [TrainingElement], a drop-down list feed from a table. Field 2 is [RevCal] is used to input the elements revision level or calendar year. Training is completed when training documents are revised, or for "non-document" type training, on a calendar year.

    As the records "grow" there is the possibility that any training for say a new employee, could be entered in the wrong revision level or calendar year for a particular training elements. So to summarize, there can be multiple records for a particluar "training element" but combined with the revision level/calendar year, there should only be (1) record for each.

    What I am wondering is if there is a way to compare the [TrainingElement] and [RevCal] fields, and when a new record is added for the same "Training Element", but with a new (higher) revision level or calendar year, the previous combination (record) is locked, and cannot be changed.

    Hopefully I have not made this too confusing.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You mention that the RevCal field can contain a revision level or year. How does that work? How do we know whether 1987 is a revision level or a year?

  3. #3
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    The revision levels are indicated in the format of 1.0, 1.1, 1.2, etc,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Is RevCal a text field?

  5. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I have it set as a text field, due to the decimal in the revision level. I tried a number field but it either wanted the decimal in every entry, including the calendar year, or not use a decimal at all. So I took the easy way out and made it a text field.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It would have been better to use two fields, one for the revision level and another for the year, but I'll see if I can come up with something. Might take time, though.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    This system is just now starting to be used, so making changes is not an issue. If it works better to seperate the field as you suggest, I can that.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Using two separate fields would be better, because (1) you shouldn't store dissimilar data in the same field, and (2) it makes comparing values easier.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Okay, I can make that change. I'm assuming then I want to also make the revision field, a number with (1) decimal place, and the calendar year a number only (no decimal places).

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, revision could be a number (single precision) field, with the formatting set to display one decimal place, and year could be a number (long integer) field.

    Could you attach a stripped down and zipped copy of the database when you've done that?
    • Make a copy of the database and work with that.
    • Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    • In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    • Remove or modify data of a confidential nature.
    • Perform a compact and repair (Tools/Database Utilities).
    • Make a zip file containing the database.
    • Attach the zip file to a reply.

    That will make it easier to investigate what's needed.

  11. #11
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Will do. I'll make the change and then send it along.

  12. #12
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Here you go. Thanks Hans!
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by SmokeEater View Post
    Here you go. Thanks Hans!
    Before we get to your problem, we need to fix some problems.

    1a) The table tblTraining has a text field TrainingElement. Instead of this, you should have a field TrainingElementID that links to tblTrainingElements.
    In the attached version, I have added this field and created an update query to populate it. Eventually, the TrainingElement field should be removed, but I left it for the moment so that you can see the result.

    1b) During step 1a) you'll find out that there is a value in the TrainingElement field in tblTraining that doesn't have a match in tblTrainingElements: "Awareness overview". This means that I couldn't fill in the TrainingElementID field for that record. You will have to solve that.

    2a) The table tblTrainingSub contains text fields TrainingSubName and TrainingSubVerify that correspond to employees and trainers, respectively.
    Therefore, they should be replaced by number fields EmpID and TrainerID.
    I have added these fields and created update queries to populate them. Again, I left the old fields in the table for now.

    2b) There is one record without a TrainingSubName.

    3) There are several values of TrainingSubID in tblTrainingSub that do not correspond to a TrainingID in tblTraining, so these are "orphans". You will have to add the corresponding records in tblTraining otherwise the sub records will remain in limbo. See the query "Missing TrainingIDs".

    When you have corrected the problems, it will be possible to create relationships between the tables and to enforce referential integrity; this will prevent the creation of "orphan" records. I have done this except for the relationship between tblTraining and tblTrainingSub because of 3)

    You will have to go through the queries and forms to see how the above affects them.

    [attachment=86259:Training.zip]
    Attached Files Attached Files

  14. #14
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for the changes. I will make the ones you mentioned and then get back with you.

  15. #15
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I made the changes you listed, but am not clear on how to get the actual values back to the TrainingRecord form. With those fields now gone, should I be pointing to the "ID" fields? I created the relationships in the queries but again, not sure how to get the actual field information, and not just ID numbers, into the underlying tables, and to the form.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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