I have an access database that has an id referring to a customer who has trucks of different sizes. currently the table looks something like this:
我有一个访问数据库，它的id指的是拥有不同型号卡车的客户。目前的表格是这样的:
id.....tire size
1......30
1......30
1......31
1......31
2......32
What I want to achieve is something like this:
我想实现的是这样的:
id.....30.....31.....32
1......2......2......0
2......0......0......0
where it counts the number of occurrences of a specific tire size and inputs it into the respective tire size column.
计算某一轮胎尺寸出现的次数，并将其输入到相应的轮胎尺寸栏中。
3 个解决方案
#1
2
In order to display the data as you have written it, you will need to do a crosstab query. The code below should achieve what you want
为了显示您所编写的数据，您需要执行交叉表查询。下面的代码应该实现您想要的
TRANSFORM Nz(Count([YourTable].[Tire Size]),0) AS [CountOfTire Size]
SELECT [YourTable].[ID]
FROM [YourTable]
GROUP BY [YourTable].[ID]
PIVOT [YourTable].[Tire Size];
#2
0
The first step would be a query like:
第一步是查询如下:
select tire_size, COUNT(id) from mytable
GROUP BY tire_size
(I put the "special magic" parts of that query in UPPER CASE for emphasis.)
(我将该查询的“特殊魔力”部分用大写字母表示强调。)
In the MS-Access query-builder, grouping features are accessed by clicking a button that looks vaguely like an "E" (actually, a Greek "epsilon" character), if I recall correctly. This adds a new "grouping" row to the query-builder grid.
在MS-Access query-builder中，通过单击一个看似“E”(实际上是希腊语“epsilon”字符)的按钮来访问分组特性，如果我没记错的话。这将向查询生成器网格添加一个新的“分组”行。
This will produce (as you will quickly see) a row-by-row result with tire-size and the count of id's for that tire-size.
这将生成(正如您将很快看到的)逐行结果，其中包含了tire大小和id的计数。
Many other variations of this are possible. Read the MS-Access on-line help which discusses this feature: they did a very good job with it.
许多其他的变化是可能的。阅读MS-Access在线帮助，其中讨论了这个特性:他们在这方面做得很好。
The essential idea is the GROUP BY clause: this says that each distinct value of tire_size forms a "group." (Yes, you can GROUP BY more than one column, in which each unique combination of values forms one group.) Then, you specify so-called "domain aggregate functions, such as COUNT(), AVG(), SUM(), to produce summary statistics for each group.
最基本的思想是GROUP BY子句:这表示tier_size的每个不同值组成一个“GROUP”。(是的，可以对多个列进行分组，其中每个值的唯一组合组成一个组。)然后，指定所谓的“域聚合函数，例如COUNT()、AVG()、SUM()，以生成每个组的汇总统计信息。
Every GROUP BY column must appear in the SELECT clause, and every other item that appears there must be a domain aggregate function. (Which, if you think about it, makes perfect sense ...)
每一组一列都必须出现在SELECT子句中，而出现在该子句中的每一项都必须有域聚合函数。(如果你仔细想想，就会发现这是完全合理的……)
(Fortunately, MS-Access's query builder does a good job of "hiding" all that. You can build a grouping-query interactively, thanks to that "epsilon" button. But it's useful then to look at the "SQL View" to see what it did in SQL terms.)
(幸运的是，MS-Access的查询构建器很好地“隐藏”了所有这些。由于有了“epsilon”按钮，您可以交互式地构建一个分组查询。但是，查看“SQL视图”以了解它在SQL术语中的作用是很有用的。
#3
0
Use the 'GROUP BY' aggregator
使用“GROUP BY”聚合器。
You'll need something like this:
你需要这样的东西:
SELECT
tyre_size,
count(id)
FROM tablename
GROUP BY
tire_size
#1
2
In order to display the data as you have written it, you will need to do a crosstab query. The code below should achieve what you want
为了显示您所编写的数据，您需要执行交叉表查询。下面的代码应该实现您想要的
TRANSFORM Nz(Count([YourTable].[Tire Size]),0) AS [CountOfTire Size]
SELECT [YourTable].[ID]
FROM [YourTable]
GROUP BY [YourTable].[ID]
PIVOT [YourTable].[Tire Size];
#2
0
The first step would be a query like:
第一步是查询如下:
select tire_size, COUNT(id) from mytable
GROUP BY tire_size
(I put the "special magic" parts of that query in UPPER CASE for emphasis.)
(我将该查询的“特殊魔力”部分用大写字母表示强调。)
In the MS-Access query-builder, grouping features are accessed by clicking a button that looks vaguely like an "E" (actually, a Greek "epsilon" character), if I recall correctly. This adds a new "grouping" row to the query-builder grid.
在MS-Access query-builder中，通过单击一个看似“E”(实际上是希腊语“epsilon”字符)的按钮来访问分组特性，如果我没记错的话。这将向查询生成器网格添加一个新的“分组”行。
This will produce (as you will quickly see) a row-by-row result with tire-size and the count of id's for that tire-size.
这将生成(正如您将很快看到的)逐行结果，其中包含了tire大小和id的计数。
Many other variations of this are possible. Read the MS-Access on-line help which discusses this feature: they did a very good job with it.
许多其他的变化是可能的。阅读MS-Access在线帮助，其中讨论了这个特性:他们在这方面做得很好。
The essential idea is the GROUP BY clause: this says that each distinct value of tire_size forms a "group." (Yes, you can GROUP BY more than one column, in which each unique combination of values forms one group.) Then, you specify so-called "domain aggregate functions, such as COUNT(), AVG(), SUM(), to produce summary statistics for each group.
最基本的思想是GROUP BY子句:这表示tier_size的每个不同值组成一个“GROUP”。(是的，可以对多个列进行分组，其中每个值的唯一组合组成一个组。)然后，指定所谓的“域聚合函数，例如COUNT()、AVG()、SUM()，以生成每个组的汇总统计信息。
Every GROUP BY column must appear in the SELECT clause, and every other item that appears there must be a domain aggregate function. (Which, if you think about it, makes perfect sense ...)
每一组一列都必须出现在SELECT子句中，而出现在该子句中的每一项都必须有域聚合函数。(如果你仔细想想，就会发现这是完全合理的……)
(Fortunately, MS-Access's query builder does a good job of "hiding" all that. You can build a grouping-query interactively, thanks to that "epsilon" button. But it's useful then to look at the "SQL View" to see what it did in SQL terms.)
(幸运的是，MS-Access的查询构建器很好地“隐藏”了所有这些。由于有了“epsilon”按钮，您可以交互式地构建一个分组查询。但是，查看“SQL视图”以了解它在SQL术语中的作用是很有用的。
#3
0
Use the 'GROUP BY' aggregator
使用“GROUP BY”聚合器。
You'll need something like this:
你需要这样的东西:
SELECT
tyre_size,
count(id)
FROM tablename
GROUP BY
tire_size