查询以查找重复项并添加别名列 [英] Query to Find Duplicates and Add Alias Column

问题描述

我有一个名为testdb的MS Access数据库，表名称为table1.

I have an MS Access database named testdb, with table name table1.

该表具有2个字段BinNo和Prodcode.

The table has 2 fields BinNo and Prodcode.

BinNo有很多重复的行，我想将BinNo分组到有重复的行，以创建新的别名列.

BinNo has many duplicate rows and I want to group BinNo wherever it has a duplicate row to create new alias column.

给出以下数据:

+---------+----------+
|  BinNo  | Prodcode |
+---------+----------+
| Bin no1 | Pro 1    |
| Bin no1 | Pro 2    |
| Bin no1 | Pro 3    |
| Bin no2 | Pro 4    |
| Bin no2 | Pro 5    |
+---------+----------+

这是预期的结果:

+---------+-----------+-----------+-----------+
|  BinNo  | Prodcode1 | Prodcode2 | Prodcode3 |
+---------+-----------+-----------+-----------+
| Bin no1 | Pro 1     | Pro 2     | Pro 3     |
| Bin no2 | Pro 4     | Pro 5     |           |
+---------+-----------+-----------+-----------+

推荐答案

目前尚不清楚您要做什么，但是要执行您要求的操作，首先要在表中添加两列.

It is unclear what you are trying to do, but to do what you asked start by adding two columns to your table.

通常在某个时候需要一个主键，因此即使我在这里不使用ID列也是一个很好的做法.然后添加带有最终行标题的列.我将此列称为CodeOrder.要将CodeOrder列转换为行，请进行交叉表查询.

you usually need a primary key at some point so adding the ID column is good practice even if I don't use it here. Then add a column with your eventual row titles. I called this column CodeOrder. To convert the CodeOrder column to a row make a crosstab query.

https://support.office.com/en-us/article/make-summary-data-easier-to-read-by-using-a-crosstab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8 Open the query designer and make it look like:

注意变量的交叉表和总计行.当您在功能区上找到并选择交叉表时，将添加这些行.请注意，为值变量选择正确的摘要函数是很重要的，这首先是针对这些数据的.我希望这会有所帮助:)

Note the Crosstab and total rows on the variables. These rows will be added when you find and select crosstab on the ribbon. Note it is important to choose the right summary function for the value variable which is first for these data. I hope this helps :)

这篇关于查询以查找重复项并添加别名列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！