CODEWITHSUNDEEP
sqlms-access
Chuckun
Chuckun

Reputation: 37

MS Access - How to add incremental values to groups in table

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?

Thanks a lot!

Upvotes: 0

Views: 225

Answers (2)

Makketronix
Makketronix

Reputation: 1460

Updated for comment: You can use:

 Select * from (
 Select * from yourTableName ORDER BY yourTableName.Sort)
 ORDER BY yourTableName.GroupID

When you create simple query, go to the SQL mode and insert the code.

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

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

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

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.

Upvotes: 1

Related Questions