access 能不能做到的效果???
我有一表

是考勤的一表


ID staff_no action date Time
1 123456 IN 2007-7-27 9:00
2 123456 OUT 2007-7-27 11:00
3 123456 IN 2007-7-27 12:00
4 123456 OUT 2007-7-27 6:00

我想做到

staff_no date IN1 OUT1 IN2 OUT2
123456 2007-7-27 9:00 11:00 12:00 6:00

交叉表只能足有一IN和OUT 不能把後面的示出

[解决办法]
原理大概就是这样了,你按照你的需求再作一些修改就可以了


--表数据
IDstaff_noactiondatetime
1123456in12007-7-279
2123456out12007-7-2711
3123456in22007-7-2712
4123456out22007-7-2718
--执行查询
SELECT
staff_no,date,
sum(iif(action= 'in1 ',time,0)) as in1,
sum(iif(action= 'out1 ',time,0)) as out1,
sum(iif(action= 'in2 ',time,0)) as in2,
sum(iif(action= 'out1 ',time,0)) as out2
FROM 表1
group by staff_no,date;
--查询结果
staff_nodatein1out1in2out2
1234562007-7-279111211