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!

 

[Joe4]

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]

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]

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:

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]

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.

 

[Joe4]

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):

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]

wow that works.... thank you

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

 

[Joe4]

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]

Thanks!

 

[Raysoc]

Why doesnt this work?

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;