Help a query... (1 Viewer)

[smtazulislam]

Thanks for any help.

In the attached dB, I want to show .............
Table Employee is "Status = Active"
Table Transfer is "Status = Transfer"
Table Vacation is "Status = Back Vacation"


How to work it :
First checking Employee Table with Status is "Active" Then Transfer Table with tStatus is "Null Or Transfer" or "Current". ("Current" meaning he is work any company. And "Transfer" meaning he don't have work).
then need to confirm that he is return from Vacation or Not. (If he return from Vacation then vStatus will be "Back Vacation") .

Criteria Like : Status = "Active" And tStatus = "Null or Transfer" (Note: Latest Record) And vStatus ="Back Vacation" (Note: Latest Record)
Show how many employee Not Transfer yet : .................

 

[smtazulislam]

any help...........

 

[arnelgp]

see Query1

 

[smtazulislam]

Thank you so much for your reply.
its display well. But need some criteria added.
Please see the attached picture i have explain there.
And I have attached more record in this dB.
View attachment 91866

 

[smtazulislam]

can anyone help me....

 

[arnelgp]

can you check Query1?

 

[smtazulislam]

Thank you so much,
Its work only if change anything with table Vacation,
But when I try to Transfer table any change then nothing update in the Query. Can you check it.

EDIT:/
If is it well work. I have to put my app. And it is Split with FE and BE. Query based on two Query is work in the BE file. Please give advice.

 

[smtazulislam]

can you check Query1?

Hello sir, any solution above the message which Transfer table any change then nothing update in the Query .

 

[smtazulislam]

Thank you. I did it. Alhamdulillah.

Status: Switch([VacStart]>[TransferFrom] And IsNull([VacReturN]),"VacNoReturn",IsNull([TransferFrom]),"No Work",[TransferTo]>[TransferFrom],"No Work",[TransferFrom]>=Nz([VacReturn]),"Work",True,"No Work")