Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52

    Unanswered: Counting Classes

    Good Morning Gurus,
    I have three tables in an Access database.
    Instructor table, Class table, Assistant table

    The Instructor table stores information about an instructor and their class status.
    Fields: InsID - Instructors last 6 digits of SSN Primary Key
    Instructor Name
    Address
    SocialSecurityNumber
    OrigCertDate - First time an instructor is certified
    CurrentCertDate - Last time an instructor was certified
    DateLastClass - The date of the last class an instructor taught
    ClassesLast - The number of classes an instructor taught since the
    CurrentCertDate
    TTLClasses - Total number of classes an instructor taught since the
    OrigCertDate

    The Class table stores information about a class they have taught
    Fields: InsID Foreign Key
    ClassID Primary Key Autonumber
    ClassDate - Date of the Class

    The Assistant table stores information about the assistants that participated in that class.
    Fields: ClassID Foreign Key
    AssistID Primary Key Autonumber
    AssistantName
    AssistantSSN
    CertType - A 1 or 2, 1 means the assistant is already a certified
    instructor, 2 means not certified.
    When a new ClassDate is entered in the Class table, an Update query changes the DateLastClass in the Instructor table to that date. I need to create something that will automatically add a 1 to the ClassesLast and
    TTLClasses to keep count of the classes the instructor has taught. I'm thinking a counter but I'm not that experienced.
    Also if a certified Assistant participates in the class, I need to automatically add a 1 to those same fields for their individual record.
    Each Instructor and certified Assistant has a personal record in the Instructor table.
    Too much information? or not enough?

    Scenario:
    An Instructor teaches a class with a CertType 1 assistant.
    (The Instructor has taught 5 classes since his last certification date and 10 classes since his original certification date and the assistant has taught 3 classes since his last certification date and 5 classes since his original certification date) When the class date is entered, I need the Instructors and the assistants number of classes since their last certification date and since their original certification date to increase by 1.
    Last edited by BugMe; 05-12-04 at 13:10.

  2. #2
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I have a couple of things...

    1. Your primary key is duplicate information that is already in the database. I would either make it the full SSN, or create an autonumber field and drop the field with only the last 6 digits.

    2. How familiar are you with Access VBA? Would you feel comfortable writing a procedure on an AfterUpdate of a field to increment all of those numbers by one?

    I hope I can help,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You have TONS of redundant data using your current method...

    DateLastClass, ClassesLast, and TTLClasses are all figures you can derive from the Class table. JS is also right about the SSN. If you are omitting the first number for security reasions, then you may as well go auto-number and keep the SSN seperate all-togehter, otherwise use the whole thing.

    Everything you would like to do is actually already done. You just have to get comfy with a few functions. Here's a few overview examples:

    DateLastClass:

    SELECT MAX(Date) FROM Class WHERE InsID = whateverIdYouWant

    ClassesLast:

    SELECT COUNT(ClassID) FROM Class INNER JOIN Instructor On Class.InsID = Instructor.InsID WHERE Class.date > Instructor.CurrentCertDate

    TTLClasses:

    SELECT COUNT(ClassID) FROM Class INNER JOIN Instructor On Class.InsID = Instructor.InsID WHERE Class.date > Instructor.OrigCertDate



    So you see, you already have all the information you need at your fingertips. No need to reinvent the wheel!!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Go Teddy... havent seen you on here in a while I didnt even think about the other redundant data... thanks for catching my slack.


    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I've been on holiday for my one year anniversary!!

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Ah congratulations!
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52
    JSthePatriot and Teddy,

    Thanks for the quick reply!

    I'll use those suggestions and get back with my results.
    Basically, I was put in a cubicle, handed an Access for Dummies book and told to figure it out. I've created some minor procedures on my own with success but still have alot to learn

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well, you've got a good start, you're just making things too hard on yourself!

    I would also recommend picking up a book on SQL. IMO, most access books don't cover SQL well enough for you to be able to do what you really need. Wizards are crap. Learn what the wizard is doing, then you can get the most out of it.

    SQL in a nutshell isn't too bad...

    But yeah, you have a good structure there, you're simply not making the best use out it.

    Good luck!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52
    Teddy,

    Thanks for the words of support!!
    One question about the current data on the mainframe. For some reason they set all the dates as text data types without template characters. For example, 02242004. Therefore, I have set the date fields in the same manner. Sometimes we upload records to an outside contracted database company and these are their specs. What kind of problems do you think this will create?

    JSThePatriot,
    I'm curious! What did you have in mind for the AfterUpdate event?
    I've written some procedures. They usually deal with auto filling fields. For example, code that will fill in the correct city, county code and county name based on the zip code.


    You guys are wonderful!!! Thank you!
    Tracy

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would use a true date/time datatype as opposed to text. The primary reason is to avoid inconsistant datatypes. The conversion would be relatively simple, just parse out the dates and add a "/". Then set the field's datatype to date/time.

    This is another example where SQL is going to be a fairly powerful tool for you...

    UPDATE yourTable
    SET yourDate = Left$(yourDate, 2) & "/" & Mid$(yourDate, 3, 2) & "/" & Right$(yourDate, 4)

    Some of the main issues you will run into by NOT using a date/time will be immediately evident. For example, which is bigger?

    04242003 or 01012004

    The first one is going to be bigger, therefore if you ask access for the "latest" date, it's going to give you 4/24/2003. If it's an actual date/time field, you can evaluate it as such, and you would return 1/1/2004.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Okay the AfterUpdate()

    It would go something like this.

    Code:
    Private Sub ClassDate_AfterUpdate()
        docmd.openform "formnamehere"
            yourformcontrol.value = yourformcontrol.value + 1
            yourformcontrol.value = yourformcontrol.value + 1
        docmd.close acform, "formnamehere"
        docmd.openform "formnamehere"
            yourformcontrol.value = yourformcontrol.value + 1
            yourformcontrol.value = yourformcontrol.value + 1
        docmd.close acform, "formnamehere"
    End Sub
    Something like the above... Hope it helps you on your way. Teddy could probably give you an SQL statement to do everything I just did :P

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  12. #12
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52
    Good Morning Teddy,
    I barely know anything about SQL. I understand the commands and the syntax fairly well. However, I'm not having alot of success implementing the statements you kindly offered me.
    It's my understanding that SQL statements can be in a query, report or form.
    I've tried entering these statements using the query builder from the record selector of my main form. Is this the wrong approach?

    Thank you!!

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That should be the right approach. Switch to sql view and you should be able to cut and paste some of these examples directly into the query.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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