访问SQL转换枢轴问题? [英] Access SQL Transform pivot problem?

问题描述

我在Access数据库中有一个枢轴sql(转换)，它从表中检索工作数据以进行透视显示.命令是:

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'')

然后，我将获得每天要显示的"working_state"数量的数据.

但是，为了显示[tb_working_info].[working_state]的值，我可以将"count([tb_working_info].[working_state])"更改为"[tb_working_info].[working_state]"吗?或者如何显示?做另一种方式来获得这样的结果?

谢谢.

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.

推荐答案

在创建数据透视表之前，请尝试使用COUNT函数创建正确的SELECT查询. 您不需要添加iif函数，COUNT函数将对每个working_state的记录进行计数.

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.

这篇关于访问SQL转换枢轴问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！