based on date sum up of data

[koolninja]

I have created a table name Sale having Six columns

**SlNo Product    Type          Date     Quantity Stock_balance`**

1        A         IN           1/2/16      40           40

2        A         OUT          2/2/16      20           20

3        A         OUT          2/2/16      10           10

4        B         IN           1/2/16      30           30

5        B         OUT          2/2/16      20           10

6        A         OUT          5/2/16      05           05

These type of data entered upto Quantity but Stock_balance need to updated with update sql queries. Product A and B each summing for ‘IN’ or subtraction for ‘Out’ related to Previous date entries for particular product. I want to overcome missed entries which later insert with correct date then all data related to product Stock_balance corrected. I am not getting idea to update stock_balance with use of date… or other references to performs this task

thanks in advance for help

[koolninja]

Suppose slno 2 entry missed on that particular date. So stock_balance get wrong for slno 3(30) and 6(25) get worng but missed entry when updated then product wise and date wise stock_balance get corrected for all entries respect to that product

[Ratslinger]

A basic principle of data bases is if you can calculate the information from other information don’t store it.

Placing balances (as in this past post) in detail records is not a good idea. Placing running balances by date within each product is even worse. Each time you add a new detail record, you need to reprocess each and every detail record to re-generate the running total because the system doesn’t know where the date falls in. This also means reading and writing each record each time you add a new record.

Now other problems are possible. If you have an “IN” record which was forgotten and entered past the date, it will be later in the table than the other records. Not a big problem in generating the total but if looking at table entries for a product it is difficult at best to see exactly where or how the totals are calculated. Do this 20-30 times and it’s a real headache!

Can your request be done? Based upon your HSQL version it would probably be best done with a macro and run whenever a new record is added. Execute a query sorted by Type within Date within Product. Then reading the query accumulate totals and write out the balance to each record accordingly. When the product changes, zero the accumulator and start with the new product.

Not knowing your intention for this, as appears on the surface, the best solution to get a total as of a specific date for a specific product is through a query using a parameter(s) (Product,Date). This can be a straight Query or using a form.

[koolninja]

I need to view balance per row quantity entered product wise with respect to IN/OUT(+/-)

[Ratslinger]

Yes, it is understood what your request is. It doesn’t make sense as to why in one day if there are 18 OUT entries, the balance needs to be seen for each OUT entry especially because they may not be entered exactly as they went out or, as stated, one was missed and entered later. Reasons have been given why not to do this and also how to do it. If you really must, code a macro as mentioned. Can’t tell you specifics because system details are needed. Outline was already provided above.