Autofreak
asked on
VB and Access
Hello All,
I would lile to ask you about the following today.
Say, there's a table, table1 in myDB.mdb that liiks like following:
table1:
Security Type Units Purchase Price Sale Price Gains/Loss
AAA1 B 50 100 - -
AAA1 B 5 120 - -
AAA1 S 25 ? 120 ?
AAA1 S 30 ? 130 ?
Thes are four trading records above that say that 25+30 units, Type "S", ( 3rd and 4th lines) were sold @ $120 and $130 respectively from inventory of 50 + 5 units(1st and 2nd lines) purchased in the past @ $100 and $120 respectively. My task here is to assign Purchase Price to the 25 lot (3rd line) and 30 lot (4th line) on FIFO bases, that is, first -in , first- out, and based on the prices assigned calculate Gains/Losses.
FIFI logic would result in this :
first lot of 25 (3rd line) would get $100 price from 1st Type B line for all 25 units ,
G/L = ($120-$100)*25=$500
likewise,
2nd lot of 30 would get 25 units @ $100 and 5 @120
G/L = ($130-$100)*25 +($130-$120)*5 = $800
While the logic in this case is pretty simple, I am not sure if I can implement it more effectively through VB, or SQL ,or some combination of both. The file is usually huge and pure VB looping and record-by-record checking is very time consuming. I learned the power of SQL recently and hoped you would prbably be able to help me.
Thank you very much
Serge
I would lile to ask you about the following today.
Say, there's a table, table1 in myDB.mdb that liiks like following:
table1:
Security Type Units Purchase Price Sale Price Gains/Loss
AAA1 B 50 100 - -
AAA1 B 5 120 - -
AAA1 S 25 ? 120 ?
AAA1 S 30 ? 130 ?
Thes are four trading records above that say that 25+30 units, Type "S", ( 3rd and 4th lines) were sold @ $120 and $130 respectively from inventory of 50 + 5 units(1st and 2nd lines) purchased in the past @ $100 and $120 respectively. My task here is to assign Purchase Price to the 25 lot (3rd line) and 30 lot (4th line) on FIFO bases, that is, first -in , first- out, and based on the prices assigned calculate Gains/Losses.
FIFI logic would result in this :
first lot of 25 (3rd line) would get $100 price from 1st Type B line for all 25 units ,
G/L = ($120-$100)*25=$500
likewise,
2nd lot of 30 would get 25 units @ $100 and 5 @120
G/L = ($130-$100)*25 +($130-$120)*5 = $800
While the logic in this case is pretty simple, I am not sure if I can implement it more effectively through VB, or SQL ,or some combination of both. The file is usually huge and pure VB looping and record-by-record checking is very time consuming. I learned the power of SQL recently and hoped you would prbably be able to help me.
Thank you very much
Serge
ASKER
> 1) How do you know that type B and S are related.
If there's (an) S record(s) there is always (a) B type(s) to offset, thier totals equal
> 2) Tables do not have a record order, so FIFO can only work if for example a date is added to the table. Date when purchases/sold.
You are right, there is a data field that together with Security # uniquely id B's and S's
> 3) a G/L report is generated
Security Type Units Purchase Price Sale Price Gains/Loss
AAA1 S 25 100 120 500
AAA1 S 25 100 130 750
AAA1 S 5 120 130 50
or the consolidated form
Security Type Units Purchase Price Sale Price Gains/Loss
AAA1 S 25 100.0000 120 500
AAA1 S 30 103.3333 130 800
Thank you,
Serge
If there's (an) S record(s) there is always (a) B type(s) to offset, thier totals equal
> 2) Tables do not have a record order, so FIFO can only work if for example a date is added to the table. Date when purchases/sold.
You are right, there is a data field that together with Security # uniquely id B's and S's
> 3) a G/L report is generated
Security Type Units Purchase Price Sale Price Gains/Loss
AAA1 S 25 100 120 500
AAA1 S 25 100 130 750
AAA1 S 5 120 130 50
or the consolidated form
Security Type Units Purchase Price Sale Price Gains/Loss
AAA1 S 25 100.0000 120 500
AAA1 S 30 103.3333 130 800
Thank you,
Serge
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Paul,
this one is also part of the Q you answered so why leave it for cancelation, Thanks! Take care! Serge
https://www.experts-exchange.com/questions/21476953/SQL-query-vs-VB.html
this one is also part of the Q you answered so why leave it for cancelation, Thanks! Take care! Serge
https://www.experts-exchange.com/questions/21476953/SQL-query-vs-VB.html
1) How do you know that type B and S are related.
2) Tables do not have a record order, so FIFO can only work if for example a date is added to the table. Date when purchases/sold.
3) What needs to happen to the table after the math?
Once you have sold the previously purchased goods you need to update the stock.