is this possible? yes/no

Raysoc

Board Regular
Joined
Feb 10, 2009
Messages
227
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!
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
I would say "Yes".

What I would probably do is an Aggregate Query, where you group on your ID numbers, then Sum the Cost and Reversal fields. Then add a calculation for Total Cost = SumofCost-SumofReversal. This should give you what you want.

If you actually want to delete them from your source table (instead of just collapsing them down in a query), take a look at using Delete Queries instead.
 

Raysoc

Board Regular
Joined
Feb 10, 2009
Messages
227
Just collapsing down would work Joe, but Im not quite sure how ur proposal would work...

90% of rows are standard, 10% might be reversals.
If its a standard row its cost field has a number and its reversal field is 0.
If its a reversal row its cost field is 0 and its reversal field has a number.

What i wana do is say in my query that IF there is another entry where the reversal field = the cost field in a dif row AND the ID and Clerk #'s in both rows match, then dont include either row in my query.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Let's say your table is named MyCostTable and has the following fields in it:

ID
Clerk_Num
Cost
Reversal

Then you could create an Aggregate Query like this. I believe the NetValue calculation is what you are looking for:

Code:
SELECT MyCostTable.ID, MyCostTable.Clerk_Num, [MyCostTable]![Cost]+[MyCostTable]![Reversal] AS TransValue, Sum(MyCostTable.Cost) AS SumOfCost, Sum(MyCostTable.Reversal) AS SumOfReversal, [SumOfCost]-[SumOfReversal] AS NetAmount
FROM MyCostTable
GROUP BY MyCostTable.ID, MyCostTable.Clerk_Num, [MyCostTable]![Cost]+[MyCostTable]![Reversal];

If you replace the table/field names that I used which what you have, then paste this code in the SQL view of a new query, you can see if this gives you what you want. If you want to see how to build, change to Design View after your paste the SQL code in.
 

Raysoc

Board Regular
Joined
Feb 10, 2009
Messages
227

ADVERTISEMENT

Hey Joe, appreciate the help thus far.

Here is your code and the table I made to test it.

SELECT MyCostTable.ID, MyCostTable.Clerk_Num, [MyCostTable]![Cost]+[MyCostTable]![Reversal] AS TransValue, Sum(MyCostTable.Cost) AS SumOfCost, Sum(MyCostTable.Reversal) AS SumOfReversal, [SumOfCost]-[SumOfReversal] AS NetAmount
FROM MyCostTable
GROUP BY MyCostTable.ID, MyCostTable.Clerk_Num, [MyCostTable]![Cost]+[MyCostTable]![Reversal];

