SQL question - finding same number either + or - (1 Viewer)

voidcranium · Jul 14, 2010

Here is the final query that works.

Code:

SELECT Ap.VenNum AS VendorNumber, Ap.GrossAmt, ApVenTest.VenName, Abs([Ap].[GrossAmt])
FROM ApTest AS Ap INNER JOIN ApVenTest ON Ap.VenNum = ApVenTest.VenNum
WHERE (((Abs([Ap].[GrossAmt])) In (SELECT ABS(Ap.GrossAmt) AS ABSOLUTEGross
           FROM ApTest Ap
         GROUP 
             BY ABS(Ap.GrossAmt) 
         HAVING COUNT(*) > 1 )))
ORDER BY Ap.VenNum, Abs([Ap].[GrossAmt]);

THE OUTPUT

Code:

VendorNumber	GrossAmt	VenName	Expr1003
47801	$174,240.00	SUMRAM     	174240
47801	$174,240.00	SUMRAM     	174240
47801	$178,200.00	SUMRAM     	174240
47801	$178,200.00	SUMRAM     	174240
47801	($182,880.00)	SUMRAM     	174240
47801	$182,880.00	SUMRAM     	174240
47801	$187,920.00	SUMRAM     	174240
47801	$187,920.00	SUMRAM     	174240
47801	$189,750.00	SUMRAM     	174240
47801	($189,750.00)	SUMRAM     	174240
47801	$189,750.00	SUMRAM     	174240
47862	$144,867.00	OCEAN  SALES	144867
47862	$144,867.00	OCEAN  SALES	144867
47862	($144,867.00)	OCEAN  SALES	144867
47862	$144,867.00	OCEAN  SALES	144867
47862	$145,800.00	OCEAN  SALES	144800
47862	$145,800.00	OCEAN  SALES	144800
47862	$170,100.00	OCEAN  SALES	170100
47862	($170,100.00)	OCEAN  SALES	170100
47862	$171,700.00	OCEAN  SALES	171700
47862	$171,700.00	OCEAN  SALES	171700
47862	($171,700.00)	OCEAN  SALES	171700
47862	$171,700.00	OCEAN  SALES	171700
47862	$171,700.00	OCEAN  SALES	171700
47862	$171,700.00	OCEAN  SALES	171700

I have never done anything this complex before so this was not easy for me.
I actually found another forum that helped me figure out what I was doing wrong.

Sometimes its better to throw a life vest instead of laughing about the boat they are sinking in.

SQL question - finding same number either + or - (1 Viewer)

voidcranium

Registered Something.

Similar threads

Users who are viewing this thread