Query or Report?

[kruseanderson]

I am trying to obtain information from my database but not sure whether to do it in a query or a report. Either way I am having trouble getting what I what displayed.



My database is for tracking CPR instructors and their classes. I have the database designed and working the way I want it but I need to be able to check one thing before renewing an instructor. Per American Heart Assoc. the instructor has to teach a minimum of 4 classes from the date they obtained their instructor status. I have this date saved as a field in the InstructorT. I also have a from based off query results to show each class that an instructor teaches.

What I need is something that pulls the date from the instructor's expiration date field and counts the number of classes they have taught between then and (today). If that count is less than four I want the record displayed.

I am new at access and only have what little programming I have picked up from trying to design my database.

Thank you for any help.
Major

[rpeare]

Let's assume your main table is called

tblMain
it contains:
MainID (the PK of your instructors)
MainDate (the date of your instructor's certification)

Your sub table is:

tblSub
it contains:
SubID (PK of the table)
MainID (FK to your instructor table)
SubDate (date of the class taught)

this query would do what you want:

SELECT tblMain.MainID, tblMain.MainDesc, tblMain.MainDate, Sum(IIf([subdate]>[maindate],1,0)) AS ClassesTaught
FROM tblMain LEFT JOIN tblSub ON tblMain.MainID = tblSub.MainID
GROUP BY tblMain.MainID, tblMain.MainDesc, tblMain.MainDate
HAVING (((Sum(IIf([subdate]>[maindate],1,0)))<4));

Assuming you are only keeping their most recent certification date.

The query would be slightly different if you have multiple certification dates.

[kruseanderson]

TrainingCenter - Copy.zip

I tried using your code, modifying it to fit my names, but it added (sum) each class done on a given date. It is in Query1. Not quite what I was trying to get.

I did attach my database so you can see it. I probably have a lot of redundant forms or queries but it is working for me currently.

Just need the new query.

[rpeare]

I can't view access 2010 files. If you can convert it to something prior I can take a look.

[kruseanderson]

I have some calculated fields in my tables that wont let me convert. They are to combine first and last names. I will do some research on how to change this and repost it.
Thank you

[rpeare]

Get away from using calculated values and multi value fields in your table. They are a terrible addition to 2010 and the developers should be shot for including it!

If you have a field that's firstname and a field that's lastname it's very easy to concantenate them in any report or query and it doesn't need to be calculated at all.

[kruseanderson]

TrainingCenter - Copy.zip

Ok I removed the calculated fields and got everything working except the 'GoTo' on 3 forms. They are CPR Instructors + R, ACLS Instructors + R, and PALS Instructors + R. I changed everything the same as the other forms but these are not working.

Thanks
Major

[kruseanderson]

I was able to "fix" the 3 forms that were not working. Although I couldn't find the actual problem, I was able to copy a working version of it and change the source queries to display the correct information.

[rpeare]

None of your buttons work on this copy but I don't really need them to.

Does an instructor get credit for teaching a class if they are NOT the lead instructor.

For instance on your record 11 you have a lead instructor and 3 assisting instructors. Do all 3 assisting instructors get credit for having taught a class?

You are only storing expiration dates of your certifications, not the 'start' date of the certification so unless these certifications all have the same 'valid' period (1 year?) you'll have to do more setup in your query.

You can do what you want if you can answer these questions but I have more comments on your database.

You have

BLS EXP DATE
ACLS EXP DATE
PALS EXP DATE
BLS TCF EXP DATE
ACLS TCF EXP DATE
PALS TCF EXP DATE

and you have a checkmark for each of these as well. This is not a normalized structure which is going to make it harder on you in the long run.

What you really should have is your instructor table with instructor related items (up to AHA instructor ID or possibly alignmentform)

Then have a separate table for the certifications that person holds with their startdate/enddate OR just store the end date, but have another table that has a list of the certifications available and the length of time those certifications are good for. Then you do not need the checkboxes (you don't really need them now you could deduce it if they had an expiration date that's greater than today).

Some general notes on your database.
Do not use spaces in your object names, it will make things harder on you in general. I use an _ (underscore) wherever I want a space in a field/object name
Do not use special characters other than _ (underscore) in your field/object names you are using a forward slash (\) in at least one spot and those kind of characters will make it more difficult to work with your database.
Last and probably most importantly I would try to normalize your database

You currently have 3 tables you're going to have a lot of 'empty' areas of your tables and it will make pulling information a lot more tedious than it should be.

Break your instructor table into instructor related information (as I said above) and a table for the certifications they hold, then you only need to store the certification type and the expiration date.
Create a table with the different types of certifications and the length of time those certifications are valid.
Break student into two parts. One table should only have student related information on it. (similar to the instructor table) (first name, last name, department, city, state, zipi, phone etc). The second should be the classes taken and results, the classes taken should be a link to your RosterT table in this table you should store the pass/fail result and the score (though I think you can tell whether they pass by the score you enter so the yes/no checkbox is kind of redundant). I would also assume that the course completed is redundant because if they do not have a score they haven't completed the class?

At any rate this is a lot to take in for your DB but you'll save yourself aggravation if you try to normalize the design before you go much further.

[kruseanderson]

Thank you for your evaluation of my database. If I understand correctly I should have 6 tables (Instructors, certificates, certifications, rosters, students, and results)?

Yes, An instructor gets credit for teaching or assisting. This was hard for me to figure out how to combine but I finally did it with a UNION query.

I am only storing the expiration date of the certificates. They are good for two years.

Not sure why the buttons don't work. They do on my copy but I have not tried this on any other computers or versions of access.

I had all the spaces out of the names at one time. I must have changed them in a form and it transferred back into the tables. I will fix that again.


I understand breaking the student table into two for a normal database but in this one each student record will be one time use. In fact student records and rosters older than 3 years could be archived out.

I will see if I can split that stuff up and make it work.

[kruseanderson]

have another table that has a list of the certifications available and the length of time those certifications are good for.

How would I list the valid period? What data type?

Some general notes on your database.
Do not use spaces in your object names, it will make things harder on you in general. I use an _ (underscore) wherever I want a space in a field/object name
Do not use special characters other than _ (underscore) in your field/object names you are using a forward slash (\) in at least one spot and those kind of characters will make it more difficult to work with your database.

I did have those spaces and such out. They are there in "caption" but not in the actual field name.

Last and probably most importantly I would try to normalize your database
Break your instructor table into instructor related information (as I said above) and a table for the certifications they hold, then you only need to store the certification type and the expiration date.
Create a table with the different types of certifications and the length of time those certifications are valid.

Trying to do this.