Solved UNION QUERY.

[smtazulislam]

This is my UNION QUERY.

SELECT qryVacationProgressList.EmployeeID, qryVacationProgressList.EmployeeName, qryVacationProgressList.CompanyName, qryVacationProgressList.MaxOfLeaveDate, qryVacationProgressList.BranchEn, qryVacationProgressList.Status
FROM qryVacationProgressList;
UNION SELECT qryFinalExitProgressList.EmployeeID, qryFinalExitProgressList.EmployeeName, qryFinalExitProgressList.CompanyName, qryFinalExitProgressList.MaxOfLeaveDate, qryFinalExitProgressList.BranchEn, qryFinalExitProgressList.ExitStatus
FROM qryFinalExitProgressList;

Result Display :

This result is perfect display. But problem is we don't know who is applied for Final Exit Or Vacation.

Can possible that, write, Progress - Vacation from vacation table And Progress - Final Exit from Final Exit table.

 

[XPS35]

Try something like this:

SELECT qryVacationProgressList.EmployeeID, qryVacationProgressList.EmployeeName, qryVacationProgressList.CompanyName, qryVacationProgressList.MaxOfLeaveDate, qryVacationProgressList.BranchEn, qryVacationProgressList.Status, "Progress - Vacation" as Type
FROM qryVacationProgressList;
UNION SELECT qryFinalExitProgressList.EmployeeID, qryFinalExitProgressList.EmployeeName, qryFinalExitProgressList.CompanyName, qryFinalExitProgressList.MaxOfLeaveDate, qryFinalExitProgressList.BranchEn, qryFinalExitProgressList.ExitStatus, "Progress - Final exit" as Type
FROM qryFinalExitProgressList;

 

[jdraw]

Try the following

SELECT qryVacationProgressList.EmployeeID, qryVacationProgressList.EmployeeName, qryVacationProgressList.CompanyName, qryVacationProgressList.MaxOfLeaveDate, qryVacationProgressList.BranchEn, qryVacationProgressList.Status,"Vacation" as MyStatus
FROM qryVacationProgressList
UNION SELECT qryFinalExitProgressList.EmployeeID, qryFinalExitProgressList.EmployeeName, qryFinalExitProgressList.CompanyName, qryFinalExitProgressList.MaxOfLeaveDate, qryFinalExitProgressList.BranchEn, qryFinalExitProgressList.ExitStatus, "FinalExit" as MyStatus
FROM qryFinalExitProgressList;

 

[arnelgp]

you can also use this:

SELECT qryVacationProgressList.EmployeeID, qryVacationProgressList.EmployeeName, qryVacationProgressList.CompanyName, qryVacationProgressList.MaxOfLeaveDate, qryVacationProgressList.BranchEn, qryVacationProgressList.Status + " - Vacation" As EmpStatus
FROM qryVacationProgressList
UNION
SELECT qryFinalExitProgressList.EmployeeID, qryFinalExitProgressList.EmployeeName, qryFinalExitProgressList.CompanyName, qryFinalExitProgressList.MaxOfLeaveDate, qryFinalExitProgressList.BranchEn, qryFinalExitProgressList.ExitStatus +  " - Final Exit" As EmpStatus FROM qryFinalExitProgressList;

 

[Grumm]

And this is a good example on why it is important to normalize a database.
You could have avoided the union if you added a table for all the employees, a table for the companies, a table for the vacations and a table for the FinalExits.
All linked with ID's and you can select any employee with their vacation and finalExit using left joins.

 

[gemma-the-husky]

All you need is for the fields count and types to match. You could extra fields to both parts, and populate some with blanks.

Instead of your query simply do

SELECT * FROM qryVacationProgressList
UNION SELECT * FROM qryFinalExitProgressList;

or if you don't need all the fields in the lists

SELECT * FROM q1 (where q1 is based on qryVacationProgressList)
UNION SELECT * FROM q2 (where q2 is based on qryFinalExitProgressList)


and then tweak both queries until you get the fields to match.
You can add sort by setting to this as well.

 

[smtazulislam]

Thank you very much to all.