 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / August 2006complex select query
I am creating a database that tracks when hospital employees take certain required education courses. I have a query currently that lists what employees have not taken a specified course. However, I need this query to be a little more involved, and I am stuck. There are certain courses that are required of certain job codes, so I need to figure in that logic - for example, list only those employees that are required to take the specified course, and not those that are not required to take it. (Each employee has a job code.) Does anyone have any suggestions on even where to begin? Thanks in advance. hi, I have a suggestion... Perhaps you could add a column to the job table called "job code". Then you could use the job code of each employee to relate it to all jobs with that specific job code in the job table. Let me know if you need further help... geebee > I am creating a database that tracks when hospital employees take certain > required education courses. I have a query currently that lists what [quoted text clipped - 8 lines] > > Thanks in advance. MES, Do you have the "required courses" information in a table somewhere in your database. I think you will need it, in order to do what you want here. In other words, a list of which courses are required for which job codes.  SignatureSteve Schapel, Microsoft Access MVP > I am creating a database that tracks when hospital employees take certain > required education courses. I have a query currently that lists what [quoted text clipped - 8 lines] > > Thanks in advance. Do you mean a matrix showing for each job code what course is required? >MES, > [quoted text clipped - 8 lines] >> >> Thanks in advance. MES, Well, yes. You want to process data on the basis of whether a required course has been taken. And the meaning of "required" depends on the job code. The only way your query can "know" how to process the data is on the basis of data. So you need to have data available which shows which courses are required for which job codes. Does that make sense? I'm not sure what you mean by "matrix" though. I think you could use a table with 2 fields: JobCode CourseID ... and then it's just a list of all courses associated with all jobs. Once you have that list, I expect it will be a simple matter to join this table into your existing query, and you should have the results you want. SignatureSteve Schapel, Microsoft Access MVP > Do you mean a matrix showing for each job code what course is required? What about if I have multiple job codes that are required to take one course? In this table, would I have the following: JobCode_Course JobCode Course as the fields? Then I could query a job code, and it would give me a list of courses required for that job code? >MES, > [quoted text clipped - 14 lines] > >> Do you mean a matrix showing for each job code what course is required? MES, Yes, that is correct. I am not sure what you are proposing with the JobCode_Course field... is this an Autonumber field? I wouldn't normally think this was necessary, but equally does no harm. SignatureSteve Schapel, Microsoft Access MVP > What about if I have multiple job codes that are required to take one course? > In this table, would I have the following: [quoted text clipped - 5 lines] > as the fields? Then I could query a job code, and it would give me a list of > courses required for that job code? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.