Having trouble combining records

[Nestor]

I have another question regarding something similar. I have the following field names.

ItemNumber Period1 Period2 Period3 Period4

1 CBM001-1B05 25 40 12 120

2 CBM001-2B05 20 10 40 12

3 CBM001-1T05 56 14 46 145

4 CBM001-2T05 23 15 78 34

5 MBM010-1B05 12 67 356 366

6 MBM010-2B05 14 266 256 134

I have about 3000 records similar like this one. Of course with different item numbers. What Im trying to do is the following: i want to add the qty for matching item numbers. The thing is that some item numbers have the same first 6 numbers, what difference them from all others is the last 4 #'s. For example i would want record 1 and 2 to be add together and turn them into one record. the same thing with record 3, and 4, and 5 and 6. Qty's for cbm001-1b05 correspond with cbm001-2b05. the key is the 1b05, or 1t05. if item numbers have the same first 6 numbers the same and have the following numbers like 1b05 and 2b05 i would like to group them together and make one record with qty's for each period add it up. So ANY IDEAS HOW THIS CAN BE DONE??? thank you very much

[sbg2]

I have the distinct feeling that you are not providing enough information but from the little information you have provided...

MyNewCalcField = Left(ItemNumber,7) & Right(ItemNumber,3)

the above calc would result in:

#1 & 2 in your list = CBM001-B05

#3 & 4 in your list = CBM001-T05 etc. etc.

The idea being that once you create a calculation field that produces the same result for your different, yet similar item numbers, you can create a report to total your quantities. The problem may be that you havent provided enough info for us to determine what makes an item# similar. Is there an Item CBM001-1B04 which you do not want grouped with 1B05's? Are there item #'s that might be in a different configuration such as 9 characters to the left of the dash and 3 to the right? etc.

Does that help any?