Creating List of Classes showing if Employee has taken or not

[CindiN]

Hi guys,
I am trying to create a form or report (doesn't matter which) that will show my list of ALL classes, and then allow me to select an employee to see if they've taken this class or not and if they passed. I have a ClassesTBL, EmployeeTBL and Pass/FailTBL. I have even been able to create a query that has a parameter asking for the employee name, then it will give me a list of "only the employee's" classes they've taken. I still want the results to show ALL the classes with a yes or no if taken or not.

I'm not a VBA or SQL user, so if there is a way to do this without writing code, I would appreciate the help.
Thanks,
CindiN

 

[RivetHed]

Hiya,

first off if you want to show all the classes wether they've been taken or not change the join properties in your query to: show all records from classestbl and only those from employeestbl which match.

next to display all this on a form create a form based on the results of this query with all the fields you want to view in the form detail in a row and change the defaultview property of the form to continuous formsand shrink the detail section so it is only one row big. In the form header insert a text box and a command button, open up properties for the command button and go to the onclick event procedure click build and go into the code builder, in there just type in requery between the 2 lines already present.

Next go to your original query and change the criteria from your parameter to [forms]![FormName]![TextBoxName]

Now when you type an employee name in to the text box on the form and click the button it should bring up all classes in a sort of table

 

[RivetHed]

Or you can ignore what I've just realised is an overcomplicated method and just amend the query like I suggested in the first paragraph to get all the classes, create a report using the wizard and for your yes\no field change the employeename field (assuming that you output this in your query if not, do) from =[employeename] to
=iif(isnull([employeename]),"No","Yes&quot

 

[CindiN]

Ok, I think I did what you suggested in your second message, but how can I also show the employee's name AND the yes or no? Plus when I changed the query to add the IIF code, the report show's no records at all. I'm sure it's something small, but I've spent all day on it.

Thank you so much for your help.
CindiN

 

[RivetHed]

sorry I haven't been very clear, for the IIF formula I was talking about the report, but if you want to do it in the query thats no problem have the employeename field output and the IIF formula in a seperate column as an expression and populate your report with those fields.
so your query should have all the fields you want including the employeename and an expression returning a yes or no value.

 

[CindiN]

Thanks, RivetHed! I'll try that, in the mean time, I wasn't sure you were going to respond, so I created another Thread....named "I have an employee table..." or something like that. I did this one a little different than the one we've been talking about. Could you tell me what you think?
Thanks,
Cindi

 

[CindiN]

Hi RivetHed,
I tried what you suggested, I am coming up with ALL classes, and a list of employees per class. I want to tell it which employee and have it list ALL the classes and whether or not that one employee took each class or not.
Sorry to be confusing. My other post, if you've read it might be more explanitory.
Thank you for your patience,
CindiN

 

[RivetHed]

Hi CindiN,

try this, based on 3 tables: TblClasses containing [ClassID] and [ClassDescription], TblEmployees containing [EmployeeID] and [EmployeeName] and TblPass\Fail containing [ClassID], [EmployeeID] and [Pass\Fail]:

SELECT TblEmployees.EmployeeName, TblClasses.ClassDescription, [TblPass\Fail].[Pass\Fail], IIf(IsNull([employeename]),"Class Not Taken","Class Taken&quot AS ClassTaken
FROM TblClasses LEFT JOIN ([TblPass\Fail] LEFT JOIN TblEmployees ON [TblPass\Fail].EmployeeID = TblEmployees.EmployeeID) ON TblClasses.ClassID = [TblPass\Fail].ClassID
WHERE (((TblEmployees.EmployeeName)=[Please enter employee name] Or (TblEmployees.EmployeeName) Is Null));

 

[CindiN]

Hi RivetHed,

I am so sorry, I am just not explaining myself correctly. Would you mind terribly reading my other post, titled: "I have an employee table..."?

I have really gone into much more detail on that one, which hopefully makes sense more than this one has. Forgive my inexperience, please.

I really appreciate your help.
CindiN