Query to display empty rows for "missing" data

[diberlee]

Hi,

I'm trying to make a sub form that displays the hours of an employee selected in a listbox. I've got most of it working but having a bit of an issue.

The info for thre query is in 3 tables:
tblStaff (name etc)
tblShifts (start and end times for days that this employee works)
tblDays (a list of days names so I can use numbers elsewhere)

My query looks like this:

SELECT tblDays.dayName, IIf(Nz([startTime],"")="","NWD",[startTime]) AS start, IIf(Nz([endTime],"")="","NWD",[endTime]) AS [end], tblStaff.staffName
FROM tblDays LEFT JOIN (tblStaff RIGHT JOIN tblShifts ON tblStaff.staffPK = tblShifts.staffFK) ON tblDays.dayPK = tblShifts.workingDay
WHERE (((tblStaff.staffName)=[Forms]![frmMain]![lst_myTeam] Or (tblStaff.staffName) Is Null));

This worked fine with a single user and some test data - it correctly displayed all days of the week, with start/end times on Mon and Tues where I had entered shift information, and "NWD" against all other days.

However, when I add a couple more employees to the mix it shows the correct info for the first employee, but anybody else it will only display days where person 1 doesn't have any hours. I haven't entered any hours for the new employees, but the query should still display Mon-Sun with NWD in every column. It shows Wed-Sun but Mon and Tues are missing.

I've tried different join types but they all come back with "ambiguous joins" error when I try to run.

Can anybody spot what I'm doing wrong?

Regards
Duane

 

[GinaWhipp]

You may need two queries to get what you want which is a RIGHT join. So, you put two tables together in one query and then create a third query with the first query and the last table. Understand?

 

[diberlee]

Thanks Gina, I'll give that a go today.

Sorry for the slow reply, I made the thread just as I was leaving work and all but forgot about work issues until I got back in the office

 

[diberlee]

Tried that and it works perfectly. Thanks for your help

 

[GinaWhipp]

No problem, glad to help!