Having trouble combining records

This topic is 6973 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Share on other sites

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?

Share on other sites

This topic is 6973 days old. Please don't post here. Open a new topic instead.

Create an account

Register a new account