I want to learn

[Justinidea]

I have a Database that looks like this:

Before

[A].NAME,	[A].GROUP_CODE,	[A].ENTITLED_AMT,	A].PTY_TRN_UID,	[A].SumOfEntAmt
Jim	NAN	-26.20	J301	-218.5
Jim	NAN	26.20	J302	218.5
Jim	NAN	-75.20	J301	-218.5
Jim	NAN	75.20	J302	218.5
Jim	NAN	-80.10	J301	-218.5
Jim	NAN	80.10	J302	218.5
Jim	NAN	-37.00	J301	-218.5
Jim	NAN	37.00	J302	218.5

<tbody>
</tbody>

I have three queries to delete the rows with negative values so that the table looks like this

After

[A].NAME,	[A].GROUP_CODE,	[A].ENTITLED_AMT,	[A].PTY_TRN_UID,	[A].SumOfEntAmt
Jim	NAN	26.20	J302	218.5
Jim	NAN	75.20	J302	218.5
Jim	NAN	80.10	J302	218.5
Jim	NAN	37.00	J302	218.5

<tbody>
</tbody>

I'm wondering if there is a way to do this with only one query.

Here are the three I use, is there anyway to merge these three in to a single query?

[U]Query 1[/U]
SELECT [A].PTY_TRN_UID, 
Sum([A].ENTITLED_AMT) AS SumOfENTAMT INTO [B]
FROM [A]
GROUP BY [A].PTY_TRN_UID
 
[U]Query 2[/U]
UPDATE [A] INNER JOIN [B] 
ON [A].PTY_TRN_UID = [B].PTY_TRN_UID 
SET [A].SumOfEntAmt = [B]![SumOfEntAMT]
 
[U]Query 3[/U]
DELETE DISTINCTROW 
[A].NAME, 
[A].GROUP_CODE, 
[A].ENTITLED_AMT, 
[A].PTY_TRN_UID, 
[A].SumOfEntAmt
FROM [A]
WHERE [A].SumOfEntAmt)<0;

 

[SydneyGeek]

Unless I'm being misled by the data you posted you could try:

SELECT * FROM [A]
WHERE ENTITLED_AMT<0;

If that shows all of the unwanted records, change the SELECT to DELETE.

Denis

 

[Justinidea]

I'm sorry, I misled you with the data. In my attempt to make the data non-proprietary, I got it slightly wrong. There are lines that have zero values and some small positive values that also need to be grouped and deleted by the PTY_TRN_UID that are net negative. So, I had to sum the lines by PTY_TRN_UID and put the net total in the last field to identify the net negative PTY_TRN_UID transactions.

The following is a better representation of the data... sorry.

[TABLE="width: 432"]
<tbody>[TR]
[TD][A].NAME,[/TD]
[TD][A].GROUP_CODE,[/TD]
[TD][A].ENTITLED_AMT,[/TD]
[TD]A].PTY_TRN_UID,[/TD]
[TD][A].SumOfEntAmt[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]NAN[/TD]
[TD="align: right"]0[/TD]
[TD]J301[/TD]
[TD]-124.31[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]NAN[/TD]
[TD="align: right"]-7.21[/TD]
[TD]J301[/TD]
[TD]-124.31[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]NAN[/TD]
[TD="align: right"]-80.1[/TD]
[TD]J301[/TD]
[TD]-124.31[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]NAN[/TD]
[TD="align: right"]-37[/TD]
[TD]J301[/TD]
[TD]-124.31[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]NAN[/TD]
[TD="align: right"]0[/TD]
[TD]J302[/TD]
[TD]124.31[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]NAN[/TD]
[TD="align: right"]7.21[/TD]
[TD]J302[/TD]
[TD]124.31[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]NAN[/TD]
[TD="align: right"]80.1[/TD]
[TD]J302[/TD]
[TD]124.31[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]NAN[/TD]
[TD="align: right"]37[/TD]
[TD]J302[/TD]
[TD]124.31[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

 

[xenou]

Your table already has a SumOfEntAmt. How did that get there?

Without deleting anything, this gets the records you want:

SELECT DISTINCT 
 NAME, GROUP_CODE, PTY_TRN_UID , SumOfEntAmt FROM [A]
 WHERE SumOfEntAmt > 0

 

[Justinidea]

See the first two queries in my original post. The first one sums the ENTITLED_AMD and the second one updates the field SumOfEntAmt

I believe I have to delete the unwanted rows, because in a later update query, I have to use the remaining lines to replace data in another table.

I could probably use select distinct and create a new table to use in the later update table, but that would just add another table to the db.

Thanks for your reply!

 

[xenou]

I don't think you can do this in one query if you want to delete the original data from A. First you have to sum up the amounts, then delete the unwanted rows, and they can't both be done at once. BTW, I don't see it helping to have the SumOfEntAmt field in A. You can already get that sum from Entitled Amount.

This would create the table with the rows you want (I think).
Then you only need to replace table [A] with table , or rename them, or insert the data back into A, or just use B instead of A - whatever seems best to you:

SELECT 
    [NAME], SUM(ENTITLED_AMT) AS SumOfEntAmt, GROUP_CODE, PTY_TRN_UID
INTO [B]
FROM [A]
GROUP BY [NAME], GROUP_CODE, PTY_TRN_UID
HAVING SUM(ENTITLED_AMT) >= 0;

 

[Justinidea]

Thanks for your response.
I'll try that and see if it gets me where I want to be.