Help with a SQL Statement

[tsyle]

I have been thinking about how to do this query for hours but have yet to figure it out.

Here is what I have

Table
ItemCode Inv# ExtAmt

/ITM550 3221 500
/ITM550 3221 250
/ITM550 1111 100
/ITM550 1111 250
/ITM550 2222 800
/ITM100 1111 300
/ITM100 2000 400

Desired output
ItemCode Inv# ExtAmt ExtAmtTotals
/ITM550 3221 500 750
/ITM550 3221 250 750
/ITM550 1111 100 350
/ITM550 1111 250 350
/ITM550 2222 800 800
/ITM100 1111 300 300
/ITM100 2000 400 400

I am able to get the ExtAmtTotals and list the ItemCode and Inv# next to it but I can't get it to include the ExtAmt.

Here is the SQL that i have so far:

SELECT ItemCode, [inv#], SUM(ExtAmt) as ExtAmtTotals
FROM Table1
GROUP BY [inv#], ItemCode
ORDER BY ItemCode;

 

[MazeWorX]

Query 1

SELECT DISTINCT Table1.ItemCode, Table1.[Inv#], Table1.ExtAmt, Query2.ExtAmtTotals
FROM Table1 INNER JOIN Query2 ON Table1.[Inv#] = Query2.[Inv#]
ORDER BY Table1.ItemCode;

Query 2

SELECT Table1.ItemCode, Table1.[Inv#], Sum(Table1.ExtAmt) AS ExtAmtTotals
FROM Table1
GROUP BY Table1.ItemCode, Table1.[Inv#]
ORDER BY Table1.ItemCode;

results

ItemCodeInv# ExtAmt ExtAmtTotals
ITM550 1111 100 650
ITM550 1111 250 650
ITM550 1111 300 650
ITM550 2000 400 400
ITM550 2222 800 800
ITM550 3221 250 750
ITM550 3221 500 750

Thoughts?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[tsyle]

Hi MazeWorx. Thakns for the reply.

I actually had a query similar to that but it returns too many results.

My goal is to add the extamttotals to each row in the original table.

So I need the same number of rows returned but with another oclumn that shows the ExtAmtTotal. This is needed so I can export the data into an excel sheet and import it into a different application.

I'm beginning to think that this is not possible with just sql queries.

The query2 table is the view for the results of Table1. I will need to check that if table1.itemcode = query2.itemcode and table1.inv#=query2.inv# then display the extamttotals.

I'm a developer so i don't do sql queries often but the more I think about this, the more i don't think it's possible.

what do you think?

/ITM550 3221 500 750
/ITM550 3221 250 750
/ITM550 1111 100 350
/ITM550 1111 250 350
/ITM550 2222 800 800
/ITM100 1111 300 300
/ITM100 2000 400 400

 

[tsyle]

FOUND THE SOLUTION!! woohoo

SELECT Table1.ItemCode, Table1.[inv#], Query2.ExtAmtTotals
FROM Table1, Query2
WHERE Table1.ItemCode+Table1.[inv#] = Query2.ItemCode+Query2.[inv#]
ORDER BY Table1.ItemCode