Finding (and removing) records that sum to zero

[ejstefl]

Hello,

I was wondering if you guys and gals could help me out. I have a table that lists dollar amounts. I want a way to find and delete records whose dollar amounts sum to zero. As an example, lets say I have these records:

Record 1 10/1/02 $15.00
Record 2 10/1/02 -$14.00
Record 3 10/1/02 $13.00
Record 4 10/1/02 -$12.00
Record 5 10/2/02 -$15.00
Record 6 10/2/02 $14.00

I want a way to automatically delete records 1 and 5, and 2 and 6. Can anyone suggest a method I could use? Any help would be greatly appreciated.

Thanks,
Eric

 

[The_Doc_Man]

If the date and amount are the only two fields in the table, this could be a problem. However, where there is a will, there is a way. (Or, where there is a will, there are heirs...)

This is sloppy but effective. Some folks will probably tell you not to do this. But there is a simple way to do this.

First, create a new, separate table that lists dates and dollar amounts. Maybe that is the only thing in the table. (If you left something out, I might need more information to make this a better table.)

Second, create an append query that involves group-by [itemdate] and sum [amounts]. Append one record to the new table for each date. The value is just the sum of all entries for that date. Store each date in a separate record. When you are done, the new table has zero for a sum in only a few cases: 1) The table has no entries for a given date, or 2) The table has at least one entry, with zero as the value for all entries, or 3) The table has at least two entries, neither of which is individually zero, but for which the sum IS zero.

Third, now write a delete query that joins the new table to the old one in a many (old entries) to one (new entries) relationship. Delete all entries where the sum is zero. When you build this join back to the old table, the case of having NO entries for a date is not a problem because there will be no records to join in that case. In all other cases, a zero record will exist or a combination of records totalling zero will exist.

Fourth, write a delete query for all records in the new table only.

Now, write a macro that does the following:

1. Run the "new-table delete-all" query.
2. Run the append query to store date/sum records with the grouping as noted.
3. Run the "joined-query delete" query that selects sums of 0
4. (Optional) Run the "new-table delete-all" query again.

Run the macro. That should do it for you. Step 4 minimizes the size of the DB if you choose to do a compression.

 

[ejstefl]

That's a pretty inventive solution! There are acutally more fields in my table. There are about three more fields in each record, all of type Text. Would this be a problem for your solution?

 

[ejstefl]

Actually, I was looking at this. I don't see how this will work. After step 1 and 2, I will have a table with the following entries:

10/1/02 1
10/2/02 -2

And in step 3, when I go to join, there are no entries that are zero and nothing is deleted. However, Records 1 and 5, 2 and 6 should be delted. Could you please clarify step 3?

Thanks!

 

[ejstefl]

<Bump>

Anyone have any other ideas as to how I could do this?

Thanks!

 

[Jon K]

Make a backup copy of your table.

Then try this Select Query first (type/paste in the SQL View of a new query, replacing with the correct table name and field name):-

SELECT *
FROM TableName
WHERE abs(AmountField) in (Select abs(AmountField) from TableName group by abs(AmountField) having count(*)=2 and sum(AmountField)=0)
ORDER BY abs(AmountField);


Run the Select Query.

Make sure the records returned are the ones you want to delete. If they are, then convert the query to a Delete Query by changing the word SELECT to DELETE and removing the words ORDER BY abs(AmountField);

 

[ejstefl]

Jon,

That works! Thanks very much for your help - I would never have figured that out. I appreciate it.

Eric

 

[DaveO]

Perhaps someone can help with a variation of this request.

I have two tables, one of which contains 'ID' and 'Total' amongst other fields, the second contains 'ID' and 'Qty' of which there may be several records with the same 'ID'

What I need to do is generate a total 'Qty' for each 'ID' in the second table and store it in the 'Total' field in the first, and also set the 'Total' field to 0 if there are no occurances of a particular 'ID' from the first table within the second table.

ie Table 1 contains

[123456] [ ] (Total fields may or may not be blank)
[234567] [ ]
[345678] [ ]

Table 2 contains
[123456] [-1]
[345678] [6]
[123456] [2]
[123456] [-1]

and I need the resulting table 1 to be

[123456] [0] (Sum of -1, 2, -1)
[234567] [0] (234567 does not occur in table 2)
[345678] [6] (Sum of matching records in table 2)

Help would be very much appreciated

Dave

 

[Rich]

Just use a Totals query, GroupBy ID and Sum the quantity, by the way, isn't the Sum of -1,2,-1 0

 

[DaveO]

Thanks for your reply.

I have got a query which will do the summation in exactly the way you indicated. What I am struggling with is getting it to update the first table with the results of this query.

