访问:将行转换为具有许多不同行值的列 [英] Access: transform rows to columns with many different row-values

问题描述

让我开始说，我知道可以使用TRANSFORM命令将行转换为列.实际上，我已经在这个很棒的网站上了解到，有足够的理由让我注册:)

Let me start to say that I know that transforming rows to columns can be done with the TRANSFORM command. Actually, I've learned that on this great site, reason enough for me to register :)

我遇到的问题是，我想将行转换为列，而行中的每个值都不是唯一的列. 我将尝试通过我的Access表示例对其进行解释，请参见链接中的图片:

The problem I have is that I would like to transform rows into columns without every different value in the row is going to be a unique column. I'll try to explain it by an example of my Access table, see the picture in the link:

此查询显示产品(第一列loar_aid)，并带有描述产品的文字片段.每个产品都有一个值tekst1，tekst2和tekst3. 但是tregel1列最多可以为1个产品包含3个不同的文本.如果您知道我的意思，我想将此tregel1转换为3列:tregel1_firstValue，tregel1_secondValue，tregel1_thirdValue.

This query shows products (first column loar_aid), with pieces of text to describe the product. Every product has one value tekst1, tekst2 and tekst3. But column tregel1 can contain up to 3 different pieces of text for 1 product. I would like to transform this tregel1 to 3 columns: tregel1_firstValue, tregel1_secondValue, tregel1_thirdValue, if you know what I mean.

最终，我希望使用此表作为源标签(每列都是标签上的规则).因此，理想情况下，这些值应该彼此相邻.

Eventually I would like to make labels with this table as a source (every column a rule on the label). Therefore the values should ideally be next to each other.

有人可以帮助我解决这个问题吗? 帮助将不胜感激！

Could someone help me with this problem? Help will be greatly appreciated!

推荐答案

您需要每个唯一组的记录序列ID.以下内容依靠自动编号ID字段为原始表中的每个记录提供唯一的标识符，以定义DCount()表达式中的排序顺序.

You need a record sequence ID for each unique group. The following relies on an autonumber ID field to provide a unique identifier for each record in the original table to define sort order in DCount() expression.

TRANSFORM First(Table3.tregel1) AS FirstOftregel1
SELECT Table3.loar_aid, Table3.tekst2, Table3.tekst3
FROM Table3
GROUP BY Table3.loar_aid, Table3.tekst2, Table3.tekst3
PIVOT DCount("*","Table3","loar_aid='" & [loar_aid] & "' AND tekst1='" & [tekst1] & "' AND tekst2='" & [tekst2] & "' AND Nz(tekst3,'None')='" & Nz([tekst3],"None") & "' AND ID<" & [ID])+1;

请注意，使用DCount()或嵌套子查询的这种类型的查询在大型数据集上的执行速度会很慢.

Be aware this type of query using DCount() or a nested subquery can perform slowly on very large dataset.

这篇关于访问:将行转换为具有许多不同行值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！