Nested If Statement in SQL

It sounds like you need something like

SELECT rotas.day_of_week, staff.staff_name, rotas.bk_date, rotas.short_name, rotas.description,
FROM rotas INNER JOIN staff ON rotas.short_name = staff.short_name
WHERE (((rotas.short_name)=[enter name]) AND ((rotas.year_number)=[enter year]) AND ((rotas.week_number)=[enter week]) AND           rotas.description In ("PDO", "UDO")
ORDER BY rotas.bk_date;

I'm taking a bit of a guess at some things because you haven't provided any information about your tables, so I'm assuming you have a common field in your staff and rotas tables that identifies the staff member. In this case I've assumed the field is called short_name. If your using an ID field instead then your query would become something like;

SELECT rotas.day_of_week, staff.staff_name, rotas.bk_date, rotas.short_name, rotas.description,
FROM rotas INNER JOIN staff ON rotas.staff_id = staff.staff_id
WHERE (((rotas.short_name)=[enter name]) AND ((rotas.year_number)=[enter year]) AND ((rotas.week_number)=[enter week]) AND           rotas.description In ("PDO", "UDO")
ORDER BY rotas.bk_date;

I cant' understand this part ...

(rotas.short_name)=[enter name])

???? AND ((rotas.description) AND ????

select * from staff s inner join rotas r on r.staff_name = s.staff_name and r.description in ('PDO','UDO') and (r.day_of_week = 6 or r.day_of_week = 7)

or
The set of all staff who have rotas with PDO or UDO on day 6 or 7

Or perhaps we don't understand the question...

Apologies but I don't think I have explained this very well. The code below extracts the Saturdays or Sundays an employee has off in a year. Days off are marked in the rotas.description field as 'UDO' or 'PDO'. What I need is SQL to run in MS Access that will allow me to group Saturday and Sunday together ie: Weekend, and search the database for all the weekends a member of staff has off in 1 year, ie: an employee has both Saturday and Sunday off not Saturday or Sunday. Therefore the day of the week is 1 and 2 and the rota.description is UDO and PDO.

SELECT rotas.day_of_week, staff.staff_name, rotas.bk_date, rotas.short_name, DayDescriptiontbl.DayFullName, rotas.description, rotas.year_number, rotas.week_number
FROM DayDescriptiontbl INNER JOIN (staff INNER JOIN rotas ON staff.short_name = rotas.short_name) ON DayDescriptiontbl.DayNumber = rotas.day_of_week
WHERE (((rotas.day_of_week)=1 Or (rotas.day_of_week)=2) AND ((rotas.short_name)=[enter name]) AND ((rotas.description)="UDO" Or (rotas.description)="PDO") AND ((rotas.year_number)=[enter year]) AND ((rotas.rota_status)<>"X"))
ORDER BY rotas.bk_date;

thanks
B

How is your data structured?
I.E., post some sample data from DayDescriptiontbl, rotas, staff, etc.