0

I am trying to figure out how to create a column that says that this is 'x' of 'y' items. So, what I have is the following table:

Drawer ID   Drawer Name     Size            Qty     MaterialSchedule    PurchaseOrder   
253         5 Piece Drawer  15 1/2x3x20     4       19mm Tandem         DWR_TEST_001
254         5 Piece Drawer  15 1/2x3x20     4       19mm Tandem         DWR_TEST_001
255         5 Piece Drawer  15 1/2x3x20     4       19mm Tandem         DWR_TEST_001
256         5 Piece Drawer  15 1/2x3x20     4       19mm Tandem         DWR_TEST_001
257         5 Piece Drawer  17 1/2x3x20     4       19mm Tandem         DWR_TEST_001
261         5 Piece Drawer  17 1/2x3x20     4       19mm Tandem         DWR_TEST_001
265         5 Piece Drawer  17 1/2x3x20     4       19mm Tandem         DWR_TEST_001
269         5 Piece Drawer  17 1/2x3x20     4       19mm Tandem         DWR_TEST_001
260         5 Piece Drawer  17 1/2x6x20     4       19mm Tandem         DWR_TEST_001
264         5 Piece Drawer  17 1/2x6x20     4       19mm Tandem         DWR_TEST_001
268         5 Piece Drawer  17 1/2x6x20     4       19mm Tandem         DWR_TEST_001
272         5 Piece Drawer  17 1/2x6x20     4       19mm Tandem         DWR_TEST_001
248         5 Piece Drawer  26 1/2x3x20     5       19mm Tandem         DWR_TEST_001
249         5 Piece Drawer  26 1/2x3x20     5       19mm Tandem         DWR_TEST_001
250         5 Piece Drawer  26 1/2x3x20     5       19mm Tandem         DWR_TEST_001
251         5 Piece Drawer  26 1/2x3x20     5       19mm Tandem         DWR_TEST_001
252         5 Piece Drawer  26 1/2x3x20     5       19mm Tandem         DWR_TEST_001
258         5 Piece Drawer  17 1/2x4x20     8       19mm Tandem         DWR_TEST_001
259         5 Piece Drawer  17 1/2x4x20     8       19mm Tandem         DWR_TEST_001
262         5 Piece Drawer  17 1/2x4x20     8       19mm Tandem         DWR_TEST_001
263         5 Piece Drawer  17 1/2x4x20     8       19mm Tandem         DWR_TEST_001
266         5 Piece Drawer  17 1/2x4x20     8       19mm Tandem         DWR_TEST_001
267         5 Piece Drawer  17 1/2x4x20     8       19mm Tandem         DWR_TEST_001
270         5 Piece Drawer  17 1/2x4x20     8       19mm Tandem         DWR_TEST_001
271         5 Piece Drawer  17 1/2x4x20     8       19mm Tandem         DWR_TEST_001

What I need to do is have a 'x of' column. Basically, if you look at the first row, the Qty is 4, I need it to have a column that says 1, the second row then needs to have a value of 2, so that the data looks like this:

