Hi Kristen,
I still don't understand how to write this query. My SQL Query looks like this.
SELECT
[BSPMS_TR].[General].Estate.EstateName,
[BSPMS_TR].[Checkroll].DailyAttendance.DailyReceiptionID,
[BSPMS_TR].[Checkroll].DailyAttendance.ActiveMonthYearID,
[BSPMS_TR].[Checkroll].DailyAttendance.RDate,
[BSPMS_TR].[Checkroll].DailyAttendance.DailyTeamActivityID,
[BSPMS_TR].[Checkroll].DailyAttendance.TotalOT,
[BSPMS_TR].[Checkroll].CREmployee.EmpCode,
[BSPMS_TR].[Checkroll].CREmployee.EmpName,
[BSPMS_TR].[Checkroll].AttendanceSetup.AttendType
FROM [BSPMS_TR].[Checkroll].DailyAttendance
INNER JOIN
[BSPMS_TR].[Checkroll].CREmployee ON [BSPMS_TR].[Checkroll].DailyAttendance.EmpID
= [BSPMS_TR].[Checkroll].CREmployee.EmpID INNER JOIN
[BSPMS_TR].[Checkroll].AttendanceSetup ON [BSPMS_TR].[Checkroll].DailyAttendance.AttendanceSetupID
= [BSPMS_TR].[Checkroll].AttendanceSetup.AttendanceSetupID
INNER JOIN
[BSPMS_TR].[General].Estate ON [BSPMS_TR].[Checkroll].DailyAttendance.EstateID
= [BSPMS_TR].[General].Estate.EstateID AND [BSPMS_TR].[Checkroll].CREmployee.EstateID
= [BSPMS_TR].[General].Estate.EstateID AND
[BSPMS_TR].[Checkroll].AttendanceSetup.EstateID = [BSPMS_TR].[General].Estate.EstateID
UNION ALL
SELECT
[BSPMS_BRF].[General].Estate.EstateName,
[BSPMS_BRF].[Checkroll].DailyAttendance.DailyReceiptionID,
[BSPMS_BRF].[Checkroll].DailyAttendance.ActiveMonthYearID,
[BSPMS_BRF].[Checkroll].DailyAttendance.RDate,
[BSPMS_BRF].[Checkroll].DailyAttendance.DailyTeamActivityID,
[BSPMS_BRF].[Checkroll].DailyAttendance.TotalOT,
[BSPMS_BRF].[Checkroll].CREmployee.EmpCode,
[BSPMS_BRF].[Checkroll].CREmployee.EmpName,
[BSPMS_BRF].[Checkroll].AttendanceSetup.AttendType
FROM [BSPMS_BRF].[Checkroll].DailyAttendance
INNER JOIN
[BSPMS_BRF].[Checkroll].CREmployee ON [BSPMS_BRF].[Checkroll].DailyAttendance.EmpID
= [BSPMS_BRF].[Checkroll].CREmployee.EmpID INNER JOIN
[BSPMS_BRF].[Checkroll].AttendanceSetup ON [BSPMS_BRF].[Checkroll].DailyAttendance.AttendanceSetupID
= [BSPMS_BRF].[Checkroll].AttendanceSetup.AttendanceSetupID
INNER JOIN
[BSPMS_BRF].[General].Estate ON [BSPMS_BRF].[Checkroll].DailyAttendance.EstateID
= [BSPMS_BRF].[General].Estate.EstateID AND [BSPMS_BRF].[Checkroll].CREmployee.EstateID
= [BSPMS_BRF].[General].Estate.EstateID AND
[BSPMS_BRF].[Checkroll].AttendanceSetup.EstateID = [BSPMS_BRF].[General].Estate.EstateID
UNION ALL
SELECT
[BSPMS_HO].[General].Estate.EstateName,
[BSPMS_HO].[Checkroll].DailyAttendance.DailyReceiptionID,
[BSPMS_HO].[Checkroll].DailyAttendance.ActiveMonthYearID,
[BSPMS_HO].[Checkroll].DailyAttendance.RDate,
[BSPMS_HO].[Checkroll].DailyAttendance.DailyTeamActivityID,
[BSPMS_HO].[Checkroll].DailyAttendance.TotalOT,
[BSPMS_HO].[Checkroll].CREmployee.EmpCode,
[BSPMS_HO].[Checkroll].CREmployee.EmpName,
[BSPMS_HO].[Checkroll].AttendanceSetup.AttendType
FROM [BSPMS_HO].[Checkroll].DailyAttendance
INNER JOIN
[BSPMS_HO].[Checkroll].CREmployee ON [BSPMS_HO].[Checkroll].DailyAttendance.EmpID
= [BSPMS_HO].[Checkroll].CREmployee.EmpID INNER JOIN
[BSPMS_HO].[Checkroll].AttendanceSetup ON
[BSPMS_HO].[Checkroll].DailyAttendance.AttendanceSetupID =
[BSPMS_HO].[Checkroll].AttendanceSetup.AttendanceSetupID
INNER JOIN
[BSPMS_HO].[General].Estate ON
[BSPMS_HO].[Checkroll].DailyAttendance.EstateID =
[BSPMS_HO].[General].Estate.EstateID AND
[BSPMS_HO].[Checkroll].CREmployee.EstateID =
[BSPMS_HO].[General].Estate.EstateID AND
[BSPMS_HO].[Checkroll].AttendanceSetup.EstateID =
[BSPMS_HO].[General].Estate.EstateID
UNION ALL
SELECT
[BSPMS_BRF].[General].Estate.EstateName,
[BSPMS_BRF].[Checkroll].DailyAttendance.DailyReceiptionID,
[BSPMS_BRF].[Checkroll].DailyAttendance.ActiveMonthYearID,
[BSPMS_BRF].[Checkroll].DailyAttendance.RDate,
[BSPMS_BRF].[Checkroll].DailyAttendance.DailyTeamActivityID,
[BSPMS_BRF].[Checkroll].DailyAttendance.TotalOT,
[BSPMS_BRF].[Checkroll].CREmployee.EmpCode,
[BSPMS_BRF].[Checkroll].CREmployee.EmpName,
[BSPMS_BRF].[Checkroll].AttendanceSetup.AttendType
FROM [BSPMS_BRF].[Checkroll].DailyAttendance
INNER JOIN
[BSPMS_BRF].[Checkroll].CREmployee ON
[BSPMS_BRF].[Checkroll].DailyAttendance.EmpID =
[BSPMS_BRF].[Checkroll].CREmployee.EmpID INNER JOIN
[BSPMS_BRF].[Checkroll].AttendanceSetup ON
[BSPMS_BRF].[Checkroll].DailyAttendance.AttendanceSetupID =
[BSPMS_BRF].[Checkroll].AttendanceSetup.AttendanceSetupID
INNER JOIN
[BSPMS_BRF].[General].Estate ON
[BSPMS_BRF].[Checkroll].DailyAttendance.EstateID = [BSPMS_BRF].[General].Estate.EstateID
AND [BSPMS_BRF].[Checkroll].CREmployee.EstateID =
[BSPMS_BRF].[General].Estate.EstateID AND
[BSPMS_BRF].[Checkroll].AttendanceSetup.EstateID =
[BSPMS_BRF].[General].Estate.EstateID
UNION ALL
SELECT
[BSPMS_POM].[General].Estate.EstateName,
[BSPMS_POM].[Checkroll].DailyAttendance.DailyReceiptionID,
[BSPMS_POM].[Checkroll].DailyAttendance.ActiveMonthYearID,
[BSPMS_POM].[Checkroll].DailyAttendance.RDate,
[BSPMS_POM].[Checkroll].DailyAttendance.DailyTeamActivityID,
[BSPMS_POM].[Checkroll].DailyAttendance.TotalOT,
[BSPMS_POM].[Checkroll].CREmployee.EmpCode,
[BSPMS_POM].[Checkroll].CREmployee.EmpName,
[BSPMS_POM].[Checkroll].AttendanceSetup.AttendType
FROM [BSPMS_POM].[Checkroll].DailyAttendance
INNER JOIN
[BSPMS_POM].[Checkroll].CREmployee ON [BSPMS_POM].[Checkroll].DailyAttendance.EmpID
= [BSPMS_POM].[Checkroll].CREmployee.EmpID INNER JOIN
[BSPMS_POM].[Checkroll].AttendanceSetup ON [BSPMS_POM].[Checkroll].DailyAttendance.AttendanceSetupID
= [BSPMS_POM].[Checkroll].AttendanceSetup.AttendanceSetupID
INNER JOIN
[BSPMS_POM].[General].Estate ON [BSPMS_POM].[Checkroll].DailyAttendance.EstateID
= [BSPMS_POM].[General].Estate.EstateID AND [BSPMS_POM].[Checkroll].CREmployee.EstateID
= [BSPMS_POM].[General].Estate.EstateID AND
[BSPMS_POM].[Checkroll].AttendanceSetup.EstateID = [BSPMS_POM].[General].Estate.EstateID
I need to write a procedure that selects the correct database from a database table instead of hardcoding, so if we add a new database it will automatically select it, without having to re-write the code.
I AM SO SORRY.... i know it's so cumbersome having to help me out like this.