Calculation and Conditional Deletion of Lines

[ease20022002]

Hello, I want to delete the correct records in Access based on a date by performing a calculation.

This is an example of the data:

MCP QUANTITY DOC DATE
4036311/665940/3100 1 5/23/2005
4036311/665940/3100 1 5/18/2005
4036311/665940/3100 -1 5/23/2005
4036311/665940/3100 -1 4/18/2005
4036311/665940/3100 1 5/30/2005
4036311/665940/3100 1 4/15/2005

A little background:

The field named MCP is a product identification code, and the field named QUANTITY is obviously the quantity of the product. I deal with Demo equipment and inventory so the quantity number can be negative (product actually being demoed by the customer and out of the inventory) and positive (product returned by the customer and re-entered into the inventory). The DOC DATE dates the inventory for depreciation purposes

What I want to do is delete records that net to 0 using the QUANTITY field as the criteria. Because there are two negative 1s and two positive 1s, which equal 0, I want to delete the lines that equal 0 and keep the remaining positive quantities that have the most "Recent" Doc Date, i.e., the two remaining records with 5/23/2005 and 5/30/2005. So basically I think I have to write code that will delete the oldest lines of data that = 0 based on the DATE DOC field. Also, if the sum of the quantity of all the records for one MCP group code = 0, then I will delete them all (The entire group). Also, there will never be a negative net sum of a MCP group.

I need to do this for every "IDENTICAL" MCP code group, so there will be several other MCP groups with identical codes that have a similar situation with positive and negative quantities. Most of the MCP code groups will have quantities of -1 and 1, but sometimes there quantities will be greater than 1, but there will ALWAYS, 100% of the time be an offset for that number, i.e., 10 and -10 in 2 or however many lines of positive lines of data there are with the corresponding offset. For example:

MCP QUANTITY DOC DATE
Y11710550001/546000/3200 -10 3/25/2005
Y11710550001/546000/3200 10 4/27/2005
Y11710550001/546000/3200 5 2/17/2005
Y11710550001/546000/3200 -5 4/19/2005

These obviously net to 0.

Any help provided will be much appreciated.

Thank You

 

[markphsd]

If you want to delete those records it sounds like you would do a loop through your records sorted by the [DOC DATE] Order By Acsending and Order By [MCP]; While looping the code you add the quantity, everytime you hit a zero amount within the MCP group you delete the records; Once you hit a new MCP you reset the quantity.

However, why do you need to delete it at all? I would think at some point you would like to get something else out of this information, perhaps how many demo's you turned into sales etc..

I think that you might want to look at normalizing a little bit. It's hard to suggest examples because there's very little information, but I have a feeling that's the way to go.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.