Grouping different values into One

C

ChuckW

Hi,

I have a table with three fields: ItemNumber,ItemName and
Quantity. My Item Numbers are either a number 1 through
13 or an alphanumeric number 1T through 13T. Item 1 and
1T are the same thing as is all items with corresponding
Ts (13 and 13T are the same thing). In the Item Name
field, all items with T are the same as those without
except they have the word "Taxable" after the item name.
For instance, Item 1 is Value Pack and 1T is Value Pack
Taxable.

What I want to do is to combine the items without the Ts
with those with the Ts and sum the quantity. So I want
to combine all 1s and 1Ts together with the combined
quantity sum. The Item name should exclude the word
taxable. The final data would have:

ItemNumber ItemName QTY
1 Value Pack 300

Can anyone help?

Thanks,

Chuck
 
J

John Spencer (MVP)

The first thing I would suggest is to change your table design so that you have
a separate field to contain the word TAXABLE and probably a separate field to
contain the the letter T. The design seems to be storing the same data twice
(assumes that T means Taxable).

If you can't change the design, then try the following

SELECT Val(ItemNumber),
IIF(ItemName Like "*Taxable",Left(ItemName,Len(ItemName)-7),ItemName),
Sum(Qty) as ItemCount
FROM YourTable
GROUP BY Val(ItemNumber),
IIF(ItemName Like "*Taxable",Left(ItemName,Len(ItemName)-7),ItemName)

Now you can see why it would be much simpler if you just had a column to store
the "Taxable" or the "T". If you did that the query would be

SELECT ItemNumber, ItemName, Sum(Qty) as ItemCount
FROM YourTable
GROUP BY ItemNumber, ItemName
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top