Multiple quaries

[musaad]

Hello Access Guru's..

I need your help on this quarry...

I have table with the below data

tbl_attend_log

ST_Name ST_Date ST_Attend
Tom 01/01/2014 1
Mat 01/01/2014 1
Jam 01/01/2014 1
Tom 02/01/2014 2
Mat 02/01/2014 1
Jam 02/01/2014 3
Tom 03/01/2014 1
Mat 03/01/2014 2
Jam 03/01/2014 1


ST_attend column values represents as below
1 = OnDuty
2 = AnnualLeave
3 = DutyTravel

when I give a date range I need to quarry to display as below..

"ST_Name" "OnDuty" "AnnualLeave" "DutyTravel"
Tom 2 1 0
Mat 2 1 0
Jam 2 0 1


I manage to wright a quarry as below

SELECT tbl_attend_log.ST_Name, Count(tbl_attend_log.ST_Attend) AS OnDuty
FROM tbl_attend_log
WHERE (((tbl_attend_log.ST_Date) Between #01/04/2014# And #30/04/2014#) AND ((tbl_attend_log.ST_Attend) Like '1'))
GROUP BY tbl_attend_log.ST_Name;

Output

ST_Name OnDuty
Tom 2
Mat 2
Jam 2


How can I add other columns and criteria’s ?

 

[CazB]

Have you tried a crosstab query?

Oh, and to simplify things, it would probably work best if you had a separate table to 'lookup' what the 1,2 and 3 mean in ST_Attend...

 

[pr2-eugin]

Hello musaad, try this query.

SELECT tbl_attend_log.ST_Name, Sum(IIF(tbl_attend_log.ST_Attend = 1, 1, 0)) AS OnDuty
                             , Sum(IIF(tbl_attend_log.ST_Attend = 2, 1, 0)) AS AnnualLeave
                             , Sum(IIF(tbl_attend_log.ST_Attend = 3, 1, 0)) AS DutyTravel
FROM tbl_attend_log
WHERE tbl_attend_log.ST_Date Between #01/04/2014# And #30/04/2014#
GROUP BY tbl_attend_log.ST_Name;

 

[CazB]

Or, if you add another table in to look up the 'descriptions' : (I called mine tbl_Attend_Description, just contains the st_attend 'number' and st_attend_desc 'text'

PARAMETERS [Start Date?] DateTime, [End Date?] DateTime;
TRANSFORM Count(tbl_Attend_log.st_date) AS CountOfst_date
SELECT tbl_Attend_log.st_name
FROM tbl_Attend_log LEFT JOIN tbl_Attend_Description ON tbl_Attend_log.st_attend = tbl_Attend_Description.ST_Attend
WHERE (((tbl_Attend_log.st_date) Between [Start Date?] And [End Date?]))
GROUP BY tbl_Attend_log.st_name
PIVOT tbl_Attend_Description.st_attend_desc;

Advantage of doing it this way is that if you then add more 'reasons' then you can just add them into the table and you won't need to modify your query...

 

[pr2-eugin]

^^^ +1 ^^^

 

[musaad]

Hello musaad, try this query.

SELECT tbl_attend_log.ST_Name, Sum(IIF(tbl_attend_log.ST_Attend = 1, 1, 0)) AS OnDuty
                             , Sum(IIF(tbl_attend_log.ST_Attend = 2, 1, 0)) AS AnnualLeave
                             , Sum(IIF(tbl_attend_log.ST_Attend = 3, 1, 0)) AS DutyTravel
FROM tbl_attend_log
WHERE tbl_attend_log.ST_Date Between #01/04/2014# And #30/04/2014#
GROUP BY tbl_attend_log.ST_Name;

Thanks a lot pr2-eugin it works ..!!!!!1