<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>Query1</caption> <thead> <tr> <th bordercolor="#000000" bgcolor="#c0c0c0">ID</th> <th bordercolor="#000000" bgcolor="#c0c0c0">Clerk_Num</th> <th bordercolor="#000000" bgcolor="#c0c0c0">TransValue</th> <th bordercolor="#000000" bgcolor="#c0c0c0">SumOfCost</th> <th bordercolor="#000000" bgcolor="#c0c0c0">SumOfReversal</th> <th bordercolor="#000000" bgcolor="#c0c0c0">NetAmount</th> </tr> </thead> <tbody> <tr valign="top"> <td bordercolor="#c0c0c0">11</td> <td bordercolor="#c0c0c0">55</td> <td bordercolor="#c0c0c0">780</td> <td bordercolor="#c0c0c0" align="right">78</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">78</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">12</td> <td bordercolor="#c0c0c0">1</td> <td bordercolor="#c0c0c0">024</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">24</td> <td bordercolor="#c0c0c0" align="right">-24</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">12</td> <td bordercolor="#c0c0c0">1</td> <td bordercolor="#c0c0c0">240</td> <td bordercolor="#c0c0c0" align="right">24</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">24</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">12</td> <td bordercolor="#c0c0c0">67</td> <td bordercolor="#c0c0c0">050</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">50</td> <td bordercolor="#c0c0c0" align="right">-50</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">12</td> <td bordercolor="#c0c0c0">67</td> <td bordercolor="#c0c0c0">500</td> <td bordercolor="#c0c0c0" align="right">50</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">50</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">13</td> <td bordercolor="#c0c0c0">67</td> <td bordercolor="#c0c0c0">520</td> <td bordercolor="#c0c0c0" align="right">52</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">52</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">14</td> <td bordercolor="#c0c0c0">44</td> <td bordercolor="#c0c0c0">045</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">45</td> <td bordercolor="#c0c0c0" align="right">-45</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">14</td> <td bordercolor="#c0c0c0">44</td> <td bordercolor="#c0c0c0">450</td> <td bordercolor="#c0c0c0" align="right">45</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">45</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">16</td> <td bordercolor="#c0c0c0">44</td> <td bordercolor="#c0c0c0">340</td> <td bordercolor="#c0c0c0" align="right">34</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">34</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">18</td> <td bordercolor="#c0c0c0">25</td> <td bordercolor="#c0c0c0">450</td> <td bordercolor="#c0c0c0" align="right">45</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">45</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">19</td> <td bordercolor="#c0c0c0">56</td> <td bordercolor="#c0c0c0">230</td> <td bordercolor="#c0c0c0" align="right">23</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">23</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">2</td> <td bordercolor="#c0c0c0">55</td> <td bordercolor="#c0c0c0">780</td> <td bordercolor="#c0c0c0" align="right">78</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">78</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">20</td> <td bordercolor="#c0c0c0">22</td> <td bordercolor="#c0c0c0">520</td> <td bordercolor="#c0c0c0" align="right">52</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">52</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">3</td> <td bordercolor="#c0c0c0">44</td> <td bordercolor="#c0c0c0">450</td> <td bordercolor="#c0c0c0" align="right">45</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">45</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">5</td> <td bordercolor="#c0c0c0">12</td> <td bordercolor="#c0c0c0">500</td> <td bordercolor="#c0c0c0" align="right">50</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">50</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">6</td> <td bordercolor="#c0c0c0">1</td> <td bordercolor="#c0c0c0">200</td> <td bordercolor="#c0c0c0" align="right">20</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">20</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">7</td> <td bordercolor="#c0c0c0">24</td> <td bordercolor="#c0c0c0">015</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">15</td> <td bordercolor="#c0c0c0" align="right">-15</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">7</td> <td bordercolor="#c0c0c0">24</td> <td bordercolor="#c0c0c0">150</td> <td bordercolor="#c0c0c0" align="right">15</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">15</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">8</td> <td bordercolor="#c0c0c0">522</td> <td bordercolor="#c0c0c0">230</td> <td bordercolor="#c0c0c0" align="right">23</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">23</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">9</td> <td bordercolor="#c0c0c0">9474</td> <td bordercolor="#c0c0c0">900</td> <td bordercolor="#c0c0c0" align="right">90</td> <td bordercolor="#c0c0c0" align="right">0</td> <td bordercolor="#c0c0c0" align="right">90</td> </tr> </tbody> <tfoot></tfoot> </table>
Okay here is the original table:
<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>MyCostTable</caption> <thead> <tr> <th bordercolor="#000000" bgcolor="#c0c0c0">ID</th> <th bordercolor="#000000" bgcolor="#c0c0c0">Clerk_Num</th> <th bordercolor="#000000" bgcolor="#c0c0c0">Cost</th> <th bordercolor="#000000" bgcolor="#c0c0c0">Reversal</th> </tr> </thead> <tbody> <tr valign="top"> <td bordercolor="#c0c0c0">11</td> <td bordercolor="#c0c0c0">55</td> <td bordercolor="#c0c0c0">78</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">12</td> <td bordercolor="#c0c0c0">67</td> <td bordercolor="#c0c0c0">50</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">12</td> <td bordercolor="#c0c0c0">67</td> <td bordercolor="#c0c0c0">0</td> <td bordercolor="#c0c0c0">50</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">12</td> <td bordercolor="#c0c0c0">1</td> <td bordercolor="#c0c0c0">24</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">12</td> <td bordercolor="#c0c0c0">1</td> <td bordercolor="#c0c0c0">0</td> <td bordercolor="#c0c0c0">24</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">13</td> <td bordercolor="#c0c0c0">67</td> <td bordercolor="#c0c0c0">52</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">14</td> <td bordercolor="#c0c0c0">44</td> <td bordercolor="#c0c0c0">45</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">14</td> <td bordercolor="#c0c0c0">44</td> <td bordercolor="#c0c0c0">0</td> <td bordercolor="#c0c0c0">45</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">16</td> <td bordercolor="#c0c0c0">44</td> <td bordercolor="#c0c0c0">34</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">18</td> <td bordercolor="#c0c0c0">25</td> <td bordercolor="#c0c0c0">45</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">19</td> <td bordercolor="#c0c0c0">56</td> <td bordercolor="#c0c0c0">23</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">2</td> <td bordercolor="#c0c0c0">55</td> <td bordercolor="#c0c0c0">78</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">20</td> <td bordercolor="#c0c0c0">22</td> <td bordercolor="#c0c0c0">52</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">3</td> <td bordercolor="#c0c0c0">44</td> <td bordercolor="#c0c0c0">45</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">5</td> <td bordercolor="#c0c0c0">12</td> <td bordercolor="#c0c0c0">50</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">6</td> <td bordercolor="#c0c0c0">1</td> <td bordercolor="#c0c0c0">20</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">7</td> <td bordercolor="#c0c0c0">24</td> <td bordercolor="#c0c0c0">15</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">7</td> <td bordercolor="#c0c0c0">24</td> <td bordercolor="#c0c0c0">0</td> <td bordercolor="#c0c0c0">15</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">8</td> <td bordercolor="#c0c0c0">522</td> <td bordercolor="#c0c0c0">23</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">9</td> <td bordercolor="#c0c0c0">9474</td> <td bordercolor="#c0c0c0">90</td> <td bordercolor="#c0c0c0">0</td> </tr> </tbody> <tfoot></tfoot> </table>
Here is the result I want:
<table style="width: 224px; height: 302px;" bgcolor="#ffffff" border="1" cellspacing="0"><caption>RESULTS</caption> <thead> <tr> <th bordercolor="#000000" bgcolor="#c0c0c0">ID</th> <th bordercolor="#000000" bgcolor="#c0c0c0">Clerk_Num</th> <th bordercolor="#000000" bgcolor="#c0c0c0">Cost</th> <th bordercolor="#000000" bgcolor="#c0c0c0">Reversal</th> </tr> </thead> <tbody> <tr valign="top"> <td bordercolor="#c0c0c0">11</td> <td bordercolor="#c0c0c0">55</td> <td bordercolor="#c0c0c0">78</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">13</td> <td bordercolor="#c0c0c0">67</td> <td bordercolor="#c0c0c0">52</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">16</td> <td bordercolor="#c0c0c0">44</td> <td bordercolor="#c0c0c0">34</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">18</td> <td bordercolor="#c0c0c0">25</td> <td bordercolor="#c0c0c0">45</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">19</td> <td bordercolor="#c0c0c0">56</td> <td bordercolor="#c0c0c0">23</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">2</td> <td bordercolor="#c0c0c0">55</td> <td bordercolor="#c0c0c0">78</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">20</td> <td bordercolor="#c0c0c0">22</td> <td bordercolor="#c0c0c0">52</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">3</td> <td bordercolor="#c0c0c0">44</td> <td bordercolor="#c0c0c0">45</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">5</td> <td bordercolor="#c0c0c0">12</td> <td bordercolor="#c0c0c0">50</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">6</td> <td bordercolor="#c0c0c0">1</td> <td bordercolor="#c0c0c0">20</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">8</td> <td bordercolor="#c0c0c0">522</td> <td bordercolor="#c0c0c0">23</td> <td bordercolor="#c0c0c0">0</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0">9</td> <td bordercolor="#c0c0c0">9474</td> <td bordercolor="#c0c0c0">90</td> <td bordercolor="#c0c0c0">0</td> </tr> </tbody> <tfoot></tfoot> </table>
As you can see ANY entry that has another entry with a reversal number matching, then both rows are to disapear ;) In the end I do not care about the initial entry if it later was reversed.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
It looks like the big problem is that maybe your Cost and/or Reversal column is formatted as Text instead of Number, so it is messing up the TransValue Grouping. Fix those fields so that they are formatted as numbers instead of text, and things will begin to work properly and look a lot better.

