count help please! 2

[danwand]

Hi,

I have a table staffDetails with field staffID(among many other fields!). I have another table :tblISS which has fields: staffID and sessionDate.
I have created a query based on tblISS which has fields: tblISS.StaffID(group by), sessionCount(count StaffID), xPeriod(group by) and tblISS.sessionDate(group by, asending, not shown).
xPeriod = getPeriod(sessionDate) - this is a call to a custom function that works out the year period for the given sessionDate and returns a string value such as "2001/2002"

This all works fine and gives me a count for the number of sessions attended by each member of staff for each xPeriod(but only for those members of staff that actually attented a session in the given xPeriod).
The problem is that i need to show count results for each member of staff from tblStaffDetails, so if they didn't attend any sessions in a given xPeriod then the query should show 0 for count for that xPeriod. So basically for each xPeriod each staffName in tblStaffDetails should be displayed in the query results with a count value of 0(if staff did not attend a session in that year) or 1 or more for those that did attend.

Any ideas would be appreciated.

Thanks Dan

 

[lespaul]

change the inner join to a left join. In the query designer this means right clicking on the relationship line between staffDetails and tblISS and selecting the option that reads:

Select all the records from staffDetails and only those that match from tblISS.

Read more about joins:

Understanding SQL Joins

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[danwand]

Thanks for the reply Leslie but still having problems!

The query now reads:
SELECT tblStaffDetails.StaffID, qryISSSub3.sessC, qryISSSub3.xPeriod
FROM tblStaffDetails LEFT JOIN qryISSSub3 ON tblStaffDetails.StaffID = qryISSSub3.StaffID;

Which produces the following results when ran:

StaffID sessCount xPeriod
1 1 2001/2002
1 1 2005/2006
2 1 2004/2005
2 3 2005/2006
3 1 2004/2005
3 1 2005/2006
4 1 2005/2006
5
6 1 2005/2006
7 1 2005/2006
8 1 2004/2005
8 1 2005/2006
9 1 2005/2006
10 1 2005/2006
11 1 2005/2006
12 1 2005/2006
13 1 2005/2006
14 2 2005/2006
15 1 2005/2006

So as you can see we have 3 xPeriods: "2001/2002", "2004/2005" and "2005/2006".

Only one staff member attendeded a session in 2001(staffID 1) which is correct.

In period "2005/2006" all but one staff member attended at least 1 session. Which again is correct.

However i need the results to include each staffID for each period, so for period "2001/2002" there should be staffID 1 with a count of 1, but i also need staffIDs 2 to 15 to also be displayed with a count of 0 for that xperiod.
And for the other xPeriods as well.

So this type of join works to a certain extent in that staffID 5 is included in the query even though this staff member has not attended any sessions but sessC and xPeriod are blank!

Not sure where i'm going wrong, again further help would be appreciated

 

[lespaul]

So what's the SQL for:

qryISSSub3

 

[danwand]

Hi leslie,

The sql for subISS3 is:
SELECT tblISS.StaffID, Count(tblISS.StaffID) AS sessC, getPeriod([sessDate]) AS xPeriod
FROM tblISS
GROUP BY tblISS.StaffID, getPeriod([sessDate]);


thanks for your time,

Dan

 

[r937]

this is tricky, because you need the left table in the LEFT OUTER JOIN to contain all combinations of StaffID and xPeriod

because access does not support CROSS JOIN, you will need two more queries:

qryPeriods --

select distinct getPeriod([sessDate]) AS xPeriod
  FROM tblISS

qryCrossJoin --

SELECT tblStaffDetails.StaffID
     , qryPeriods.xPeriod
  FROM tblStaffDetails 
     , qryPeriods

now you can write the query --

SELECT qryCrossJoin.StaffID
     , iif(isnull(qryISSSub3.sessC)
               ,0,qryISSSub3.sessC) as sessions
     , qryCrossJoin.xPeriod
  FROM qryCrossJoin
LEFT OUTER
  JOIN qryISSSub3 
    ON qryCrossJoin.StaffID = qryISSSub3.StaffID
   and qryCrossJoin.xPeriod = qryISSSub3.xPeriod

please let me know if this works




r937.com | rudy.ca

 

[danwand]

Fantastic works perfectly,

Thanks to both of you for your help.

Dan