INSERT INTO UIAB_STAFF
SELECT *
FROM (SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='ALJ' and Unit='Hearing' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='ALJ')) and EMP.Unit='Hearing' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='ALJ' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='ALJ')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='ALJ' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='ALJ')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Board' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Board')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Board' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Board')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Admin' and Unit='Hearing' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Admin')) and EMP.Unit='Hearing' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Admin' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Admin')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Admin' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Admin')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
UNION ALL
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Admin' and Unit='IT' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Admin')) and EMP.Unit='IT' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Executive' and Unit='Hearing' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Executive')) and EMP.Unit='Hearing' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Executive' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Executive')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Executive' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Executive')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
UNION ALL
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Executive' and Unit='IT' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Executive')) and EMP.Unit='IT' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Project' and Unit='Hearing' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Project')) and EMP.Unit='Hearing' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Project' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Project')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Project' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Project')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
UNION ALL
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street' and Category='Project' and Unit='IT' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Project')) and EMP.Unit='IT' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc ) AS [%$##@_Alias];