Replacing data in a table WITHOUT changing the numbers created from a running sum..

[StenKoll]

Help needed in order to create a register of stocks in a company. In
accordance with local laws I need to give each individual share a
number. I have accomplished this by establishing three tables (se
below) then I run a query giving me a running total, which give me the
first stock in the batch purchased by an individual, then I use this
number and add the number of shares in order to find the number of the
individuals' last share.

So far everything looks fine – BUT- this is changed when the
individuals start trading stocks. This isn't a regular event, but I
want to find a way to handle it if and when it occurs. Due to the way
I have it now, the shares before the ones traded keep their correct
numbers, but the ones after are assigned new numbers.

What I need is a way to remove the traded amount from the
Number_of_Share s in a post and recalculate the correct number for this
post without changing Start_no and End_no for the other shares in the
table…
In addition, I need to place the Start_no and End_no for the removed
shares as a new entry in the table TblNumber_of_sh ares

Any advice on how to do this would be greatly appreciated! I realize
that I might need a different way to solve how I assign individual
nubers in order to make this work, but this is how I've acomplished it
so far.

I'am fairly new to access and VB programming, so please be gentle with
me :-0
Thanks,
Sten Koll
About the database ( Pasted from an earlier post…)

Tables
Name:
TblName_ID (Primary key)
Firstname
Lastname

TblNew_Issues_S hares
Issue_ID (Primary key)
Date
Number_of_share s (for each new issue)

TblNumber_of_sh ares
Transaction_Cou nter
Name_ID
Issue_ID
Number_of_Share s
Start_no and End_no

[U N Me]

StenKoll wrote:

Help needed in order to create a register of stocks in a company. In
accordance with local laws I need to give each individual share a
number. I have accomplished this by establishing three tables (se
below) then I run a query giving me a running total, which give me the
first stock in the batch purchased by an individual, then I use this
number and add the number of shares in order to find the number of the
individuals' last share.

So far everything looks fine – BUT- this is changed when the
individuals start trading stocks. This isn't a regular event, but I
want to find a way to handle it if and when it occurs. Due to the way
I have it now, the shares before the ones traded keep their correct
numbers, but the ones after are assigned new numbers.

What I need is a way to remove the traded amount from the
Number_of_Share s in a post and recalculate the correct number for this
post without changing Start_no and End_no for the other shares in the
table…
In addition, I need to place the Start_no and End_no for the removed
shares as a new entry in the table TblNumber_of_sh ares

Any advice on how to do this would be greatly appreciated! I realize
that I might need a different way to solve how I assign individual
nubers in order to make this work, but this is how I've acomplished it
so far.

I'am fairly new to access and VB programming, so please be gentle with
me :-0

Thanks,
Sten Koll

About the database ( Pasted from an earlier post…)

Tables
Name:
TblName_ID (Primary key)
Firstname
Lastname

TblNew_Issues_S hares
Issue_ID (Primary key)
Date
Number_of_share s (for each new issue)

TblNumber_of_sh ares
Transaction_Cou nter
Name_ID
Issue_ID
Number_of_Share s
Start_no and End_no

Maybe create another table for shares traded?

[StenKoll]

Thank you for your suggestion, but how do I then keep track of the
shares in my reports? Furthermore: How do I keep track of the shares
when they ar traded the second and n'th time?

Maybe the easiest way to get around this is to manually number the
shares, but if there is a way around this I would like som help
finding it.

Thanks,

U N Me <un**@together. com> wrote in message news:<3F******* ********@togeth er.com>...

StenKoll wrote:

Help needed in order to create a register of stocks in a company. In
accordance with local laws I need to give each individual share a
number. I have accomplished this by establishing three tables (se
below) then I run a query giving me a running total, which give me the
first stock in the batch purchased by an individual, then I use this
number and add the number of shares in order to find the number of the
individuals' last share.

So far everything looks fine ? BUT- this is changed when the
individuals start trading stocks. This isn't a regular event, but I
want to find a way to handle it if and when it occurs. Due to the way
I have it now, the shares before the ones traded keep their correct
numbers, but the ones after are assigned new numbers.

