CODEWITHSUNDEEP
sqlms-access
Samir Ibrahim
Samir Ibrahim

Reputation: 102

How to make relation to a column(s) depending on value

4I have these tables : 

day_shift                               
id_dshift   dshift_name on_duty off_duty    in_start    in_end  out_start   out_end     workday
1001        ds_normal   7:00    15:00       7:00        10:00   11:00       20:00       1
1002        ds_Saturday 7:00    14:00       7:00        10:00   11:00       14:00       1

week_shift
id_wshift   wshift_name   mon   tue     wed     thu     fri     sat     sun
2001        ws_normal     1001  1001    1001    1001    1001    1002    1001
2002        ws_2013_w1    0     1001    1001    1001    1001    1001    0
2003        ws_2013_w2    1003  1001    1001    1001    1001    1002    1001

daily_attendance                    
emp_id  checkdate   in      out     emp_shift_id
10      15/06/2013  7:10    15:05   2001        <-- saturday
10      16/06/2013  7:05    15:03   2001        <-- sunday

what I want is having a result like this :

emp_id  checkdate   in       out    on_duty off_duty
10      15/06/2013  7:10    15:05   07:00   14:00
10      16/06/2013  7:30    14:30   07:00   15:00

in first row of daily_attendance, since the weekday is saturday so i want to get the value of week_shift.sat (1002) if the weekday is sunday, i want to get the value of week_shift.sun (1001)

so I get the on_duty and off_duty values from day_shift

How to do it in query?

Upvotes: 1

Views: 62

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123779

The trick here would be to create a saved query in Access named [week_shift_transformed] to transform your [week_shift] table into separate rows for each day of the week:

SELECT id_wshift, wshift_name, 1 AS [weekday], [sun] as id_dshift FROM week_shift
UNION ALL
SELECT id_wshift, wshift_name, 2 AS [weekday], [mon] as id_dshift FROM week_shift
UNION ALL
SELECT id_wshift, wshift_name, 3 AS [weekday], [tue] as id_dshift FROM week_shift
UNION ALL
SELECT id_wshift, wshift_name, 4 AS [weekday], [wed] as id_dshift FROM week_shift
UNION ALL
SELECT id_wshift, wshift_name, 5 AS [weekday], [thu] as id_dshift FROM week_shift
UNION ALL
SELECT id_wshift, wshift_name, 6 AS [weekday], [fri] as id_dshift FROM week_shift
UNION ALL
SELECT id_wshift, wshift_name, 7 AS [weekday], [sat] as id_dshift FROM week_shift

That will give you

id_wshift  wshift_name  weekday  id_dshift
---------  -----------  -------  ---------
2001       ws_normal          1  1001     
2002       ws_2013_w1         1  0        
2003       ws_2013_w2         1  1001     
2001       ws_normal          2  1001     
2002       ws_2013_w1         2  0        
2003       ws_2013_w2         2  1003     
2001       ws_normal          3  1001     
2002       ws_2013_w1         3  1001     
2003       ws_2013_w2         3  1001     
2001       ws_normal          4  1001     
2002       ws_2013_w1         4  1001     
2003       ws_2013_w2         4  1001     
2001       ws_normal          5  1001     
2002       ws_2013_w1         5  1001     
2003       ws_2013_w2         5  1001     
2001       ws_normal          6  1001     
2002       ws_2013_w1         6  1001     
2003       ws_2013_w2         6  1001     
2001       ws_normal          7  1002     
2002       ws_2013_w1         7  1001     
2003       ws_2013_w2         7  1002

Then you can use a query like this:

SELECT da.emp_id, da.checkdate, da.in, da.out, ds.on_duty, ds.off_duty
FROM
    daily_attendance da
    INNER JOIN
    (
        week_shift_transformed wtt
        INNER JOIN
        day_shift ds
            ON ds.id_dshift = wtt.id_dshift
    )
        ON wtt.weekday = Weekday(da.checkdate)
            AND wtt.id_wshift = da.emp_shift_id

Upvotes: 2

Related Questions