The Logic Escapes Me! 2

[HJessen]

I can't figure out the logic needed for this query and I am sure once I see it, I will feel really dumb!

I have a table that has the following fields:
Name
ID
Topic
Date
Score
FY/Qtr
The problem is, I need to see who missed specific training for a particular quarter of a fical year. i.e. maybe 5 individuals did not make safety training for 2002Q4, and I need to know who they are.

Since there are a number of records for safety training (2002Q3, 2002Q2, 2003Q1, etc.) how can I look to see who missed just that one class for that one quarter? No record is input until the training is attended.

I hope this is clear enough for someone to be able to help.

THANKS. H. Jessen
"Now I know more, and I feel dummer"

 

[Geekmomz]

In your criteria row, under FYQtr, enter the value for the particular quarter (2002Q3), and in the same row under Score, enter Is Null. That ought to give you the results you need.

HTH
Geekmomz

 

[HJessen]

Geekmomz

Right, it would except that there would be no record until a score was entered. Therefore it won't work. That is my problem - I am trying to find what is not there in two fields. H. Jessen
"Now I know more, and I feel dummer"

 

[r937]

set up a small table of fiscal quarters

into this table, you will insert the values you want to search for, e.g.

2002Q1
2002Q2
2002Q3
2002Q4

replace these values with other values for different searches

okay, now join this table to your main table, group by employee, and the guys you want are the ones who don't have 4 matching rows

[tt]select Name
from yourtable Y
inner
join fiscalyeartable F
on Y.fyrqtr = F.fyrqtr
where Topic = 'safety training'
group
by Name
having count(*) < 4[/tt]

rudy

http://rudy.ca/

 

[KenReay]

Hi

Maybe I am missing something in your explanation, but surely you need two additional things not mentioned so far:

A table containing a list of all of the people (Employees?)

A Left Join Employees -> Training (on EmployeeId)

Now with Criteria of Topic = &quot;Safety&quot; and FYQtr = &quot;2002Q2&quot; And Score = null

All EEs with no training record in 2002Q2 will be selected

Did I miss the plot? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[r937]

the employees table is just an added wrinkle, which i thought was covered by your &quot;Name&quot; and &quot;ID&quot; columns

the problem with using a left outer join and then testing for null is that you would have to enter all fiscal quarters that you're searching for into the criteria

now, if it's just one quarter (and it is in your original question) then an outer join would solve the problem

&quot;has anybody missed more than one in the last four quarters&quot; would require the fiscal table

your choice

 

[KenReay]

Hi

Surely:

Since the table

Name
ID
Topic
Date
Score
FY/Qtr

Contains ONLY the EE's who have had training, it is essential to include the Employees table, since that is the only place a list of all employees is known
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[r937]

yes, you're right

and don't call me shirley


just kidding

yeah, i missed that



rudy