具有水平记录的交叉表查询 |微软访问 [英] Cross Tab Query with Records Going Horizontally | MS Access
问题描述
基本上我已经创建了一个交叉表查询，但我需要一些帮助来水平显示更多的记录.
我已经查看了以下帖子中的一些指示以取得进展，但仍需要一些帮助，我真的很感激能得到更多帮助以正确完成最后一部分.
使用以下查询，我能够让所有颜色水平显示，没有问题:
TRANSFORM First(ColourNames.AssignedColour) AS 随便选择颜色名称.产品IDFROM (SELECT t1.ProductID, t1.[AssignedColour], "Colour" & Format(COUNT(*),"000") AS ColourName FROM ProductColor AS t1 INNER JOIN ProductColor AS t2 ON (t1.[AssignedColour]>;=t2.[AssignedColour]) AND (t1.ProductID = t2.ProductID) GROUP BY t1.ProductID, t1.[AssignedColour]) AS ColourNamesGROUP BY ColourNames.ProductIDPIVOT ColourNames.ColourName;结果:
但是我在理解如何在每个 ProductID 的每个 AssignedColour 之后水平列出 ColorFinding 时遇到了一些麻烦，如下所示:
第二次尝试:
CrossTab 查询 1:
TRANSFORM First(ColourNames.AssignedColour) AS 随便选择颜色名称.产品IDFROM (SELECT t1.ProductID, t1.[AssignedColour], "Colour" & Format(COUNT(*),"000") AS ColourName FROM ProductColor AS t1 INNER JOIN ProductColor AS t2 ON (t1.[AssignedColour]>;=t2.[AssignedColour]) AND (t1.ProductID = t2.ProductID) GROUP BY t1.ProductID, t1.[AssignedColour]) AS ColourNamesGROUP BY ColourNames.ProductIDPIVOT ColourNames.ColourName;CrossTab 查询 2:
TRANSFORM First(ColourNames.ColourFinding) AS 随便选择颜色名称.产品IDFROM (SELECT t1.ProductID, t1.[ColourFinding], "Colour" & Format(COUNT(*),"000_Finding") AS ColourName FROM ProductColor AS t1 INNER JOIN ProductColor AS t2 ON (t1.[ColourFinding]>;=t2.[ColourFinding]) AND (t1.ProductID = t2.ProductID) GROUP BY t1.ProductID, t1.[ColourFinding]) AS ColourNamesGROUP BY ColourNames.ProductIDPIVOT ColourNames.ColourName;我现在如何组合 2 的结果以获得所需的结果?
更新 2:
我使用嵌套子查询方法得到不正确的结果:
SELECT t1.ProductID, t1.[AssignedColour] AS 数据，颜色"&格式(计数(*)，000")AS CntFROM ProductColor AS t1 INNER JOIN ProductColor AS t2 ON (t1.ProductID = t2.ProductID) AND (t1.[AssignedColour]>=t2.[AssignedColour])GROUP BY T1.ProductID, T1.AssignedColourUNION SELECT t1.ProductID, t1.[ColourFinding], 颜色"&格式(计数(*)，000")&_发现"FROM ProductColor AS t1 INNER JOIN ProductColor AS t2 ON (t1.ProductID = t2.ProductID) AND (t1.[ColourFinding]>=t2.[ColourFinding])GROUP BY T1.ProductID, T1.ColourFinding;如果以下是我的数据:
我的预期结果是:
使用嵌套子查询，我得到以下结果:
参考链接中描述的耦合技术:
http://allenbrowne.com/ser-67.html#MultipleValues
如何创建多值Access 2013 中的交叉表查询?
将您的两个 CROSSTAB 查询加入到 Products - 唯一产品记录的主表中.按照您想要的任何顺序将字段拉入查询中.
SELECT Products.ProductID, ProductName, Colour001, Colour002, Colour003, Colour001_Finding, Colour002_Finding, Colour003_FindingFROM Query2 RIGHT JOIN (Query1 RIGHT JOIN Products ON Query1.ProductID = Products.ProductID) ON Query2.ProductID = Products.ProductID;这确实限制为 3 组数据.所以使用通配符拉入字段但不能指定顺序.SELECT Products.ProductID, ProductName, Query1.*, Query2.* ...
另一种方法涉及一个 UNION 查询，然后将在 CROSSSTAB 中使用该查询.这很可能是性能最慢的方法.
查询 1:数据联合
SELECT ID、ProductID、AssignedColour AS D、Assigned"AS Cat，颜色"&DCount("*","ProductColor","ProductID='" & ProductID & "' AND ID<" & ID)+1 AS GrpSeq FROM ProductColorUNION SELECT ID、ProductID、ColourFinding、Finding"、Colour"&DCount("*","ProductColor","ProductID='" & ProductID &"' AND ID<" & ID)+1 &_发现"从产品颜色；查询 2:
TRANSFORM First(DataUNION.D) AS FirstOfD选择数据UNION.ProductID来自 DataUNIONGROUP BY DataUNION.ProductIDPIVOT DataUNION.GrpSeq;这是我为 UNION 使用嵌套查询生成行编号的内容.不幸的是，CROSSTAB 不喜欢它.因此，最后的建议是将这些记录写入临时表并将该表用作 CROSSTAB 的源.
SELECT ProductID, [AssignedColour] AS Data, (SELECT "Colour" & Format(COUNT(*)+1,"000")从 ProductColor AS T1 WHERE ProductColor.ProductID = T1.ProductID AND ProductColor.[ID]>T1.[ID]) AS GrpSeq从产品颜色UNION SELECT ProductID, [ColourFinding] AS Data, (SELECT "Colour" & Format(COUNT(*)+1,"000_Finding")从 ProductColor AS T1 WHERE ProductColor.ProductID = T1.ProductID AND ProductColor.[ID]>T1.[ID]) AS GrpSeq从产品颜色；VBA 代码如:
CurrentDb.Execute DELETE FROM ProductColorTEMP"CurrentDb.执行INSERT INTO ProductColorTEMP SELECT * FROM DataUNION"；Essentially I have created a cross tab query, but I need some help presenting more of the records horizontally.
I have reviewed some of the pointers from the following post to progress forward, but still need some help, I would really appreciate a bit more help to get this final part correct.
How can I Create a Multi-Value CrossTab Query in Access 2013?
I currently have a table with the following data:
Using the following Query I was able to get all the colours to display horizontally with no issue:
TRANSFORM First(ColourNames.AssignedColour) AS whatever
SELECT ColourNames.ProductID
FROM (SELECT t1.ProductID, t1.[AssignedColour], "Colour" & Format(COUNT(*),"000") AS ColourName FROM ProductColor AS t1 INNER JOIN ProductColor AS t2 ON (t1.[AssignedColour]>=t2.[AssignedColour]) AND (t1.ProductID = t2.ProductID) GROUP BY t1.ProductID, t1.[AssignedColour]) AS ColourNames
GROUP BY ColourNames.ProductID
PIVOT ColourNames.ColourName;
Result:
But I am having some trouble understanding how I can also list the ColorFinding Horizontally after each AssignedColour for each ProductID like this:
2nd Attempt:
CrossTab Query 1:
TRANSFORM First(ColourNames.AssignedColour) AS whatever
SELECT ColourNames.ProductID
FROM (SELECT t1.ProductID, t1.[AssignedColour], "Colour" & Format(COUNT(*),"000") AS ColourName FROM ProductColor AS t1 INNER JOIN ProductColor AS t2 ON (t1.[AssignedColour]>=t2.[AssignedColour]) AND (t1.ProductID = t2.ProductID) GROUP BY t1.ProductID, t1.[AssignedColour]) AS ColourNames
GROUP BY ColourNames.ProductID
PIVOT ColourNames.ColourName;
CrossTab Query 2:
TRANSFORM First(ColourNames.ColourFinding) AS whatever
SELECT ColourNames.ProductID
FROM (SELECT t1.ProductID, t1.[ColourFinding], "Colour" & Format(COUNT(*),"000_Finding") AS ColourName FROM ProductColor AS t1 INNER JOIN ProductColor AS t2 ON (t1.[ColourFinding]>=t2.[ColourFinding]) AND (t1.ProductID = t2.ProductID) GROUP BY t1.ProductID, t1.[ColourFinding]) AS ColourNames
GROUP BY ColourNames.ProductID
PIVOT ColourNames.ColourName;
How can I now combine the results of the 2 to get the needed result?
Update 2:
I am getting incorrect results using the Nested Subquery Approach:
SELECT t1.ProductID, t1.[AssignedColour] AS Data, "Colour" & Format(COUNT(*),"000") AS Cnt
FROM ProductColor AS t1 INNER JOIN ProductColor AS t2 ON (t1.ProductID = t2.ProductID) AND (t1.[AssignedColour]>=t2.[AssignedColour])
GROUP BY T1.ProductID, T1.AssignedColour
UNION SELECT t1.ProductID, t1.[ColourFinding], "Colour" & Format(COUNT(*),"000") & "_Finding"
FROM ProductColor AS t1 INNER JOIN ProductColor AS t2 ON (t1.ProductID = t2.ProductID) AND (t1.[ColourFinding]>=t2.[ColourFinding])
GROUP BY T1.ProductID, T1.ColourFinding;
If following is my data:
My Expected Result is:
Using the Nested Sub Query I get the following:
Couple techniques described in referenced links:
http://allenbrowne.com/ser-67.html#MultipleValues
How can I Create a Multi-Value CrossTab Query in Access 2013?
Join your two CROSSTAB queries to Products - master table of unique product records. Pull fields into query in whatever order you want.
SELECT Products.ProductID, ProductName, Colour001, Colour002, Colour003, Colour001_Finding, Colour002_Finding, Colour003_Finding
FROM Query2 RIGHT JOIN (Query1 RIGHT JOIN Products ON Query1.ProductID = Products.ProductID) ON Query2.ProductID = Products.ProductID;
That does limit to 3 sets of data. So use wildcard to pull in fields but then cannot specify order.
SELECT Products.ProductID, ProductName, Query1.*, Query2.* ...
Another approach involves a UNION query that would then be used in CROSSSTAB. This is quite likely the slowest performance approach.
Query1: DataUNION
SELECT ID, ProductID, AssignedColour AS D, "Assigned" AS Cat, "Colour" & DCount("*","ProductColor","ProductID='" & ProductID & "' AND ID<" & ID)+1 AS GrpSeq FROM ProductColor
UNION SELECT ID, ProductID, ColourFinding, "Finding", "Colour" & DCount("*","ProductColor","ProductID='" & ProductID & "' AND ID<" & ID)+1 & "_Finding" FROM ProductColor;
Query2:
TRANSFORM First(DataUNION.D) AS FirstOfD
SELECT DataUNION.ProductID
FROM DataUNION
GROUP BY DataUNION.ProductID
PIVOT DataUNION.GrpSeq;
Here is what I have for UNION with nested queries to generate row numbering. Unfortunately, CROSSTAB doesn't like it. So, final suggestion is to write these records to a temp table and use that table as source for CROSSTAB.
SELECT ProductID, [AssignedColour] AS Data, (SELECT "Colour" & Format(COUNT(*)+1,"000")
FROM ProductColor AS T1 WHERE ProductColor.ProductID = T1.ProductID AND ProductColor.[ID]>T1.[ID]) AS GrpSeq
FROM ProductColor
UNION SELECT ProductID, [ColourFinding] AS Data, (SELECT "Colour" & Format(COUNT(*)+1,"000_Finding")
FROM ProductColor AS T1 WHERE ProductColor.ProductID = T1.ProductID AND ProductColor.[ID]>T1.[ID]) AS GrpSeq
FROM ProductColor;
VBA code like:
CurrentDb.Execute "DELETE FROM ProductColorTEMP"
CurrentDb.Execute "INSERT INTO ProductColorTEMP SELECT * FROM DataUNION"
这篇关于具有水平记录的交叉表查询 |微软访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
