I have a pivot sql (transform) in access database which retrieves working data from a table to show pivotly. And the command is:
TRANSFORM iif(isnull(count([tb_working_info].[working_state])),'0','count([tb_working_info].[working_state])') AS [status] SELECT ([tb_employee].[emp_name]+' '+[tb_employee].[emp_lastname]) AS Name FROM (([tb_project] RIGHT outer JOIN [tb_employee] ON [tb_project].[pro_id] = [tb_employee].[emp_pro_id]) LEFT JOIN tb_working_info ON [tb_employee].[emp_id] = [tb_working_info].[emp_id]) where [tb_working_info].[working_date] between #26/11/2010# and #25/12/2010# and [tb_employee].emp_startworking <=#25/12/2010# and [tb_employee].emp_status='work' GROUP BY [tb_employee].[emp_name], [tb_employee].[emp_lastname], [tb_project].[pro_name] order by [tb_employee].[emp_name] PIVOT Format([tb_working_info].[working_date],'d') in ('26','27','28','29','30','31','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25')
Then, I will get data which are the amount of "working_state" in each day to show.
But, can I change "count([tb_working_info].[working_state])" to be just "[tb_working_info].[working_state]" in order to show the value of [tb_working_info].[working_state]" instead? Or how can I do another way to get the result like this?
Thank you.