[Urgent] how to net a value with a list of value?

Dear all,

I am stuck over with the question below :sick:, please kindly help.. thank you in advance.:wave:


I have the following table with sample data as below:

Date, Fund, Unit, Clear?
1-Mar, A, 3.1, N
2-Mar, A, 2.2, N
3-Mar, A, 4.7, N

Each day, user will input the "Unit of Fund A" to this table.

Before inserting, this inputted unit has to be netted the unit that already in the table.

Say, for example:

In 4-Mar, user input -7 as the unit

This -7 unit would be netted the unit in 1-Mar (i.e. 3.1 unit)
then, -3.9 unit would be remained.

This -3.9 unit would be further netted the unit in 2-Mar (i.e. 2.2 unit)
then, -1.7 unit would be remained.

This -1.7 unit would be further netted the unit in 3-Mar (i.e. 4.7 unit)
then, +3 unit will be remained on 4-Mar

This +3 unit will be inserted into the above table and since all the records that before 4-Mar have been netted off,
they are "Cleared".

Finally, the table would look like this:

Date, Fund, Unit, Clear?
1-Mar, A, 3.1, Y
2-Mar, A, 2.2, Y
3-Mar, A, 4.7, Y
4-Mar, A, 3.0, N


I supposed I have to upload all the "unclear" record from the table into an array in the first state. Then, I have to compare the inputted value with the "unit" columns of the array.

But how? any functions in VB can help me to perform this comparsion?

Thank you.

(P.S. the above values are just examples, in realistic, the unit in the table and the inputted value can be both -ve and +ve)

In short you are adding the current value to the sum of all the units till date. for example

-7+(3.1+2.2+4.7)
=3

now where is this table and what kind of table is it?

Quote:

---

Originally Posted by koolsid

In short you are adding the current value to the sum of all the units till date. for example

-7+(3.1+2.2+4.7)
=3

now where is this table and what kind of table is it?

---

Hi ..koolsid :wave:
this table is from an MS Access database.
The table will be loaded into an array. (this part has been coded)

Yes, your short is correct
But, you know, it is just an example. It can be:

8+(-3.1)+(-3.9)
= 1

You can use SQL query to get the sum from the table

SELECT SUM(expression) FROM TABLENAME

Where expression is the Unit Field (Which I hope you have set it as numeric) and TABLENAME is the name of the table.

When you get that simply sum it up as I mentioned above and then use SQL Insert to add the new record...

Quote:

---

Originally Posted by koolsid

You can use SQL query to get the sum from the table

SELECT SUM(expression) FROM TABLENAME

Where expression is the Unit Field (Which I hope you have set it as numeric) and TABLENAME is the name of the table.

When you get that simply sum it up as I mentioned above and then use SQL Insert to add the new record...

---

this is possible if I dont have to do the record keeping.

In realistic, I have to mark which units (or more precisely, which date's unit) have been used to net-off the user's inputted unit. Therefore, it has a "clear" field in the table.

Besides, in case the query has returned a positive value, and user inputs a positive value, what user has inputted should be directly inserted into the table.

Therefore, I prefer to do it in VB rather than in SQL query. Thanks..

(My latest comment: Koolsid, it seems your method is work :>, let me further clarify )

Koolsid, in case I use your suggestion, how to overcome this issue:

1. If the sum(units) = 10 units on or before 1-Mar
2. User inputs 10 units on 2-Mar

Then, I want to insert a record of 10 units with Date 2-Mar into the table rather than insert a (10+10) units = 20 units in the table.

A simple If-Endif loop can take care of that. Check if the inputted value is > than the sum(units).