Hopefully someone can help me.

I have MSAccess db that collects orders of products, in a table called Orders. when someone orders a product, each order is a record even if you order one item. What I need to do is to add the Quantity of all the records that have the same prodID.

Example:

prodID 1 prod_code AAAA QTY 5
prodID 2 prod_code AAAA QTY 3
prodID 3 prod_code AAAA QTY 2
prodID 4 prod_code BBBB QTY 5

add the quantities of all the records that have prod_code AAA
The result should be total 10

How would I do that using SQL. If so what would a query be?
I tried using:

SELECT SUM (intQuantity) AS TotalItems
FROM tblOrders
GROUP BY prodID

no luck.. However, if you guys think that there is something else I can do... by all means, let me know. I'm using Classic ASP and AccessDB and DWMX.

Cheers,

Javier

There you go dude

<pre id=code><font face=courier size=2 id=code>
select prod_code, sum(QTY) as TotalItems from tablename group by prod_code having count(prod_code) &gt; 1
</font id=code></pre id=code>
Let me know if it worked??

Thanks Roddy, I very much appreciate it.

with the info I provided, yes it worked. But I think my problem is a little bit more complicated by my own doing, because the way I created this DB, for example the prodID field is not a numeric field, but a text field. In any case, live and learn and I for sure am learning... the hard way. The funny part is that I'm enjoying it.

Your Query
---------------------------------------------------------------------------------
SELECT prodID, SUM(intQuantity) as TotalItems
FROM tblOrders GROUP BY prodID HAVING COUNT(prodID) &gt; 1

in your code(I made few changes to it) is there a way that I could say something like:
-----------------------------------------------------------------------------------
SELECT prodID, SUM(intQuantity) AS TotalItems
FROM tblOrders GROUP BY prodID HAVING COUNT(prodID) &gt; 1
WHERE prodID = "CWildcard"

the reason being that every item is distinguished by an alpha letter from A to Z. If I can somehow distinguish by letter, and do the count, than I would be saved and later, with moretime I could modify the db and make proper changes to it.

Thanks a million.

SELECT prodID, SUM(intQuantity) AS TotalItems
FROM tblOrders WHERE prodID = "C%" GROUP BY prodID HAVING COUNT(prodID) &gt; 1
%= wildcard

If you're selecting by doing a where clause den no need to group or use the having clause.

select sum(intQuantity) as TotalItems from tblOrders where prodID like 'C%'

It will count all the c's

Thanks Roddy,

It worked beautifully. I get all worked up with SQL. I know there is a long road ahead but for sure you made it easier today.

Cheers to you,

Javier