 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / January 2005Way to compare requirements with actual?
Hello, I have a database that keeps track of employee training. The main form contains the employees bio information, the subform includes their training( from a training table; they are both linked by Employee number.) Employees have to take particular classes to ensure compliance with regulations. Is there a way to compare the classes each student has taken with the standard requirements for that employee? ( We have about 4 types of employee) The class Name field in my training subform is a lookup to a class table, which just lists the classes offered. Hopeful of a solution, John. You should have a table of classes offered and a table that records the classes employees have taken. Open the database window to queries and click on new. One of the choices is Find Unmatched Query Wizard. Use that option to create a query where you set the employee criteria for the employee you want and the query returns the classes in the classes offered table that are not in the table that records the classes the employee has taken. These will be the classes the employee has not taken. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com www.pcdatasheet.com > Hello, > [quoted text clipped - 11 lines] > > John. Hey PC Datasheet, Thanks for your reply. I did create an unmatched query, but it didn't work right. I have the query that has all employees training and am comparing it to the list of classes required. I want all classes that do not appear in the employees training to show up after the query. I did get some results, but they were incorrect. Here's the SQL: SELECT qrytrainingall.Last, qrytrainingall.First, qrytrainingall.[Class Name], qrytrainingall.[Employee Number] FROM qrytrainingall LEFT JOIN tblHOURLYrequiredclasses ON qrytrainingall.[Class Name] = tblHOURLYrequiredclasses.[Class Name] WHERE (((tblHOURLYrequiredclasses.[Class Name]) Is Null)); Not sure what I'm doing wrong but sure I'm close, John. > You should have a table of classes offered and a table that records the > classes employees have taken. Open the database window to queries and click [quoted text clipped - 27 lines] > > > > John. You need the tables: TblEmployee EmployeeID FName LName etc TblHourlyRequiredClass HourlyRequiredClassID HourlyRequiredClassName TblClassTakenByEmployee ClassTakenByEmployeeID EmployeeID HourlyRequiredClassID Create a standard query that includes TblEmployee and TblClassTakenByEmployee. Join EmployeeID in both tables. Put the following expression in the first field of the query: Employee:[LName] & ", " & [FName] Pull down HourlyRequiredClassID into the second field. Name the query, QryEmployeeClasses. Use the Wizard to create an unmatched query. Find HourlyRequiredClassID in TblHourlyRequiredClass that is not in QryEmployeeClasses. Match HourlyRequiredClassID in TblHourlyRequiredClass to HourlyRequiredClassID in QryEmployeeClasses. Include the field, Employee, in this query. Sort the Employee field ascending. This query will give you a list of required classes for each employee that they have not taken. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com www.pcdatasheet.com > Hey PC Datasheet, > Thanks for your reply. [quoted text clipped - 45 lines] > > > > > > John. Hey PC Datasheet, This is weird, but I'm following your instructions to the letter. In the wizard window where you match the fields, my hourlyrequiredclassID is not showing up in that window, even when it shows up in the query! Here's the SQL from the query: SELECT [Last] & ", " & [First] AS Employee, tbltraining.DSEReqID, tblstudents.Status FROM tblstudents INNER JOIN tbltraining ON tblstudents.[Employee Number] = tbltraining.[Employee Number] WHERE (((tblstudents.Status)="active")); Quite puzzled, John. > You need the tables: > TblEmployee [quoted text clipped - 95 lines] > > > > > > > > John. There is no hourlyrequiredclassID in your tables! Is the primary key of tbltraining DSEReqID? Do you have the three table structure and is there a DSEReqID field in the junction table? -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com www.pcdatasheet.com > Hey PC Datasheet, > This is weird, but I'm following your instructions to the letter. [quoted text clipped - 111 lines] > > > > > > > > > > John. Hey PC Datasheet, The primary key for tbltraining is trainingID; the primary key for my DSERequiredclasses table is DSEReqID. my table structure is as follows: I have a student table (or employees) with PK Employee Number. This is tied to the Training table (PK TrainingID) with Employee Number as a foreign key. My DSERequiredclasses table has PK DSEReqID. I included DSEReqID as a foreign key in my student table. I did not link this to the DSERequiredclasses table though. I will work through the solution you sent again and check my table structure and will post back shortly. Getting frustrated something simple is taking so long, John. > There is no hourlyrequiredclassID in your tables! Is the primary key of > tbltraining DSEReqID? Do you have the three table structure and is there a [quoted text clipped - 139 lines] > > > > > > > > > > > > John. It depends on how you have the tables set up that records which classes are required. The way that I'd do it would be to have three tables.... tblEmployeeType EmployeeTypeID (pk, AutoNumber) EmployeeType (text) ...anything else specific to the type tblClasses ClassID (pk, AutoNumber) ClassName (text) ...other Class fields tblRequirements EmployeeTypeID (long int) ClassID (long int) There would be a record in tblRequirements where, for example, ClassID 1 and 3 were required for Type 1, and Classes 1 and 2 for Type 2. TypeID, ClassID 1,1 1,3 2,1 2,2 Is that what you do have? (You could get away with less tables if there was no further info to be stored about Types and Classes other than their names.) > Hello, > [quoted text clipped - 11 lines] > > John. Hey Rob, Thanks for your reply; I'm not making much of the different employee types. I can just filter out what I don't want. I have two tables, one training, the other training requirements. I'm trying to use the unmatched query wizard to find out classes that are in one that don't appear in the other. Here's my SQL from the unmatched query: SELECT qrytrainingall.Last, qrytrainingall.First, qrytrainingall.[Class Name], qrytrainingall.[Employee Number] FROM qrytrainingall LEFT JOIN tblHOURLYrequiredclasses ON qrytrainingall.[Class Name] = tblHOURLYrequiredclasses.[Class Name] WHERE (((tblHOURLYrequiredclasses.[Class Name]) Is Null)); Thanks for your help! John. > It depends on how you have the tables set up that records which classes are > required. The way that I'd do it would be to have three tables.... [quoted text clipped - 43 lines] > > > > John. There are two things wrong with that (I think, although I am making some assumptions).... First is that tblHOURLYrequiredclasses is only (from the look of it) returning a list of required classes - not a list of required classes per employee. That means that when you compare that list to qrytrainingall, the fact that person A has done the class is hiding the fact that person B hasn't done it yet. To get around that create a query (which I've called qryHOURLYrequiredclasses) which contains both the employee table, and tblHOURLYrequiredclasses. No link between the tables and add the employee number, first, last and client name. Run that to hopefully get an idea of what I'm talking about. (The assumption in the above is, by the way, that you're trying to produce a list of classes required for _all_ staff, not just one at a time.) Anyway. Second problem is that your subtract query is going the wrong way. You want records that show in requirements, but not in classes taken. And you're doing it the other way round. And it also needs to be extended to take account of the employee numbers you added above. That also means that you're going to have to link two fields, which means that the wizard isn't going to cope. So... Start a new query and add both qryHOURLYrequiredclasses and qrytrainingall onto it. Create links between the two employee number fields, and also between the two class name fields. You want all of the records that appear in qryHOURLYrequiredclasses.... so on both of those links right click the line and pick out the join properties, then choose the option that says 'Select all rows from qryHOURLYrequiredclasses....'. Add all the fields from qryHOURLYrequiredclasses and the employee number and class name from qrytrainingall.... and try running that. Hopefully that makes sense and you can see that the last step is just to add the criteria Null to both qrytrainingall.[Class Name] and qrytrainingall.[Employee Number] My SQL looks like this, if that doesn't work... SELECT qryrequirements.* FROM qryrequirements LEFT JOIN qrytrainingall ON (qryrequirements.[Class Name] = qrytrainingall.[Class Name]) AND (qryrequirements.[Employee number] = qrytrainingall.[Employee number]) WHERE (((qrytrainingall.[Employee number]) Is Null) AND ((qrytrainingall.[Class Name]) Is Null)); > Hey Rob, > Thanks for your reply; I'm not making much of the different employee types. [quoted text clipped - 60 lines] > > > > > > John. Hey Rob, Thanks for all your help on this - I followed your tips; it didn't work. Here's what my SQL looked like: SELECT qryhourlyrequiredclasses.*, qrytrainingall.[Class Name], qrytrainingall.[Employee Number] FROM qryhourlyrequiredclasses LEFT JOIN qrytrainingall ON (qryhourlyrequiredclasses.[Class Name] = qrytrainingall.[Class Name]) AND (qryhourlyrequiredclasses.tblstudents.[Employee Number] = qrytrainingall.[Employee Number]) WHERE (((qrytrainingall.[Class Name]) Is Null) AND ((qrytrainingall.[Employee Number]) Is Null)); I'm going to look at this again...pls let me know if you come up with something. Feeling like Frodo near Mt Doom, John. > Hello, > [quoted text clipped - 11 lines] > > John. Hey Rob, IT WORKED! I wasn't linking the right table... Yahoo! That query was the ballgame right there...I appreciate all your help - Thank you! Feeling like Spiderman getting his powers back... John. > Hey Rob, > Thanks for all your help on this - I followed your tips; it didn't work. [quoted text clipped - 31 lines] > > > > John. That's excellent. Could I borrow Kirsten Dunst for a night out? > Hey Rob, > [quoted text clipped - 42 lines] > > > > > > John. Let me just check.... if you run qryhourlyrequiredclasses then you get a list of ALL people (and their ID) with the class names that they need to do. And if you run qrytrainingall then you get much the same thing, but ONLY showing classes that people have actually done? > Hey Rob, > Thanks for all your help on this - I followed your tips; it didn't work. [quoted text clipped - 31 lines] > > > > John. |
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.