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
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
TTLClasses - Total number of classes an instructor taught since the
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
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?
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.
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:
SELECT MAX(Date) FROM Class WHERE InsID = whateverIdYouWant
SELECT COUNT(ClassID) FROM Class INNER JOIN Instructor On Class.InsID = Instructor.InsID WHERE Class.date > Instructor.CurrentCertDate
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!!
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
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.
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?
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.
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...
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.
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?