If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query to find select columns in several tables....Help!
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) |
#2
|
|||
|
|||
Query to find select columns in several tables....Help!
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. How do I get my data back out?!?!?! 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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kimberly3626" wrote: 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) |
#3
|
|||
|
|||
Query to find select columns in several tables....Help!
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. How do I get my data back out?!?!?! 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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kimberly3626" wrote: 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) |
#4
|
|||
|
|||
Query to find select columns in several tables....Help!
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. How do I get my data back out?!?!?! 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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kimberly3626" wrote: 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) |
#5
|
|||
|
|||
Query to find select columns in several tables....Help!
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 "Jerry Whittle" wrote: 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. How do I get my data back out?!?!?! 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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. |
#6
|
|||
|
|||
Query to find select columns in several tables....Help!
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 "Jerry Whittle" wrote: 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. How do I get my data back out?!?!?! 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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. |
#7
|
|||
|
|||
Query to find select columns in several tables....Help!
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 "Jerry Whittle" wrote: 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. How do I get my data back out?!?!?! 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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. |
Thread Tools | |
Display Modes | |
|
|