Yep you are right sum -1, 2, -1 is 0, but I thought I had put that anyway.

 

[DaveO]

Thanks for your reply.

I have got a query which will do the summation in exactly the way you indicated. What I am struggling with is getting it to update the first table with the results of this query.

Yep you are right sum -1, 2, -1 is 0, but I thought I had put that anyway.

 

[Rich]

Since you can calculate the amount at any time, why do you want to store the amount? It's generally not a good idea

 

[DaveO]

The first table is a Product database for a stock file
The second table is a list of discrepancies and reasons

I need to store the total in the first table so that I can selectively print out a report of those products which have a discrepancy or a total list of discrepancies. I know I should have thought about this before I designed the app, but I now need to add this infor from my existing tables.

Thinking about it, it may be easier to write some VB code to do the job as it should only be a one off task as I can make future entries update the total.

Thanks anyway

 

[Jon K]

First change your summation query into a make-table query to create a temporary table:-

SELECT ID, Sum(Qty) AS SumOfQty INTO tblTemp
FROM [Table 2]
GROUP BY ID;


Then update Table 1 using an outer join with the temporary table:-

UPDATE [Table 1] LEFT JOIN tblTemp ON [Table 1].ID = tblTemp.ID SET [Table 1].Total = iif(isnull(tblTemp.SumOfQty), 0, tblTemp.SumOfQty);


We can't update Table 1 by directly joining it with the summation query, as the total query will make the update query not updatable.

 

[DaveO]

Thanks Jon,

I'd sort of got some code eventually which did the job and it seems very similar to what you suggested. Here is what I ended up with:

DoCmd.SetWarnings False

DoCmd.RunSQL ("SELECT TblDiscrep.Sku, Sum(TblDiscrep.Qty) AS SumOfQty INTO temp" & _
" FROM TblDiscrep" & _
" GROUP BY TblDiscrep.Sku" & _
" ORDER BY TblDiscrep.Sku;")

DoCmd.RunSQL ("UPDATE TblProducts LEFT JOIN temp ON TblProducts.Sku = temp.Sku" & _
" SET TblProducts.TotalDiscrep = [sumofqty];")

DoCmd.SetWarnings True

There is no test for NULL values, but it seems to do the job OK.

Thanks again.

Dave

 

[dmz]

I have a similar problem like the above but in my case I have about 70 columns but would like to remove any two rows that sum to zero (based on two columns) if the column "Units" are the same for the same "Bar code" thus to say "for the same 'Units' for any two lines, check to see if their respective 'Bar Codes’ are the same and if so remove if their amounts sums to zero".
I have the following code which seems to be working but then for a large data (not even too large though, about 5k), it does not remove all the data that meets the criteria and for larger data it takes eternity to remove the partial data even:

SELECT *
FROM datatable
WHERE ((([datatable].[Unit] & [datatable].[Bar Code] & Abs([datatable].[Amount])) 
In (SELECT datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount) 
FROM datatable 
GROUP BY datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount) 
HAVING count(*)>= 2 and sum(datatable.Amount)=0)))
ORDER BY [datatable].Unit & [datatable].[Bar Code] & Abs([datatable].Amount);

DMZ

 

[gemma-the-husky]

WHERE ((([datatable].[Unit] & [datatable].[Bar Code] & Abs([datatable].[Amount]))

what is this where clause testing? there is no equals in there.

also, are you really concatenating these fields into a string. Can you not test them separately.

further, using abs is potentially wrong, as in theory you could incorrectly match a neg and positive.

 

[dmz]

What do you suggest then? Do you need some data to rework this for me? The "where" is to match the concatenated phrase...and I would like to know how to test them separate rather than as a concatenated phrase to...also I used the abs so I can compare the concatenated phrase for both the positive and the negative records.

Still need your assistance!

 

[gemma-the-husky]

compare each bit separately, rather than concatenating - although you may end up with the same result - but you have to compare with something

where part1 = something and part2 = something and part3 = something

 

[dmz]

Please see the data below:

# Unit bar Code Amount

1. AAB Mac1 2.75
2. AAB Mac1 -2.75
3. AAB Mac1 1.24
4. AAC Mac3 35.00
5. AAC Mac3 -20.00
6. AAC Mac3 20.00
7. AAD Mac3 16.11
8. AAC Mac2 11.00
9. AAC Mac2 -11.00
10 AAC Mac3 12.05
11 AAF Mac3 -12.05
12 AAD Mac3 -16.11
13 AAC Mac4 35.00

I want to select records # 1, 2, 5, 6,7,8,9 and 12 since they are same units having the same bar code and their sums total zero.