What I need is a way to remove the traded amount from the
Number_of_Share s in a post and recalculate the correct number for this
post without changing Start_no and End_no for the other shares in the
table?
In addition, I need to place the Start_no and End_no for the removed
shares as a new entry in the table TblNumber_of_sh ares

Any advice on how to do this would be greatly appreciated! I realize
that I might need a different way to solve how I assign individual
nubers in order to make this work, but this is how I've acomplished it
so far.

I'am fairly new to access and VB programming, so please be gentle with
me :-0

Thanks,
Sten Koll

About the database ( Pasted from an earlier post?)

Tables
Name:
TblName_ID (Primary key)
Firstname
Lastname

TblNew_Issues_S hares
Issue_ID (Primary key)
Date
Number_of_share s (for each new issue)

TblNumber_of_sh ares
Transaction_Cou nter
Name_ID
Issue_ID
Number_of_Share s
Start_no and End_no

Maybe create another table for shares traded?

[U N Me]

StenKoll wrote:

Thank you for your suggestion, but how do I then keep track of the
shares in my reports? Furthermore: How do I keep track of the shares
when they ar traded the second and n'th time?

Maybe the easiest way to get around this is to manually number the
shares, but if there is a way around this I would like som help
finding it.

Maybe you can help me out here in understanding the problem
You have a stockholder table.
You have a new issues table
You have a stockholder and new issues table

So you add a stockholder record.
Later on you add a new issues record
Then you add new stock/issue records for those that bought the new issue.

Now, assume you have a shares traded table. This would have the
start/end/stockholder/qty in it.
The shares that have been traded will now get new numbers and get re-inserted into the
stock/issue table (if I understand correctly) but now with a new stockholder id.

Having this new table will work. If I purchase 1000 shares and sell 100, I would add a
record in the shares traded table. The 100 get added into the new issues table if I
sold them back or into the stock/issues table with the new buyer.

