Query for all employee movements (1 Viewer)

[hatmak]

i have

Movements_during_qry ------ movments to all employee during day

Movements_qry------- which including arrival in morning and depature in end day


how i combine two query’s in one query to show data

example
in day 05/02/2015 show employee 1 show all movements and arrival

( from 2 querys)

 

[Isskint]

Either create a new query using both queries as the source data OR copy one of the queries and add the other table and the relevant fields.

This is pretty basic and may not be formatted right, but it should give you some ideas

SELECT Employee_tb.Name, Movements_tb.Date_Trans, Movements_tb.time_in2, Movements_tb.time_out2, Movements_tb.Permission_Type, Format(IIf([time_in2]>(#12/30/1899 8:0:0#),(#12/30/1899 8:0:0#-[time_in2]),"0"),"Short Time") AS MorningDel, IIf([time_in2]>(#12/30/1899 8:0:0#),(DateDiff("n",#12/30/1899 8:0:0#,[time_in2])),"0") AS Minutes_Morning, Format(IIf([time_out2]<(#12/30/1899 15:30:0#),(#12/30/1899 15:30:0#-[time_out2]),"0"),"Short Time") AS DepatureDel, IIf([time_out2]<(#12/30/1899 15:30:0#),(DateDiff("n",[time_out2],#12/30/1899 15:30:0#)),"0") AS Minutes_Depature,  Movements_during_tb.ID, Movements_during_tb.Payroll_No, Movements_during_tb.out, Movements_during_tb.[in], Movements_during_tb.Date_Transsss, Movements_during_tb.Permission_Type2, Movements_during_tb.Remarks, Format(([out]-[in]),"Short Time") AS Day_Movementsl, DateDiff("n",[out],[in]) AS Day_Movements_minutes
FROM (Employee_tb INNER JOIN Movements_during_tb ON Employee_tb.Payroll_No = Movements_during_tb.Payroll_No) INNER JOIN Movements_tb ON Employee_tb.Payroll_No = Movements_tb.Payroll_No;

 

[hatmak]

many thanks Isskint

but i found data not matched as attached

please filter on query 1

ahmed

 

[Isskint]

That would be down to the join properties on you tables. You have them as only include records where the joined fields are equal. You would need to change that to All records from Employee_tb. However that then gives you duplication of some records. I am sure there is a way around the duplication but can not find that yet.

There is a better way to do this though. You only need 1 table to store your movement data. Just add an extra field to indicate if it is during day or in the day.