code to create Box x of xx

  • Thread starter Thread starter bhorwitz
  • Start date Start date
B

bhorwitz

I need to group the data by Id and assign a box # and populate the Box field
with Box x of xx. See example below.

Id Description Box
123 1 Box 1 of 3
123 2 Box 2 of 3
123 5 Box 3 of 3
456 1 Box 1 of 2
456 2 Box 2 of 2

I am assuming that the best way to do this is via some code that loops thru,
but since I dont know VBA that well Im not sure how to do it.

any help would be appreciated, even if it is pointing me to the right place
to research it.

Thank you
Bob
 
One method would be to use something like the following query.

SELECT YA.ID
, YA.Description
, Count(YB.ID) +1 as BoxNumber
, (SELECT Count(ID) FROM YourTable as YC WHERE YC.ID = YA.ID) as TotalBoxes
FROM YourTable as Ya LEFT JOIN YourTable as YB
ON YA.ID =YB.ID
AND YA.Description < YB.Description
GROUP BY YA.ID, YA.Description


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thank you I will give that a Try

John Spencer said:
One method would be to use something like the following query.

SELECT YA.ID
, YA.Description
, Count(YB.ID) +1 as BoxNumber
, (SELECT Count(ID) FROM YourTable as YC WHERE YC.ID = YA.ID) as TotalBoxes
FROM YourTable as Ya LEFT JOIN YourTable as YB
ON YA.ID =YB.ID
AND YA.Description < YB.Description
GROUP BY YA.ID, YA.Description


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Click to expand...
 
Back
Top