MS Access-如何将增量值添加到表中的组 [英] MS Access - How to add incremental values to groups in table

问题描述

我在MS Access 2007中有一个庞大的项目表，可以显示在产品页面上，它看起来像这样:

I have a huge table of items in MS Access 2007 to appear on product pages, which looks similar to this:

ID   | GroupID | ItemName       | Sort | Type

225  | 5       | Specifications | 0    | Text
226  | 5       | Option-Red     | 0    | Product
227  | 5       | Option-Blue    | 0    | Product
228  | 7       | Specifications | 0    | Text
229  | 7       | Option-Green   | 0    | Product
230  | 7       | Option-Orange  | 0    | Product
231  | 7       | Option-Pink    | 0    | Product
232  | 7       | Option-Black   | 0    | Product

每组中的第一项是规格项，然后是产品列表.

First item in each group is the specification item, and then a list of products.

他们需要按组添加排序"序列，以便所使用的系统将它们按正确的顺序放置，如下所示:

They need to have their 'sort' sequence added per-group so that the system being used will put them in their correct order, like this:

ID   | GroupID | ItemName       | Sort | Type

225  | 5       | Specifications | 0    | Text
226  | 5       | Option-Red     | 1    | Product
227  | 5       | Option-Blue    | 2    | Product
228  | 7       | Specifications | 0    | Text
229  | 7       | Option-Green   | 1    | Product
230  | 7       | Option-Orange  | 2    | Product
231  | 7       | Option-Pink    | 3    | Product
232  | 7       | Option-Black   | 4    | Product

但是我似乎无法在访问中创建一个实际可以做到的查询.任何人有任何想法吗?

But I can't seem to create a query in access that will actually work to do it. Anyone got any ideas?

非常感谢！

推荐答案

您应该可以使用以下内容:

You should be able to use the following:

select x.id, x.group_id, x.itemname, count(y.id) as sort, x.type
  from tbl x
  left outer join tbl y
    on x.group_id = y.group_id
   and y.id < x.id
 group by x.id, x.group_id, x.itemname, x.type

在以下位置查看小提琴: http://sqlfiddle.com/#!2/bc4e8/3/0

See fiddle at: http://sqlfiddle.com/#!2/bc4e8/3/0

我知道我在那个小提琴中使用了mysql，但是我不相信Access不支持的任何语法.您唯一需要做的就是将on子句包含在()中，我忘记了on子​​句如何与Access一起使用，但是知道它很奇怪.

I know that I used mysql in that fiddle but I am not using any syntax not supported by Access, I don't believe. The only thing you might have to do is enclose the on clause in () , I forget how the on clause works with Access but know it is bizarre.

这篇关于MS Access-如何将增量值添加到表中的组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！