Click here to Skip to main content
15,748,615 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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.
Updated 13-Mar-11 16:32pm
OriginalGriff 10-Mar-11 3:15am    
I was going to "Improve Question" for you, with a code block to preserve the formatting, and make the question a lot more readble.
Then I found there *is* no formatting...
Edit you question, and break the statement up into indented lines so you (and we!)can see what is going on.
At the moment, it is a mess of Joins, brackets, and so forth that I for one am not going to wade through to check!
Dalek Dave 10-Mar-11 3:32am    
This question is virtually unreadable, so probably unanswerable.
Evolve or Die!
jack_th 13-Mar-11 22:25pm    
Thank,sir. I will do that(improve it).

1 solution

Before you make pivot table, try to create correct SELECT query with COUNT function.
You don't need to add iif function, COUNT function will count records for each working_state.
SELECT [tb_working_info].[working_state] AS [state_name], COUNT([tb_working_info].[working_state]) AS [state_count], ([tb_employee].[emp_name]+' '+[tb_employee].[emp_lastname]) AS [Employee_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_working_info].[working_state], [tb_employee].[emp_name], [tb_employee].[emp_lastname], [tb_project].[pro_name]
ORDER BY [tb_working_info].[working_state], [tb_employee].[emp_name]

I hope it is helpful.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900