How to make cross tab query

[Ben Saeed]

Hi,

I would much appreciate if I could get some advise regarding following query.

I am making some attendance sheet on access as follows

Name !09:00!10:00!11:00!12:00!13:00!14:00!15:00
------!-----!------!-----!------!-----!------!
Test1 !Prsnt! ! ! ! ! !
test2 !Absnt! ! ! ! ! !
test3 !Vction! ! ! ! ! !

I have tables like
workersmaster(workerid,workername,joiningdate,stat us)
Workerdetail (Workerid,workername,entreetime,attendance)
timing(timing(0900to1500)

Now I wanted to make report as mentioned above, someone have any idea how can i do this?

Thanks
Ben

 

[CJ_London]

Hi Ben,

Something like this will get you started

TRANSFORM Count(WorkerDetail.attendance) AS CAttendance 
SELECT WorkersMaster.WorkerName
FROM WorkersMaster INNER JOIN WorkerDetail ON WorkersMaster.WorkerID= WorkerDetail.WorkerID
GROUP BY WorkersMaster.WorkerName
PIVOT WorkerDetail.entreetime

You might find it easier to to use the query builder and select crosstab to manage the construction

 

[Ben Saeed]

Hi,
Indeed your every reply make my access knowledge upgrade, thanks alot for your replies.

Problem is I wanted to put timings as default like heading:
0900-1000-1100-1200-1300-1400-1500-1600-1700. thats why I made table timing which contains timing 0900 to 1700.

I want whatever the worker get in on the specific timing like 0900 then it should be shown under the heading like this:

Name !09:00!10:00!11:00!12:00!13:00!14:00!15:00!16:00!17:00
------!-----!------!-----!------!-----!------!-----!-----!-----
Test1 !Prsnt! ! ! ! ! !
test2 ! ! !Prsnt! ! ! !
test3 ! !Prsnt! ! ! !

May be the structure i made is wrong, please help me with your kind advise.

Regards,
BEN

 

[CJ_London]

To fix the columns the end of the code I sent you would be something like

PIVOT WorkerDetail.entreetime in (1,2,3)

but

PIVOT WorkerDetail.entreetime in (09:00,10:00,11:00)

won't work. You need to change the column value into something that will - perhaps text but ideally numeric to ensure you get the right order.

Similarly the prsnt value to appear, you are not being clear - do you want to display just the 'check in' time or do you want something to appear for each hour the person is there? e.g. in your example test1 was there between 9 and 10 - is that all you need to know?

For us to help more, you need to provide more information. You need to tell us what data you actually have available - field name, field type, examples of content and be much clearer about what you want to report

 

[Ben Saeed]

Its OK I made it.

I have putted PIVOT WorkerDetail.entreetime in (0900,1000,1100).

Thanks alot and have a nice weekend

Ben