make query include all records even if no data found

[diberlee]

Hi,

I'm having some trouble getting a query to work as desired and hope somebody can help out.

I am setting up a database to hold staff details, and would like a query to show each member of staff's total hours and FTE.

Staff name etc is in tblStaff
Staff shift details are in tblShifts linked via staffID

tblShifts will contain details of the shift worked on each day of the week, but the majority of our staff work a standard shift - e.g 8-4, 9-5. Therefore what I wanted to do was in tblStaff set a field named shiftPattern to 1 2 3 or 4. 1 indicating a custom shift, and any other number indicating a set shift defined in a separate table.

The problem I have is that my query only returns people who have details in tblShifts - regardless of their shiftPattern value. If I enter a blank record in tblShifts it will do the above as intended, but I'm sure there must be a better way around it. Any ideas?

Regards
Duane

 

[vbaInet]

The problem I have is that my query only returns people who have details in tblShifts - regardless of their shiftPattern value.

The OP is talking about a join problem here Uncle Gizmo. So s/he needs to change it from an INNER JOIN to a LEFT JOIN.

 

[diberlee]

Hi,

Here is the full SQL for the query.

SELECT tblStaff.staffName, tblTeamLeader.teamLeaderName, tblDept.deptName, tblRoles.roleName, tblStaff.shiftPattern, tblStandardShifts.shiftDescription, IIf([tblStaff]![shiftPattern]=1,Sum(DateDiff("n",[startTime],[endTime])-Nz([lunchDuration])-Nz([break1duration])-Nz([break2duration])-Nz([break3duration]))/60,35) AS hoursWorked, Round([hoursWorked]/35,2) AS FTE
FROM tblStandardShifts INNER JOIN ((tblTeamLeader INNER JOIN (tblRoles INNER JOIN (tblDept INNER JOIN tblStaff ON tblDept.deptID = tblStaff.staffDept) ON tblRoles.roleID = tblStaff.staffJobTitle) ON tblTeamLeader.teamLeaderID = tblStaff.staffTL) INNER JOIN tblShifts ON tblStaff.staffID = tblShifts.staffID) ON tblStandardShifts.shiftPatternID = tblStaff.shiftPattern
GROUP BY tblStaff.staffName, tblTeamLeader.teamLeaderName, tblDept.deptName, tblRoles.roleName, tblStaff.shiftPattern, tblStandardShifts.shiftDescription;

I don't necessarily need all of these fields for the current task, but it would be handy to have them all as one of the jobs I need to do is output a summary of all staff.

Is there a particular join that need to change to LEFT?

 

[vbaInet]

Right click the join, edit join and a small window will pop up. It would become self explanatory when you see this window. Give it a shot.

 

[diberlee]

I changed the last JOIN from INNER to LEFT and that seems to have done the trick. Now returning all 66 staff instead of just the 4 with shift data.

Thanks both for taking the time to reply.

Is there somewhere in design view where I can specify the join type, or does it have to be done in SQL view?

 

[diberlee]

Right click the join, edit join and a small window will pop up. It would become self explanatory when you see this window. Give it a shot.

Lovely, will have a play around with that. I never knew those arrows were there to serve any purpose other than to irritate me until I've arranged all tables to eliminate them crossing... lol

Every time I think I'm getting a handle on Access it reminds me how little I know

 

[vbaInet]

I never knew those arrows were there to serve any purpose other than to irritate me until I've arranged all tables to eliminate them crossing... lol

Those pesky little arrows hehe!