Massive SQL query help

[elliotwinner]

So, I've got a bit of a conundrum that I cannot for the life of me figure out.

Here's what I need to do :

In column PO_I, look for column ITEM with lowest # in column Seq_I.
If column Ship_Q < Qty_Ordered, look for Ship_Qty within ITEM with next largest Seq_I, and sum the Ship_Qtys. Continue this until all Ship_Qs of the ITEM in the same PO_I have been summed.

Is this possible to do in SQL?

[Ramy Mahrous]

I don't understand anything!!! put your table structure + sample data and the output desired.

[elliotwinner]

The table is layed out like this:

PO_I ITEM Ship_Q Seq_I Qty Ordered
1234 1112 2 1 6
1234 1112 2 2 6
1234 1112 2 3 6
1234 2224 3 1 6
1234 2224 3 2 6

And I'd like it to sum all instances Ship_Q for each specific item, so that it it equals the quantity ordered, like this :

PO_I ITEM Ship_Q Qty Ordered
1234 1112 6 6
1234 2224 6 6

Does that make more sense?

Thanks!

Edit : And each of the columns of numbers should line up with the column names, but I don't know how to do a non-breaking space in anything but html...sorry!

[Ramy Mahrous]

Here you're

SELECT     Ship_Q, SUM(Seq_I) AS Expr2, [PO_I ITEM], Ordered
FROM         tableTest -- table name
GROUP BY Ship_Q, [PO_I ITEM], Ordered

[elliotwinner]

Thanks, I've almost got it.

Here's what I have now :

SELECT [Crossref table].PO_I, [Crossref table].MZRT_ITEM_I, Sum([Crossref table].[ITEM_SHIP_Q]) AS [Shipped Qty], [BA Access Main].[Qty Ordered],
WHERE [Crossref table].[Item_Ship_Q]<[BA Access Main].[Qty Ordered],
FROM [Crossref table] INNER JOIN [BA Access Main] ON [Crossref table].PO_I = [BA Access Main].[PO#]
GROUP BY [Crossref table].PO_I,  [Crossref table].MZRT_ITEM_I, [Crossref table].ITEM_SHIP_Q, [BA Access Main].[Qty Ordered];

But I can't seem to get the WHERE clause to work. To clarify, I only want the [ITEM_SHIP_Q] to be summed where [ITEM_SHIP_Q] is less than [Qty Ordered]. If it's not less than [Qty Ordered], I want it to stay as is.

[Ramy Mahrous]

So, you need to read in HAVING