is this possible? yes/no

[Raysoc]

Just need a simple yes/no.

I have lots and lots of data.
What I wana do is remove any entry if it was later reversed.

i.e. a customer purchases something it costs $20.0
so row 1
Cost Reversal
20 0

a customer then returns said item
row 87
Cost Reversal
0 20

so if the rows custID# num clerk# match in both entries i dont want them coming back in my query of all costs.

is this possible or is this a task for excel for pre or post data mining.

Thanks!

 

[Raysoc]

For some reason I can not seem to integrate this into my SQL.. it refuses to pick up the Reversal amt...

here is the SQL im using, ive made it as basic as I can to figure it out and plan on adding in the other fields etc later, right now I just want the basics working.

Any help is greatly appreciated.

SELECT MyTable.Date, [EMP List].Iden, [EMPLOYEELASTNAME] & ' ' & [EMPLOYEEFIRSTNAME] AS [EMP Name], MyTable.[EMP LOB], [EMP List].BP, MyTable.[OBB], MyTable.[CID], MyTable![Adj Amt]+MyTable![Rvrsl Amt] AS TransValue, Sum(MyTable.[Adj Amt]) AS SumOfAdj, Sum(MyTable.[Rvrsl Amt]) AS SumofRvrsl, [SumofAdj]-[SumofRvrsl] AS NetAmt
FROM MyTable LEFT JOIN [EMP List] ON MyTable.[EMP P] = [EMP List].BP
GROUP BY MyTable.Date, [EMP List].Iden, [EMPLOYEELASTNAME] & ' ' & [EMPLOYEEFIRSTNAME], MyTable.[EMP LOB], [EMP List].BP, MyTable.[OBB], MyTable.[CID], MyTable![Adj Amt]+MyTable![Rvrsl Amt], MyTable.[Adj Amt]
HAVING (((MyTable.[Adj Amt])<-500));

 

[Joe4]

I don't have a lot of time to dive into it right now, but I did notice one thing. You have MyTable.[Adj Amt] in your Group By clause, but not in your SELECT statement (other than in your calculations). I am guessing it is there because you want to use in in your criteria.

Instead of putting the criteria in your HAVING clause, you can put it in the WHERE clause, and then you do not need it in your GROUP BY.

Give that a shot and see if it makes any difference. The code will look something like this:

SELECT MyTable.Date, [EMP List].Iden, [EMPLOYEELASTNAME] & ' ' & [EMPLOYEEFIRSTNAME] AS [EMP Name], MyTable.[EMP LOB], [EMP List].BP, MyTable.[OBB], MyTable.[CID], MyTable![Adj Amt]+MyTable![Rvrsl Amt] AS TransValue, Sum(MyTable.[Adj Amt]) AS SumOfAdj, Sum(MyTable.[Rvrsl Amt]) AS SumofRvrsl, [SumofAdj]-[SumofRvrsl] AS NetAmt
FROM MyTable LEFT JOIN [EMP List] ON MyTable.[EMP P] = [EMP List].BP
WHERE MyTable.[Adj Amt])<-500
GROUP BY MyTable.Date, [EMP List].Iden, [EMPLOYEELASTNAME] & ' ' & [EMPLOYEEFIRSTNAME], MyTable.[EMP LOB], [EMP List].BP, MyTable.[OBB], MyTable.[CID], MyTable![Adj Amt]+MyTable![Rvrsl Amt];