Combining similar items into one amongst many items

[SWTpaul]

I have a few products amongst about 1,000 that have extensions to the item #, such as DF and SP. Is there a way to find those items that have multiple item #'s and combine their data?

Such as...

MICROWAVE 4400924 $1,920.25
MICROWAVE 4400924DF $1,195.13
MICROWAVE 4400924SP $1,241.29

would combine into...

MICROWAVE 4400924 $4,356.67

Item #'s range from 3 digits up to 9.

Any help would be appreciated! Thanks!

 

[PHV]

What about something like this ?
SELECT ProductName, Val([Item #]) As ItemNum, SUM(Price) As TotalPrice
FROM yourTable
GROUP BY ProductName, Val([Item #])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244