Adding something to a recordset

[emozley]

I've got a query which builds up a list of names of people who are either on holiay, off sick or don't work a particular day.

Is it possible to make it so that in the recordset an imaginary column called AbsenceCode is created which has an H for people on the first select, S for the second and N for the third?

My plan is drop all of that into an array so I can do some other stuff with it.

Thanks

Ed

SELECT FirstName, Surname FROM Users LEFT JOIN StaffHolidays ON Users.UserID=StaffHolidays.UserID WHERE HolidayDate=#2007/5/16# AND (Period=2 OR Period=3) ORDER BY FirstName ASC

UNION SELECT FirstName, Surname FROM Users LEFT JOIN StaffSickness ON Users.UserID=StaffSickness.UserID WHERE SicknessDate=#2007/5/16# AND (Period=2 OR Period=3) ORDER BY FirstName ASC

UNION SELECT FirstName, Surname FROM Users LEFT JOIN StaffStatus ON Users.UserID=StaffStatus.UserID WHERE Wednesday=FALSE And Users.Visible=TRUE AND StaffStatus.StartDate< #16/05/2007#ORDER BY FirstName ASC

 

[Remou]

How about:

[tt]SELECT "H" As AbsenceCode, FirstName, Surname FROM Users LEFT JOIN StaffHolidays ON Users.UserID=StaffHolidays.UserID WHERE HolidayDate=#2007/5/16# AND (Period=2 OR Period=3) ORDER BY FirstName ASC

UNION SELECT "S" As AbsenceCode, FirstName, Surname FROM Users LEFT JOIN StaffSickness ON Users.UserID=StaffSickness.UserID WHERE SicknessDate=#2007/5/16# AND (Period=2 OR Period=3) ORDER BY FirstName ASC

UNION SELECT "N" As AbsenceCode, FirstName, Surname FROM Users LEFT JOIN StaffStatus ON Users.UserID=StaffStatus.UserID WHERE Wednesday=FALSE And Users.Visible=TRUE AND StaffStatus.StartDate< #16/05/2007#ORDER BY FirstName ASC[/tt]