I could sell those 1000 shares 10 times, 100 shares at a time, thus I'd end up with 10
records in the shares traded table. I could then take my initial purchase amount and
subtract that from the shares traded table (using Dsum() or a SQL statement.

Anyway, I think another table is required....if I understand your problem correctly.

If you
Thanks,

U N Me <un**@together. com> wrote in message news:<3F******* ********@togeth er.com>...

StenKoll wrote:

Help needed in order to create a register of stocks in a company. In
accordance with local laws I need to give each individual share a
number. I have accomplished this by establishing three tables (se
below) then I run a query giving me a running total, which give me the
first stock in the batch purchased by an individual, then I use this
number and add the number of shares in order to find the number of the
individuals' last share.

So far everything looks fine ? BUT- this is changed when the
individuals start trading stocks. This isn't a regular event, but I
want to find a way to handle it if and when it occurs. Due to the way
I have it now, the shares before the ones traded keep their correct
numbers, but the ones after are assigned new numbers.

What I need is a way to remove the traded amount from the
Number_of_Share s in a post and recalculate the correct number for this
post without changing Start_no and End_no for the other shares in the
table?
In addition, I need to place the Start_no and End_no for the removed
shares as a new entry in the table TblNumber_of_sh ares

Any advice on how to do this would be greatly appreciated! I realize
that I might need a different way to solve how I assign individual
nubers in order to make this work, but this is how I've acomplished it
so far.

I'am fairly new to access and VB programming, so please be gentle with
me :-0

Thanks,
Sten Koll

About the database ( Pasted from an earlier post?)

Tables
Name:
TblName_ID (Primary key)
Firstname
Lastname

TblNew_Issues_S hares
Issue_ID (Primary key)
Date
Number_of_share s (for each new issue)

TblNumber_of_sh ares
Transaction_Cou nter
Name_ID
Issue_ID
Number_of_Share s
Start_no and End_no

Maybe create another table for shares traded?

[StenKoll]

U N Me <un**@together. com> wrote in message news:<3F******* ********@togeth er.com>...

The shares that have been traded will now get new numbers and get re-inserted into the
stock/issue table (if I understand correctly) but now with a new stockholder id.
You are basically right, but if I understand you correctly you're
missing a essential point: The shares' numbers has to be locked to the
specific shares, so when 100 of the shares initially sold to owner 1
with numbers 1-1000 are sold to owner number n, the shares still have
number 901-1000. If they are re inserted with new numbers, this will
cause havoc to the numbers of the other shares in the table. or??!?
Maybe I should create a table with records for every 100 shares and
then point the shareholderID to the appropriate interval? Then I could
just change the shareholderID when the shares are traded. It will
however be a fairly long table.

Still thankfull for any help

Sten

StenKoll wrote:

Thank you for your suggestion, but how do I then keep track of the
shares in my reports? Furthermore: How do I keep track of the shares
when they ar traded the second and n'th time?

Maybe the easiest way to get around this is to manually number the
shares, but if there is a way around this I would like som help
finding it.

Maybe you can help me out here in understanding the problem
You have a stockholder table.
You have a new issues table
You have a stockholder and new issues table

So you add a stockholder record.
Later on you add a new issues record
Then you add new stock/issue records for those that bought the new issue.

Now, assume you have a shares traded table. This would have the
start/end/stockholder/qty in it.
The shares that have been traded will now get new numbers and get re-inserted into the
stock/issue table (if I understand correctly) but now with a new stockholder id.

Having this new table will work. If I purchase 1000 shares and sell 100, I would add a
record in the shares traded table. The 100 get added into the new issues table if I
sold them back or into the stock/issues table with the new buyer.

I could sell those 1000 shares 10 times, 100 shares at a time, thus I'd end up with 10
records in the shares traded table. I could then take my initial purchase amount and
subtract that from the shares traded table (using Dsum() or a SQL statement.

Anyway, I think another table is required....if I understand your problem correctly.

If you
Thanks,

U N Me <un**@together. com> wrote in message news:<3F******* ********@togeth er.com>...

StenKoll wrote:

> Help needed in order to create a register of stocks in a company. In
> accordance with local laws I need to give each individual share a
> number. I have accomplished this by establishing three tables (se
> below) then I run a query giving me a running total, which give me the
> first stock in the batch purchased by an individual, then I use this
> number and add the number of shares in order to find the number of the
> individuals' last share.
>
> So far everything looks fine ? BUT- this is changed when the
> individuals start trading stocks. This isn't a regular event, but I
> want to find a way to handle it if and when it occurs. Due to the way
> I have it now, the shares before the ones traded keep their correct
> numbers, but the ones after are assigned new numbers.
>
> What I need is a way to remove the traded amount from the
> Number_of_Share s in a post and recalculate the correct number for this
> post without changing Start_no and End_no for the other shares in the
> table?
> In addition, I need to place the Start_no and End_no for the removed
> shares as a new entry in the table TblNumber_of_sh ares
>
> Any advice on how to do this would be greatly appreciated! I realize
> that I might need a different way to solve how I assign individual
> nubers in order to make this work, but this is how I've acomplished it
> so far.
>
> I'am fairly new to access and VB programming, so please be gentle with
> me :-0
>
> Thanks,
> Sten Koll
>
> About the database ( Pasted from an earlier post?)
>
> Tables
> Name:
> TblName_ID (Primary key)
> Firstname
> Lastname
>
> TblNew_Issues_S hares
> Issue_ID (Primary key)
> Date
> Number_of_share s (for each new issue)
>
> TblNumber_of_sh ares
> Transaction_Cou nter
> Name_ID
> Issue_ID
> Number_of_Share s
> Start_no and End_no

Maybe create another table for shares traded?

[U N Me]

StenKoll wrote:

U N Me <un**@together. com> wrote in message news:<3F******* ********@togeth er.com>...

The shares that have been traded will now get new numbers and get re-inserted into the
stock/issue table (if I understand correctly) but now with a new stockholder id.

You are basically right, but if I understand you correctly you're
missing a essential point: The shares' numbers has to be locked to the
specific shares, so when 100 of the shares initially sold to owner 1
with numbers 1-1000 are sold to owner number n, the shares still have
number 901-1000. If they are re inserted with new numbers, this will
cause havoc to the numbers of the other shares in the table. or??!?
Maybe I should create a table with records for every 100 shares and
then point the shareholderID to the appropriate interval? Then I could
just change the shareholderID when the shares are traded. It will
however be a fairly long table.

Still thankfull for any help

I had it wrong since you said you need to renumber them. Anyway....
I have stocks 1-1000. The numbers of the stock shares are 1 to 1000. I am person A. In my
table I have the share, the number of shares, and the numbers. I now sell shares 1-150. to
person B I would update the StocksTraded and say 150, as the amount, and enter the begin/end
numbers, the date of transaction, etc/. I would now create a new record in the Shares table
with Person B id, the begin/end numbers, the count, etc.

Basically the SharesTraded table is a transaction table. I can see what the initial shares
were and a running history of the shares that were traded. Teh share nukmbers never change,
the just get shied to a new buyer.

I think right now you want to keep this to a minimum of tables. If I were writing your app I
would add a transaction file so I can review history on an account. With good indexing and
database relations the system should be blazingly fast.

[Sten Koll]

I think I understand what you mean, but I'm having slight problems
implementing it into a working database...

In other words I shall use 4 tables:

tblOwner ' containing ownredata
OwnerID(Primary Key)
Firstname
Lastname

tbl New_Issues
IssueID(Primary Key)
Date
Number_of_share s 'per issue

tblShares_Owner ' containing the initial purchase of shares
Transaction(Pri maryKey)
OwnerID
IssueID
Start_No
End_No
No_Shares_Owner 'shares per owner

tblShares_Trade d ' containing changes to tblShares_Owner
OwnerID
IssueID
Start_No
End_No

The reports displaying the current amount of an individuals shares are
given through merging the two later tables or am I lost here?

Thank you for taking your time. Plz grab me via mail in order to avoid
the timedelay (and also in order for me to avoid making an official fool
of myself... ;-)..)

U N Me <un**@together. com> wrote in message news:<3F******* ********@togeth er.com>... I had it wrong since you said you need to renumber them. >Anyway....
I have stocks 1-1000. The numbers of the stock shares are >1 to 1000. I am person A. In mytable I have the share, the number of shares, and the >numbers. I now sell shares 1-150. toperson B I would update the StocksTraded and say 150, as >the amount, and enter the begin/endnumbers, the date of transaction, etc/. I would now create >a new record in the Shares tablewith Person B id, the begin/end numbers, the count, etc. Basically the SharesTraded table is a transaction table. I >can see what the initial shareswere and a running history of the shares that were traded. >Teh share nukmbers never change,the just get shied to a new buyer. I think right now you want to keep this to a minimum of >tables. If I were writing your app Iwould add a transaction file so I can review history on an >account. With good indexing anddatabase relations the system should be blazingly fast.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[U N Me]

Sten Koll wrote:

I think I understand what you mean, but I'm having slight problems
implementing it into a working database...

In other words I shall use 4 tables:

tblOwner ' containing ownredata
OwnerID(Primary Key)
Firstname
Lastname

tbl New_Issues
IssueID(Primary Key)
Date
Number_of_share s 'per issue

tblShares_Owner ' containing the initial purchase of shares
Transaction(Pri maryKey)
OwnerID
IssueID
Start_No
End_No
No_Shares_Owner 'shares per owner
add a date purchased. amt purchased....un less subtraction works


tblShares_Trade d ' containing changes to tblShares_Owner
add TradedID (autonumber), primary
OwnerID
IssueID
Start_No
End_No
add a date sold, amt traded....unles s subtraction works


The reports displaying the current amount of an individuals shares are
given through merging the two later tables or am I lost here?

I'd also put in a couple of date fields; date shares were purchased (owner),
traded (traded). I'd also put in the amounts/counts unless you can subtract
the share numbers from each other to get the count. Ex: SharesOwnere
start = 1 end = 1000. ShareCount 1000. Do the same for traded....unles s
you can substract beging and end from each other.. When you do queryies,
you have the count/amt from either a field or via subtraction.... same in the
traded table.

[Sten Koll]

hmmmmmm Looks like I'm in over my head....

From: U N Me

Sten Koll wrote:

I think I understand what you mean, but I'm having slight problems
implementing it into a working database...

In other words I shall use 4 tables:

tblOwner ' containing ownredata
OwnerID(Primary Key)
Firstname
Lastname

tbl New_Issues
IssueID(Primary Key)
Date
Number_of_share s 'per issue

tblShares_Owner ' containing the initial purchase of shares
Transaction(Pri maryKey)
OwnerID
IssueID
Start_No
End_No
No_Shares_Owner 'shares per owner
add a date purchased. amt purchased....un less subtraction works


tblShares_Trade d ' containing changes to tblShares_Owner
add TradedID (autonumber), primary
OwnerID
IssueID
Start_No
End_No
add a date sold, amt traded....unles s subtraction works


The reports displaying the current amount of an individuals shares are
given through merging the two later tables or am I lost here?

I'd also put in a couple of date fields; date shares were purchased
(owner),
traded (traded). I'd also put in the amounts/counts unless you can
subtract
the share numbers from each other to get the count. Ex: SharesOwnere
start = 1 end = 1000. ShareCount 1000. Do the same for traded....unles s
you can substract beging and end from each other.. When you do queryies,
you have the count/amt from either a field or via subtraction.... same in
the
traded table.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[U N Me]

Sten Koll wrote:

hmmmmmm Looks like I'm in over my head....

Not really. But don't you need a field to store how many shares have been
purchased and when traded how many sold? If you can get the count from
subtracting the start/ending numbers you would not need a field to store
that since the amount can be calculated. And wouldn't you want a date field
to know when shares are purchased and when shares are traded in order to
have a sense of the history?

A couple of commands to look at in on-line help; DCount() and DSum()
functions. Also look at creating Totals queries when summing, counting,
averaging records in a file.

Ex:
Table Buyer:
BuyID = 1, Name = Mike
BuyID = 2, Name = Sam

Table Issued
IssuedID = 1 IssueName = IBM IssueDate 1/1/2003
IssuedID = 2 IssueName = Microsoft IssueDate 2/1/2003

Table Purchase
TableID = 1, BuyerID = 1, IssueID = 1, PurchDate = 12/1/03, StockCnt = 1000,
Start = 1, End = 1000
TableID = 2, BuyerID = 1, IssueID = 1, PurchDate = 12/2/03, StockCnt = 1000,
Start = 10001, End = 2000
TableID = 3, BuyerID = 2, IssueID = 1, PurchDate = 12/3/03, StockCnt = 1000,
Start = 2001, End = 3000
TableID = 4, BuyerID = 2, IssueID = 1, PurchDate = 12/4/03, StockCnt = 100,
Start = 1, End = 100
TableID = 5, BuyerID = 1, IssueID = 2, PurchDate = 12/5/03, StockCnt = 100,
Start = 1, End = 1

Table Traded
TradedID = 1, BuyerID = 2, PurchaseID = 1, TradeAmt = 100, TradeDate =
12/4/03, Start = 1, End = 100
TradedID = 1, BuyerID = Null, PurchaseID = 1, TradeAmt = 100, TradeDate =
12/5/03, Start = 101, End = 200

From this I can tell Mike bought 1000 shares of IBM on 2 separate instances
and owns 2000 shares of IBM and he purchased 100 shares of Microsoft in
another instance. Sam bought 1000 shares of IBM and bought 100 shares from
Mike another time

Mike sold 2 100 lots of IBM. Mike owns shares 201 to 1000 and owns 800
shares.

I have access to dates of trades and purchase, I know who bought or sold
shares.

Is this what you want to accomplish? Am I close?