We can also nest and place a criteria to drop all NetValue=0. Give this code a shot (after your adjust the code):

Code:
SELECT xyz.ID, xyz.Clerk_Num, xyz.SumOfCost AS Cost, xyz.SumOfReversal AS Reversal
FROM 
(SELECT MyCostTable.ID, MyCostTable.Clerk_Num, [MyCostTable]![Cost]+[MyCostTable]![Reversal] AS TransValue, Sum(MyCostTable.Cost) AS SumOfCost, Sum(MyCostTable.Reversal) AS SumOfReversal, [SumOfCost]-[SumOfReversal] AS NetAmount
FROM MyCostTable
GROUP BY MyCostTable.ID, MyCostTable.Clerk_Num, [MyCostTable]![Cost]+[MyCostTable]![Reversal]) AS xyz
WHERE xyz.NetAmount<>0;
 

Raysoc

Board Regular
Joined
Feb 10, 2009
Messages
227

ADVERTISEMENT

wow that works.... thank you

Okay can you explain it for me? Its 3:30 on a friday and im exhausted lol
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
I don't have time to go into a lot of detail (maybe later tonight), but take a look at Access' built-in help on Aggregate Queries (and maybe search the web too for some info on this). If you do the first batch of code I provided, and switch to Design View, you can see what this looks like and try to pick apart the Aggregate Query.

