I want to pull data with an MS Access crosstab query so I can bind it to a report. When I load the page I get a Run-time error'3637': Cannot use the crosstab of a non-fixed column as a subquery.
我想用MS Access交叉表查询提取数据,以便将其绑定到报表。当我加载页面时,我得到一个运行时错误'3637':不能使用非固定列的交叉表作为子查询。
I would like a way to get back a fixed grid when I run the query and if the cell is null display a zero.
我想在运行查询时找回固定网格的方法,如果单元格为空则显示零。
1 个解决方案
#1
Subquery? That's one of my weaknesses when it comes to Access so I can't help you there. I'd suggest posting the SQL of the query though so others can take a look. Also what happens when you run the query?
子查询?这是我在Access方面的弱点之一,所以我无法帮助你。我建议发布查询的SQL,以便其他人可以看看。运行查询时会发生什么?
The following is a query that I'm using to give me costs for the last ten years for a given unit. TRANSFORM Sum(ServiceRecords.srTotalCost) AS AnnualCost SELECT ServiceRecords.srEquipmentID FROM ServiceRecords GROUP BY ServiceRecords.srEquipmentID PIVOT "C" & DateDiff("yyyy",[srServiceDate],Date()) In ("C9","C8","C7","C6","C5","C4","C3","C2","C1","C0");
以下是我用来给出特定单位过去十年成本的查询。 TRANSFORM Sum(ServiceRecords.srTotalCost)AS AnnualCost SELECT ServiceRecords.srEquipmentID FROM ServiceRecords GROUP BY ServiceRecords.srEquipmentID PIVOT“C”&DateDiff(“yyyy”,[srServiceDate],Date())In(“C9”,“C8”,“ C7" , “C6”, “C5”, “C4”, “C3”, “C2”, “C 1”, “C0”);
The trick is after the PIVOT. As I want the last ten years worth of data the "C" & DateDiff portion sets up a string variable call C0 to C9. The portion after the In tells which column to stuff things into.
诀窍是在PIVOT之后。由于我想要过去十年的数据,“C”和DateDiff部分设置一个字符串变量调用C0到C9。 In之后的部分告诉哪些列填充。
Another query which pulls in data about the Equipment calls this query. The term we generally use for such is stacked queries.
提取有关设备的数据的另一个查询调用此查询。我们通常用于这样的术语是堆叠查询。
If this isn't enough to get you going please indicate what type of data you are trying to create a cross tab.
如果这还不足以让您前进,请指明您尝试创建交叉表的数据类型。
Fellow Access MVP, Allen Browne has a good page on this topic. Crosstab query techniques
获得MVP的同事,艾伦布朗在这个主题上有一个很好的页面。交叉表查询技术
#1
Subquery? That's one of my weaknesses when it comes to Access so I can't help you there. I'd suggest posting the SQL of the query though so others can take a look. Also what happens when you run the query?
子查询?这是我在Access方面的弱点之一,所以我无法帮助你。我建议发布查询的SQL,以便其他人可以看看。运行查询时会发生什么?
The following is a query that I'm using to give me costs for the last ten years for a given unit. TRANSFORM Sum(ServiceRecords.srTotalCost) AS AnnualCost SELECT ServiceRecords.srEquipmentID FROM ServiceRecords GROUP BY ServiceRecords.srEquipmentID PIVOT "C" & DateDiff("yyyy",[srServiceDate],Date()) In ("C9","C8","C7","C6","C5","C4","C3","C2","C1","C0");
以下是我用来给出特定单位过去十年成本的查询。 TRANSFORM Sum(ServiceRecords.srTotalCost)AS AnnualCost SELECT ServiceRecords.srEquipmentID FROM ServiceRecords GROUP BY ServiceRecords.srEquipmentID PIVOT“C”&DateDiff(“yyyy”,[srServiceDate],Date())In(“C9”,“C8”,“ C7" , “C6”, “C5”, “C4”, “C3”, “C2”, “C 1”, “C0”);
The trick is after the PIVOT. As I want the last ten years worth of data the "C" & DateDiff portion sets up a string variable call C0 to C9. The portion after the In tells which column to stuff things into.
诀窍是在PIVOT之后。由于我想要过去十年的数据,“C”和DateDiff部分设置一个字符串变量调用C0到C9。 In之后的部分告诉哪些列填充。
Another query which pulls in data about the Equipment calls this query. The term we generally use for such is stacked queries.
提取有关设备的数据的另一个查询调用此查询。我们通常用于这样的术语是堆叠查询。
If this isn't enough to get you going please indicate what type of data you are trying to create a cross tab.
如果这还不足以让您前进,请指明您尝试创建交叉表的数据类型。
Fellow Access MVP, Allen Browne has a good page on this topic. Crosstab query techniques
获得MVP的同事,艾伦布朗在这个主题上有一个很好的页面。交叉表查询技术