Query to find select columns in several tables....Help!

  • Thread starter Thread starter Kimberly3626
  • Start date Start date
K

Kimberly3626

Here's the situation: I have 22 personnel. Each is in a different category
(Enlisted Air Force, Enlisted Army, Officer Air Force, Officer Army) Each
category has different training requirements. Please note that in my
Personnel Table, each person is actually assigned to one of those categories.
(Hoping that it might be in some way helpful to finding a solution)

What I've done: I created 5 Tables.
Table 1: Personnel Data. The key field is Social Security Number. It does
include a dropdown selection of the 4 categories mentioned above.
Tables 2, 3, 4, and 5: Each one is a different category of training. I
entered the field headings as the course titles, with Yes/No to track
training completion. I did use an ID index, but all tables are linked via
SSN to the Personnel Data table.

I 'assumed' that when I maximize the little '+' sign in the Personnel Data
table, it would show me the record in either of the 4 training tables that
had that person's SSN in it, and only the record from the specific table. I
was wrong. It is requiring that I only pick one table to link to the entire
personnel table. The problem with this is that then I lose the other 3
completely different training requirements tables!

There is not SO MUCH training (maybe 20-25 courses in total) that I cannot
consolidate into one table. The problem is that I need to know what is
required in addition to what is completed. (maybe to a 'training course 1
required, training course 1 complete?) I thought by doing it this way, the
SSN in the table would require that training for that individual, and the
yes/no within the table would indicate if it were complete or not.

How do I get my data back out?!?!?! I'm hoping for something like this:
SSN1, Last Name1, First Name1, List of required courses and completions
SSN2, Last Name2, First Name2, List of required courses and completions

I though a query might work, but I'm only getting results with ALL of the
data or only the ones completed by the individual (yes checked)
 
You should start with 3 tables.

Table 1: Personnel

Table 2: TrainingRequired

Table 3: Courses - with info on the various training requirements. It might
include a field about which catagory that the training is for.

The TrainingRequired would have the PK from the Personnel table and the PK
from the Courses table. It would also have something like a DateComplete
field.

That way you can match up the person to the training required and see if
they completed it. You probably need to consider how to handle things like
recurring annual training and all that fun stuff.

A crosstab query will do the trick once you have the tables set up properly.

BTW: I was a member of the first catagory for 24 years. Normally I complain
about SSAN's being used as PKs, but in the US military works that way. Just
make sure that the database is at least password protected to keep the wrong
people out.
 
Thanks for the quick reply! And kudos for the 24 years. I'm 10 in now,
still fighting to work smarter not harder while trapped in an Army unit!

I'm gonna attempt to paraphrase what you wrote to see if I've got it correct.

(I renumbered your tables so I could wrap my brain around it from step one
to three...I'm new to access 2007 as well...so I'm pretty fried this
afternoon)

Table 1: Still Personnel Data: PK is ssn. Still includes the category
(enlisted/officer)
Table 2: Courses: Fields: PK (autonumber), Course Title (filled in with
each course required regardless of who it's for), Category (based on which
category each course applies to) <---this will allow me to view status of
training based on categories. For example: "Hey...what's the status on
enlisted PME?" Hopefully, I can spin a quick query and pull it via the
category association.
Table 3: TrainingRequired (or completed): SSN, CoursesPK, Complete (yes/no
option)

I'm gonna have to try this out to see how it shows up in my little + sign.
I need to do it to see it. (yes, that makes this that much harder)

Thanks again!
Kim
 
Back
Top