Help creating report 1

[bpenney]

Hi everyone,

I am trying to create a report and I just can't seem to come up with a solution, hopefully someone can give me an answer, point me in the right direction or let me know if this is even possible.

What I am trying to do is create a required training report that returns a list of certain employees that are missing a certain class. I am working with CR 8.5 on a MS Jet database. I have fields, and history, for everything that is required, Name, Job title, Class description and so on.

What I want to do is search for those employees in a certain job title, say supervisor, who are missing a certain class, say "training 1" and have it return everyone who is a supervisor and doesn't have "training 1" listed in their training history.

Is this possible or am I off my rocker.

TIA.

 

[synapsevampire]

Sure, you can do this, providing that you have a table that lists all classes.

Even without you might use a subquery (or a main report with the classes and a subreport for the history) to produce all classes, and then join to the history table by the class id with a Left Outer and use a record selection formula that has:

isnull({history.class})

Pretty basic overview but it sounds like you just wanted theory since you didn't share anything technical.

-k

 

[bpenney]

You're right I was just theorizing, but let me see if I can figure this out now.

I am going to have three fields in my report

({Eperson.name})
({Ejob.title})
({Etrain.classdescrip})

All of the training history for the employees is located in the ({Etrain.table}).

Now what I am thinking is that I will also use parameter fields to select the wanted job title,({?Jobtitle}), and class description,({?classdescrip}).

So, should my record selection formula look like:

({Ejob.title}) = ({?Jobtitle})
and isnull({?classdescrip})

I'll give it a little more thought and try to work through it some more and see if I can get it to work. Thanks for the suggestion though.

 

[lbass]

I'm guessing you have two tables here, not three. Let's call them Employee and Training. You should have a left join from the Employee table to the Training table. Create two parameters: {?JobTitle} and
{?Class}. Your record selection could be:

{?JobTitle} = {Employee.JobTitle}

Insert a group on {Employee.Name} (or better yet, {Employee.ID} in case of duplicate names).

Then create a formula {@tookclass}:

if isnull({training.class}) or
{training.class} <> {?Class} then 0 else 1

Then go to report->edit selection formula->GROUP and enter:

Sum({@tookclass},{employee.name}) = 0

This will give you a list of employees with a certain job title who have not taken a specified class.

-LB

 

[bpenney]

LB,

Used your suggestions and it worked like a charm.

Thanks a million!!

BP