Query to display 2 column value as separate rows

[Jassi]

Hi
I have 2 tables . What i want if employee is present in 2 shifs in a day then data should be displayed in 2 separate rows.


Employee Master - empcode,empname
Employee Details - empcode,Date,shift1,shif2,shift3,amount
I have written the below Query. Employee can be present in 2 shifts in a day or it can be only one.
Let us say
Empcode = 1 Name = XYZ
Empcode = 2 name = AbC
Empcode = 1 , 20/01/2016 , 1 , 0 , 3 , 4000 . Employee is present in 2 shifts (1 & 3).
Employee = 2 , 19/01/2016 , 1 , 0, 0 , 5000. Employee is present in only 1 shift.
Date should be displayed like this
Empcode = 1
20/01/2016 1 4000
3 4000

8000Empcode = 2
19/01/2016 1 5000
5000SELECT a.employeecode, max(b.shift1) ,max(b.shift2),max(b.shift3),abs(b.amount)
FROM empmaster AS a
inner join empdetails as b
a.empcode = b.empcode
group by a.empcode
Thanks

[orange]

I would suggest this table
Employee Details - empcode,Date,shift1,shif2,shift3,amount
be modified to

Employee Details - empcode,WorkDate,shiftNo,amountHrs

Date is a reserved word in Access, so use something different eg WorkDate
shiftNo -- represents which shift this record applies to.
AmountHrs --is just a way to say the amount is in Hours, and is the Hours for this employee, on this shift on this workdate.

You may want to review Normalization. When you repeat a field shift1, shift2, shift3 --this usually means an unNormalized structure. Normalization is key to relational database.

This is worth a read also.


Good luck.

[Jassi]

I would suggest this table
Employee Details - empcode,Date,shift1,shif2,shift3,amount
be modified to

Employee Details - empcode,WorkDate,shiftNo,amountHrs

Date is a reserved word in Access, so use something different eg WorkDate
shiftNo -- represents which shift this record applies to.
AmountHrs --is just a way to say the amount is in Hours, and is the Hours for this employee, on this shift on this workdate.

You may want to review Normalization. When you repeat a field shift1, shift2, shift3 --this usually means an unNormalized structure. Normalization is key to relational database.

This is worth a read also.


Good luck.

Database cannot be changed now . I just want to split 2 columns to be displayed as separate rows

[orange]

Perhaps a Union Select along these lines.

SELECT tjassi.empId, tjassi.wdate,tjassi.shift1 as shift,amount
FROM tjassi where shift1 <> 0
UNION
SELECT tjassi.empId, tjassi.wdate, tjassi.shift2 as shift,amount
FROM tjassi where shift2 <> 0
UNION SELECT tjassi.empId, tjassi.wdate, tjassi.shift3 as shift,amount
FROM tjassi where shift3 <> 0