C＃如何通过Ms Access中的Transform / Pivot命令进行排序？ [英] C# How do I Order By my Transform/Pivot in Ms Access?

问题描述

使用此代码：

  TRANSFORM SUM（金额）SELECT DISTINCT IS_Accounts FROM [TABLE] GROUP BY IS_Accounts PIVOT状态
  

我可以为我的目的创建一个迷你报告摘要，重要的东西。

这是它的样子：

然而，帐户栏目全部无序，其中一些需要优先于其他栏目。为此，我写了一个查询并在主数据库上进行了测试。

 ORDER BY IIf（[IS_Accounts] ='收入'，1，IIf（[IS_Accounts] ='服务成本'，2，IIf（[IS_Accounts] ='经营费用]，3，IIf（[IS_Accounts] ='其他收入/费用'，4，IIf （[IS_Accounts] ='所得税'，5,6））））））ASC）; 
  

当我在主数据库上运行它时，如该模式所示，但将此插入到我的Transform / Pivot查询中，我遇到了按子句与子句排序冲突错误。如果这个操作是不允许的，我该如何命令我的透视数据？

请注意，我使用IIF而不是一个案例，这是因为MS Access不能使用数据库。

我打算做的是让我的主轴的最后一行遵循正确的模式。

解决方案

使用表达式在查询中构建一个名为[Sort]（或任何你想要的）的字段：
$ b <$ c $ [IS_Accounts] ='收入'，1，[IS_Accounts] ='服务成本'，2，[IS_Accounts] ='运营费用'，3，[IS_Accounts] ='其他收入/费用'，4， [IS_Accounts] ='所得税'，5，True，6）

现在将该字段分配为CROSSTAB中的另一个RowHeading。 p>

我假设您已经有了这些预算项目的查找表，这些预算项目用作进入数据记录的来源。该序列号可以是该查找表中的另一个字段。将表格加入CROSSTAB以拉入序列号。是否将实际的描述性文本保存到数据记录中，而不是查找表中的PK？重复此文本将更快地使用Access 2GB限制，但这可能需要很长时间，具体取决于您的数据量。另外，连接在完成数字数据时应该更有效。

Using this code:

TRANSFORM SUM(Amount) SELECT DISTINCT IS_Accounts FROM [TABLE] GROUP BY IS_Accounts PIVOT Status

I am capable of creating a mini-report summary for my purposes, generated from a much larger database with only the important stuff.

This is how it looks like:

However the Accounts column is all out of order, some of these need to be above the others in priority. To do this, I've written a query and tested it on the main database.

" ORDER BY IIf([IS_Accounts] = 'Revenues' , 1 , IIf([IS_Accounts] = 'Cost of Services', 2 , IIf([IS_Accounts] = 'Operating Expenses', 3, IIf([IS_Accounts] = 'Other income/expense', 4, IIf([IS_Accounts] = 'Income Taxes', 5, 6))))) ASC ");

When I run this on my main database, it properly gets sorted with revenues at the top and the rest as the pattern suggests, however plugging this to my Transform/Pivot query, I encounter a "Group by clause conflicts with Order by clause" error. If this operation is not allowed, how can I order my pivot data?

Note that I used IIF instead of a case, this is because MS Access cannot use when unlike other databases.

All I intend to do is make it so that the final rows of my pivot follow the correct pattern.

解决方案

Construct a field in query called [Sort] (or whatever you want) with expression:

Switch([IS_Accounts] = 'Revenues', 1, [IS_Accounts] = 'Cost of Services', 2, [IS_Accounts] = 'Operating Expenses', 3, [IS_Accounts] = 'Other income/expense', 4, [IS_Accounts] = 'Income Taxes', 5, True, 6)

Now assign that field as another RowHeading in the CROSSTAB.

I presume you already have a lookup table of these budget items used as source for entry into data records. This sequence number could be another field in that lookup table. Join the table into the CROSSTAB to pull in the sequence number. Are you saving the actual descriptive text into your data records instead of a PK from the lookup table? Repeating this text will use up Access 2GB limit faster but that could still take a very long time depending on your data volume. Also, joins are supposed to perform more efficiently when done on numeric data.

这篇关于C＃如何通过Ms Access中的Transform / Pivot命令进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！