Drawer ID   Drawer Name     Size            Index   Qty     MaterialSchedule    PurchaseOrder   
253         5 Piece Drawer  15 1/2x3x20     1       4       19mm Tandem         DWR_TEST_001
254         5 Piece Drawer  15 1/2x3x20     2       4       19mm Tandem         DWR_TEST_001
255         5 Piece Drawer  15 1/2x3x20     3       4       19mm Tandem         DWR_TEST_001
256         5 Piece Drawer  15 1/2x3x20     4       4       19mm Tandem         DWR_TEST_001
257         5 Piece Drawer  17 1/2x3x20     1       4       19mm Tandem         DWR_TEST_001
261         5 Piece Drawer  17 1/2x3x20     2       4       19mm Tandem         DWR_TEST_001
265         5 Piece Drawer  17 1/2x3x20     3       4       19mm Tandem         DWR_TEST_001
269         5 Piece Drawer  17 1/2x3x20     4       4       19mm Tandem         DWR_TEST_001
260         5 Piece Drawer  17 1/2x6x20     1       4       19mm Tandem         DWR_TEST_001
264         5 Piece Drawer  17 1/2x6x20     2       4       19mm Tandem         DWR_TEST_001
268         5 Piece Drawer  17 1/2x6x20     3       4       19mm Tandem         DWR_TEST_001
272         5 Piece Drawer  17 1/2x6x20     4       4       19mm Tandem         DWR_TEST_001
248         5 Piece Drawer  26 1/2x3x20     1       5       19mm Tandem         DWR_TEST_001
249         5 Piece Drawer  26 1/2x3x20     2       5       19mm Tandem         DWR_TEST_001
250         5 Piece Drawer  26 1/2x3x20     3       5       19mm Tandem         DWR_TEST_001
251         5 Piece Drawer  26 1/2x3x20     4       5       19mm Tandem         DWR_TEST_001
252         5 Piece Drawer  26 1/2x3x20     5       5       19mm Tandem         DWR_TEST_001
258         5 Piece Drawer  17 1/2x4x20     1       8       19mm Tandem         DWR_TEST_001
259         5 Piece Drawer  17 1/2x4x20     2       8       19mm Tandem         DWR_TEST_001
262         5 Piece Drawer  17 1/2x4x20     3       8       19mm Tandem         DWR_TEST_001
263         5 Piece Drawer  17 1/2x4x20     4       8       19mm Tandem         DWR_TEST_001
266         5 Piece Drawer  17 1/2x4x20     5       8       19mm Tandem         DWR_TEST_001
267         5 Piece Drawer  17 1/2x4x20     6       8       19mm Tandem         DWR_TEST_001
270         5 Piece Drawer  17 1/2x4x20     7       8       19mm Tandem         DWR_TEST_001
271         5 Piece Drawer  17 1/2x4x20     8       8       19mm Tandem         DWR_TEST_001

I have googled for a while, but I am not sure that I can do this. The SQL that creates the table in the first place is this:

Select Drawers.[Drawer ID], Drawers.[Drawer Name], Trim(Drawers.[Width String]) + 'x' + Trim(Drawers.[Height String]) + 'x' + Trim(Drawers.[Depth String]) As [Size], DrawerCounts.Qty, Drawers.MaterialSchedule, [Job Info].PurchaseOrder
From (((Drawers Inner Join
DrawerCounts On Drawers.[Drawer Name] = DrawerCounts.[Drawer Name] And Drawers.Width = DrawerCounts.Width And Drawers.Height = DrawerCounts.Height And Drawers.Depth = DrawerCounts.Depth) Inner Join
Cabinets On Drawers.[Cabinet ID] = Cabinets.[Cabinet ID]) Inner Join
Rooms On Cabinets.[Room ID] = Rooms.RoomNumber) Inner Join
[Job Info] On Rooms.[Job ID] = [Job Info].ID
Group By Drawers.[Drawer ID], Drawers.[Drawer Name], Trim(Drawers.[Width String]) + 'x' + Trim(Drawers.[Height String]) + 'x' + Trim(Drawers.[Depth String]), DrawerCounts.Qty, Drawers.MaterialSchedule, [Job Info].PurchaseOrder
Order By DrawerCounts.Qty,
Drawers.[Drawer Name],
Trim(Drawers.[Width String]) + 'x' + Trim(Drawers.[Height String]) + 'x' + Trim(Drawers.[Depth String]),
Drawers.MaterialSchedule

This is in Microsoft Access and I can't change any of the tables, so I have to attempt this entirely in SQL.

Andrew Riebe
  • 411
  • 7
  • 17
  • I would suggest something like this: http://stackoverflow.com/questions/14683226/how-to-show-row-number-in-access-query-like-row-number-in-sql – sagi Feb 17 '16 at 15:44
  • You can also use the running sum over group option with controlsource "=1" in a report. Otherwise @sagi's link works well. – JJ32 Feb 17 '16 at 15:52

0 Answers0