For the second batch of code, I just nested the first batch, pulling only the fields we want when the NetValue is something other than zero.

If after checking these things out, you would like a fuller description, let me know and I will see if I can get it tonight or later this weekend.
 

Raysoc

Board Regular
Joined
Feb 10, 2009
Messages
227
Why doesnt this work?
Code:
SELECT OBM1_7.Date AS [Date of Trans], [Agent List].AMDOCS AS [Agent USER ID], [EMPLOYEELASTNAME] & ' ' & [EMPLOYEEFIRSTNAME] AS [Agent Name],
    OBM1_7.[Agent LOB],
    [Agent List].IDnum AS [Agent AgentIDnum],
    OBM1_7.[UID] AS BAN,
    OBM1_7.[a1] AS Legacy,
    Abs([OBM1_7].[Trans Amt])+[OBM1_7].[Rvrsl Amt] As TransValue,
    Sum(Abs(OBM1_7.[Trans Amt])) As SumOfTrans,
    Sum(OBM1_7.[Rvrsl Amt]) As SumofRvrsl,
    [SumofTrans]-[SumofRvrsl] As NetAmt,
    OBM1_7.[Reason CODE] AS [Trans Code Used],
    [Agent List].LOCATION AS Sites
FROM OBM1_7 LEFT OUTER JOIN [Agent List] ON OBM1_7.[Agent AgentIDnum] = [Agent List].IDnum
GROUP BY OBM1_7.Date, [Agent List].AMDOCS, [EMPLOYEELASTNAME] & ' ' & [EMPLOYEEFIRSTNAME], OBM1_7.[Agent LOB], [Agent List].IDnum, OBM1_7.[UID], OBM1_7.[a1], OBM1_7.[Reason CODE], [Agent List].LOCATION, Abs([OBM1_7].[Trans Amt])+[OBM1_7].[Rvrsl Amt]
HAVING (((Abs([Trans Amt]))>500 And (Abs([Trans Amt]))<=9000)
ORDER BY [Agent List].LOCATION;
 

Watch MrExcel Video

Forum statistics

Threads
1,123,320
Messages
5,600,938
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top