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
'====================================================


bhorwitz wrote:
> 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
>

Thank you I will give that a Try

"John Spencer" wrote:

> 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
> '====================================================
>
>
> bhorwitz wrote:
> > 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
> >
>