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]

Re: Replacing data in a table WITHOUT changing the numbers created froma running sum..

StenKoll wrote:
[color=blue]
> 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[/color]

Maybe create another table for shares traded?

[StenKoll]

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

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 <unme@together. com> wrote in message news:<3FDAEB4C. 18346396@togeth er.com>...[color=blue]
> StenKoll wrote:
>[color=green]
> > 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[/color]
>
> Maybe create another table for shares traded?[/color]

[U N Me]

Re: Replacing data in a table WITHOUT changing the numbers created froma running sum..

StenKoll wrote:
[color=blue]
> 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.
>[/color]

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.

[color=blue]
> If you
> Thanks,
>
> U N Me <unme@together. com> wrote in message news:<3FDAEB4C. 18346396@togeth er.com>...[color=green]
> > StenKoll wrote:
> >[color=darkred]
> > > 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[/color]
> >
> > Maybe create another table for shares traded?[/color][/color]

[StenKoll]

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

U N Me <unme@together. com> wrote in message news:<3FDD0626. 213301CE@togeth er.com>...
[color=blue]
> 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.
>[/color]
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

[color=blue]
> StenKoll wrote:
>[color=green]
> > 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.
> >[/color]
>
> 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.
>
>[color=green]
> > If you
> > Thanks,
> >
> > U N Me <unme@together. com> wrote in message news:<3FDAEB4C. 18346396@togeth er.com>...[color=darkred]
> > > 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?[/color][/color][/color]

[U N Me]

Re: Replacing data in a table WITHOUT changing the numbers createdfrom a running sum..

StenKoll wrote:
[color=blue]
> U N Me <unme@together. com> wrote in message news:<3FDD0626. 213301CE@togeth er.com>...
>[color=green]
> > 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.
> >[/color]
> 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
>[/color]

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]

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

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... ;-)..)
[color=blue]
> U N Me <unme@together. com> wrote in message[/color]
news:<3FDD0626. 213301CE@togeth er.com>...[color=blue]
>[/color]
[color=blue]
>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[/color]
am person A. In my[color=blue]
>table I have the share, the number of shares, and the >numbers. I now[/color]
sell shares 1-150. to[color=blue]
>person B I would update the StocksTraded and say 150, as >the amount,[/color]
and enter the begin/end[color=blue]
>numbers, the date of transaction, etc/. I would now create >a new[/color]
record in the Shares table[color=blue]
>with Person B id, the begin/end numbers, the count, etc.[/color]
[color=blue]
>Basically the SharesTraded table is a transaction table. I >can see[/color]
what the initial shares[color=blue]
>were and a running history of the shares that were traded. >Teh share[/color]
nukmbers never change,[color=blue]
>the just get shied to a new buyer.[/color]
[color=blue]
>I think right now you want to keep this to a minimum of >tables. If I[/color]
were writing your app I[color=blue]
>would add a transaction file so I can review history on an >account.[/color]
With good indexing and[color=blue]
>database relations the system should be blazingly fast.[/color]




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

[U N Me]

Re: Replacing data in a table WITHOUT changing the numbers createdfrom a running sum..

Sten Koll wrote:
[color=blue]
> 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[/color]

add a date purchased. amt purchased....un less subtraction works
[color=blue]
>
>
> tblShares_Trade d ' containing changes to tblShares_Owner[/color]

add TradedID (autonumber), primary
[color=blue]
> OwnerID
> IssueID
> Start_No
> End_No[/color]

add a date sold, amt traded....unles s subtraction works
[color=blue]
>
>
> The reports displaying the current amount of an individuals shares are
> given through merging the two later tables or am I lost here?[/color]

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]

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

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

From: U N Me

Sten Koll wrote:
[color=blue]
> 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[/color]

add a date purchased. amt purchased....un less subtraction works
[color=blue]
>
>
> tblShares_Trade d ' containing changes to tblShares_Owner[/color]

add TradedID (autonumber), primary
[color=blue]
> OwnerID
> IssueID
> Start_No
> End_No[/color]

add a date sold, amt traded....unles s subtraction works
[color=blue]
>
>
> The reports displaying the current amount of an individuals shares are
> given through merging the two later tables or am I lost here?[/color]

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]

Re: Replacing data in a table WITHOUT changing the numbers createdfrom a running sum..

Sten Koll wrote:
[color=blue]
> hmmmmmm Looks like I'm in over my head....
>[/color]

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?

[Sten Koll]

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

We are getting closer and closer, but I’ll try to specify even more,
since my previous description has not been accurate enough.
I am to build a database in order to keep records of owners in ONE
company. I want to keep data about the owners in tblOwner.
Every now and then the company needs more money, and asks the owners to
contribute with more money. This is done through the issue of new
shares. I want to keep data, such as amount issued, price and so forth
in tblNewIssuesSha res.
Obviously I also want to combine the two, in order to find how many
shares each individual owns. This is kept in tblNumberOfShar es.
According to local laws, I have to issue certificates to each
shareholder, stating the total number of shares held, as well as the
shares’ individual numbers.

Therefore, in the tblNumberOfShar es, I have included a transaction
counter, numbering each initial purchase of shares. Through a query, by
sorting according to the transaction counter, I get the individual start
and end number for each individual’s shares. So far everything works
fine, but I run into problems when the shareholders start to trade the
shares in between themselves. Since my numberingsystem is based on a
running sum of the shares held, changing the amount held by each
individual also changes the numbers of the shares following that
specific owner.

I am looking for a way to transfer the shares, along with their initial
numbers from one owner to another while reducing the numbers for the
initial owner with the amount transferred, while at the same time
leaving the sharenumbers (amount and individual numbers) unchanged.
So my question is whether this is possible or not, and if it is, how do
I do it?

U N Me <unme@together. com> wrote in message
news:<3FDE4160. A644EAA@togethe r.com>...[color=blue]
> Sten Koll wrote:
>[color=green]
> > hmmmmmm Looks like I'm in over my head....
> >[/color]
>
> Not really. But don't you need a field to store how many shares have[/color]
been[color=blue]
> purchased and when traded how many sold? If you can get the count[/color]
from[color=blue]
> subtracting the start/ending numbers you would not need a field to[/color]
store[color=blue]
> that since the amount can be calculated. And wouldn't you want a date[/color]
field[color=blue]
> to know when shares are purchased and when shares are traded in order[/color]
to[color=blue]
> 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,[/color]
counting,[color=blue]
> 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 =[/color]
1000,[color=blue]
> Start = 1, End = 1000
> TableID = 2, BuyerID = 1, IssueID = 1, PurchDate = 12/2/03, StockCnt =[/color]
1000,[color=blue]
> Start = 10001, End = 2000
> TableID = 3, BuyerID = 2, IssueID = 1, PurchDate = 12/3/03, StockCnt =[/color]
1000,[color=blue]
> Start = 2001, End = 3000
> TableID = 4, BuyerID = 2, IssueID = 1, PurchDate = 12/4/03, StockCnt =[/color]
100,[color=blue]
> Start = 1, End = 100
> TableID = 5, BuyerID = 1, IssueID = 2, PurchDate = 12/5/03, StockCnt =[/color]
100,[color=blue]
> Start = 1, End = 1
>
> Table Traded
> TradedID = 1, BuyerID = 2, PurchaseID = 1, TradeAmt = 100, TradeDate[/color]
=[color=blue]
> 12/4/03, Start = 1, End = 100
> TradedID = 1, BuyerID = Null, PurchaseID = 1, TradeAmt = 100,[/color]
TradeDate =[color=blue]
> 12/5/03, Start = 101, End = 200
>
> From this I can tell Mike bought 1000 shares of IBM on 2 separate[/color]
instances[color=blue]
> and owns 2000 shares of IBM and he purchased 100 shares of Microsoft[/color]
in[color=blue]
> another instance. Sam bought 1000 shares of IBM and bought 100 shares[/color]
from[color=blue]
> Mike another time
>
> Mike sold 2 100 lots of IBM. Mike owns shares 201 to 1000 and owns[/color]
800[color=blue]
> shares.
>
> I have access to dates of trades and purchase, I know who bought or[/color]
sold[color=blue]
> shares.
>
> Is this what you want to accomplish? Am I close?[/color]





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

[Patrick Finucane]

Re: Replacing data in a table WITHOUT changing the numbers createdfrom a running sum..

Sten Koll wrote:
[color=blue]
> We are getting closer and closer, but I’ll try to specify even more,
> since my previous description has not been accurate enough.
> I am to build a database in order to keep records of owners in ONE
> company. I want to keep data about the owners in tblOwner.
> Every now and then the company needs more money, and asks the owners to
> contribute with more money. This is done through the issue of new
> shares. I want to keep data, such as amount issued, price and so forth
> in tblNewIssuesSha res.
> Obviously I also want to combine the two, in order to find how many
> shares each individual owns.[/color]

You don't want to combine tblOwer with tblNewIssueShar es. You want to
combine tblOwner with TblNumber_of_sh ares.
[color=blue]
> This is kept in tblNumberOfShar es.
> According to local laws, I have to issue certificates to each
> shareholder, stating the total number of shares held, as well as the
> shares’ individual numbers.
>
> Therefore, in the tblNumberOfShar es, I have included a transaction
> counter, numbering each initial purchase of shares. Through a query, by
> sorting according to the transaction counter, I get the individual start
> and end number for each individual’s shares. So far everything works
> fine, but I run into problems when the shareholders start to trade the
> shares in between themselves. Since my numberingsystem is based on a
> running sum of the shares held, changing the amount held by each
> individual also changes the numbers of the shares following that
> specific owner.
>
> I am looking for a way to transfer the shares, along with their initial
> numbers from one owner to another while reducing the numbers for the
> initial owner with the amount transferred, while at the same time
> leaving the sharenumbers (amount and individual numbers) unchanged.
> So my question is whether this is possible or not, and if it is, how do
> I do it?[/color]

Just how deep are you in this project. I mean, is this a production
application that has been in use forever or is this a relatively new app in
the test and debug mode? If test and debug, I'd scrap much of your
structure. You seem to want to only have 3 tables in your application. You
will have a long, hard, row to hoe with that concept.

Here's a change I would do. In TblNew_Issues_S hares I would have a start
and ending number, a next number to sell, perhaps remaining shares left
fields. When shares are purchased, I would start at the next number and
allocate the number of shares purchased and update the next number to sell
to the ending number + 1. Once all shares have been fully
allocated/purchased, I would set a flag to close that share. From then on,
any shares would come from trading/purchases amoung the owners.

I would have a transaction file of trades. I'd have a transaction file of
purchase...or combine the two into 1 table.

I would forego creating numbers via running sums. There is a better method
I am sure. When the numbers are created, they should be stored in the new
issues table and then calced when a purchase is made.

This is a hint. I suggest all of your tables have an autonumber that is
the primary key for the table. You can set relationships between tables.
With queries, you can join the tables together. With transaction tables you
can use DSUM() and DCOUNT() or use a TOTALS query (easily created in the
builder.

I think you are making this project more complex than it is. I would take a
piece of paper out and flowchart the workflow....not hing fancy...but try to
create the steps that are done....adding owners...adding new issues...the
purchase flow....how do you expect to handle trades....what info do you
need....do you require dates for historical info....etc. With some proper
planning you come up with a useable app. Start from the top...work to the
bottom. Know what results you want to achieve

PS....there is a forger using my identity. Check the e-mail headers to
ensure this does not come from an anonymous source. I will not provide
links to you...that is one way you know this is not a forgery.

Good luck.

[U N Me]

Re: Replacing data in a table WITHOUT changing the numbers createdfrom a running sum..

Sten Koll wrote:
[color=blue]
> We are getting closer and closer, but I’ll try to specify even more,
> since my previous description has not been accurate enough.
> I am to build a database in order to keep records of owners in ONE
> company. I want to keep data about the owners in tblOwner.
> Every now and then the company needs more money, and asks the owners to
> contribute with more money. This is done through the issue of new
> shares. I want to keep data, such as amount issued, price and so forth
> in tblNewIssuesSha res.
> Obviously I also want to combine the two, in order to find how many
> shares each individual owns.[/color]

You don't want to combine tblOwer with tblNewIssueShar es. You want to
combine tblOwner with TblNumber_of_sh ares.
[color=blue]
> This is kept in tblNumberOfShar es.
> According to local laws, I have to issue certificates to each
> shareholder, stating the total number of shares held, as well as the
> shares’ individual numbers.
>
> Therefore, in the tblNumberOfShar es, I have included a transaction
> counter, numbering each initial purchase of shares. Through a query, by
> sorting according to the transaction counter, I get the individual start
> and end number for each individual’s shares. So far everything works
> fine, but I run into problems when the shareholders start to trade the
> shares in between themselves. Since my numberingsystem is based on a
> running sum of the shares held, changing the amount held by each
> individual also changes the numbers of the shares following that
> specific owner.
>
> I am looking for a way to transfer the shares, along with their initial
> numbers from one owner to another while reducing the numbers for the
> initial owner with the amount transferred, while at the same time
> leaving the sharenumbers (amount and individual numbers) unchanged.
> So my question is whether this is possible or not, and if it is, how do
> I do it?[/color]

Just how deep are you in this project. I mean, is this a production
application that has been in use forever or is this a relatively new app in
the test and debug mode? If test and debug, I'd scrap much of your
structure. You seem to want to only have 3 tables in your application. You

will have a long, hard, row to hoe with that concept.

Here's a change I would do. In TblNew_Issues_S hares I would have a start
and ending number, a next number to sell, perhaps remaining shares left
fields. When shares are purchased, I would start at the next number and
allocate the number of shares purchased and update the next number to sell
to the ending number + 1. Once all shares have been fully
allocated/purchased, I would set a flag to close that share. From then on,
any shares would come from trading/purchases amoung the owners.

I would have a transaction file of trades. I'd have a transaction file of
purchase...or combine the two into 1 table.

I would forego creating numbers via running sums. There is a better method
I am sure. When the numbers are created, they should be stored in the new
issues table and then calced when a purchase is made.

This is a hint. I suggest all of your tables have an autonumber that is
the primary key for the table. You can set relationships between tables.
With queries, you can join the tables together. With transaction tables you

can use DSUM() and DCOUNT() or use a TOTALS query (easily created in the
builder.

I think you are making this project more complex than it is. I would take a

piece of paper out and flowchart the workflow....not hing fancy...but try to
create the steps that are done....adding owners...adding new issues...the
purchase flow....how do you expect to handle trades....what info do you
need....do you require dates for historical info....etc. With some proper
planning you come up with a useable app. Start from the top...work to the
bottom. Know what results you want to achieve

PS....there is a forger using my identity. Check the e-mail headers to
ensure this does not come from an anonymous source. I will not provide
links to you...that is one way you know this is not a forgery.

Good luck.

[StenKoll]

Re: Moving numbered items as a whole or partially between owners...

I am about to tear my hair out, because I can't find a way to do
this....
I have made a tabNewIssues, where I use Dsum in order to give the
shares individual numbers. When all the shares are purchased from the
new issue, I use an update query in order to transfer them to a new
table i call tblOwnership. As a result I get the individual numbers of
the batches of shares in the new table. In this table owner 1 is li
sted with shares with StartNo 1 and EndNo 1000, owner 2 with shares
with StartNo1001 and EndNo 2500 and so forth. So far I manage quite
well.
In an ideal world, I should now be able to create a tblTrade in which
I registered the trade of shares between current and new owners, and
calculate those traded shares’ individual numbers by using the
initial number and then subtract the amount traded.
Whatr I need is a way to identify who is buying and who is selling a
certain amount of shares, and then subtract those shares’
individual numbers from those initially given.
As an example, owner 1 would sell 100 shares to owner 2. I need a way
to subtract those 100 from owner 1 and add them to owner 2 BUT I also
need to keep their initial numbers so that Owner 1 has 900 shares
ranging from 1 to 900 wheras owner 2 has 1600 shares ranging from 901
through 2500.

In this matter I am completely stuck, and I am uncertain how to go on
with this project. Shall I keep the trades in the sam table as I have
transfered the shares purchased from the new issues, or is it better
to draw the shares that are traded to another table and delete them
from the tblOwnership?

I had an idea about setting up a table with a looooooong list of
sharenumbers and then use a column for owners, thus using the record
of owner as a pointer to the individual share number, but it seems
complicated and slow, so if anyone has a better way, I am eager to get
some feedback.

Sesons greetings to y’all!

Sten Koll


U N Me <unme@together. com> wrote in message news:<3FDF8CC6. 12264708@togeth er.com>...[color=blue]
> Sten Koll wrote:
>[color=green]
> > We are getting closer and closer, but I?ll try to specify even more,
> > since my previous description has not been accurate enough.
> > I am to build a database in order to keep records of owners in ONE
> > company. I want to keep data about the owners in tblOwner.
> > Every now and then the company needs more money, and asks the owners to
> > contribute with more money. This is done through the issue of new
> > shares. I want to keep data, such as amount issued, price and so forth
> > in tblNewIssuesSha res.
> > Obviously I also want to combine the two, in order to find how many
> > shares each individual owns.[/color]
>
> You don't want to combine tblOwer with tblNewIssueShar es. You want to
> combine tblOwner with TblNumber_of_sh ares.
>[color=green]
> > This is kept in tblNumberOfShar es.
> > According to local laws, I have to issue certificates to each
> > shareholder, stating the total number of shares held, as well as the
> > shares? individual numbers.
> >
> > Therefore, in the tblNumberOfShar es, I have included a transaction
> > counter, numbering each initial purchase of shares. Through a query, by
> > sorting according to the transaction counter, I get the individual start
> > and end number for each individual?s shares. So far everything works
> > fine, but I run into problems when the shareholders start to trade the
> > shares in between themselves. Since my numberingsystem is based on a
> > running sum of the shares held, changing the amount held by each
> > individual also changes the numbers of the shares following that
> > specific owner.
> >
> > I am looking for a way to transfer the shares, along with their initial
> > numbers from one owner to another while reducing the numbers for the
> > initial owner with the amount transferred, while at the same time
> > leaving the sharenumbers (amount and individual numbers) unchanged.
> > So my question is whether this is possible or not, and if it is, how do
> > I do it?[/color]
>
> Just how deep are you in this project. I mean, is this a production
> application that has been in use forever or is this a relatively new app in
> the test and debug mode? If test and debug, I'd scrap much of your
> structure. You seem to want to only have 3 tables in your application. You
>
> will have a long, hard, row to hoe with that concept.
>
> Here's a change I would do. In TblNew_Issues_S hares I would have a start
> and ending number, a next number to sell, perhaps remaining shares left
> fields. When shares are purchased, I would start at the next number and
> allocate the number of shares purchased and update the next number to sell
> to the ending number + 1. Once all shares have been fully
> allocated/purchased, I would set a flag to close that share. From then on,
> any shares would come from trading/purchases amoung the owners.
>
> I would have a transaction file of trades. I'd have a transaction file of
> purchase...or combine the two into 1 table.
>
> I would forego creating numbers via running sums. There is a better method
> I am sure. When the numbers are created, they should be stored in the new
> issues table and then calced when a purchase is made.
>
> This is a hint. I suggest all of your tables have an autonumber that is
> the primary key for the table. You can set relationships between tables.
> With queries, you can join the tables together. With transaction tables you
>
> can use DSUM() and DCOUNT() or use a TOTALS query (easily created in the
> builder.
>
> I think you are making this project more complex than it is. I would take a
>
> piece of paper out and flowchart the workflow....not hing fancy...but try to
> create the steps that are done....adding owners...adding new issues...the
> purchase flow....how do you expect to handle trades....what info do you
> need....do you require dates for historical info....etc. With some proper
> planning you come up with a useable app. Start from the top...work to the
> bottom. Know what results you want to achieve
>
> PS....there is a forger using my identity. Check the e-mail headers to
> ensure this does not come from an anonymous source. I will not provide
> links to you...that is one way you know this is not a forgery.
>
> Good luck.[/color]

[StenKoll]

Re: Moving numbered items as a whole or partially between owners...

I am about to tear my hair out, because I can't find a way to do
this....
I have made a tabNewIssues, where I use Dsum in order to give the
shares individual numbers. When all the shares are purchased from the
new issue, I use an update query in order to transfer them to a new
table i call tblOwnership. As a result I get the individual numbers of
the batches of shares in the new table. In this table owner 1 is li
sted with shares with StartNo 1 and EndNo 1000, owner 2 with shares
with StartNo1001 and EndNo 2500 and so forth. So far I manage quite
well.
In an ideal world, I should now be able to create a tblTrade in which
I registered the trade of shares between current and new owners, and
calculate those traded shares’ individual numbers by using the
initial number and then subtract the amount traded.
Whatr I need is a way to identify who is buying and who is selling a
certain amount of shares, and then subtract those shares’
individual numbers from those initially given.
As an example, owner 1 would sell 100 shares to owner 2. I need a way
to subtract those 100 from owner 1 and add them to owner 2 BUT I also
need to keep their initial numbers so that Owner 1 has 900 shares
ranging from 1 to 900 wheras owner 2 has 1600 shares ranging from 901
through 2500.

In this matter I am completely stuck, and I am uncertain how to go on
with this project. Shall I keep the trades in the sam table as I have
transfered the shares purchased from the new issues, or is it better
to draw the shares that are traded to another table and delete them
from the tblOwnership?

I had an idea about setting up a table with a looooooong list of
sharenumbers and then use a column for owners, thus using the record
of owner as a pointer to the individual share number, but it seems
complicated and slow, so if anyone has a better way, I am eager to get
some feedback.

Sesons greetings to y’all!

Sten Koll


U N Me <unme@together. com> wrote in message news:<3FDF8CC6. 12264708@togeth er.com>...[color=blue]
> Sten Koll wrote:
>[color=green]
> > We are getting closer and closer, but I?ll try to specify even more,
> > since my previous description has not been accurate enough.
> > I am to build a database in order to keep records of owners in ONE
> > company. I want to keep data about the owners in tblOwner.
> > Every now and then the company needs more money, and asks the owners to
> > contribute with more money. This is done through the issue of new
> > shares. I want to keep data, such as amount issued, price and so forth
> > in tblNewIssuesSha res.
> > Obviously I also want to combine the two, in order to find how many
> > shares each individual owns.[/color]
>
> You don't want to combine tblOwer with tblNewIssueShar es. You want to
> combine tblOwner with TblNumber_of_sh ares.
>[color=green]
> > This is kept in tblNumberOfShar es.
> > According to local laws, I have to issue certificates to each
> > shareholder, stating the total number of shares held, as well as the
> > shares? individual numbers.
> >
> > Therefore, in the tblNumberOfShar es, I have included a transaction
> > counter, numbering each initial purchase of shares. Through a query, by
> > sorting according to the transaction counter, I get the individual start
> > and end number for each individual?s shares. So far everything works
> > fine, but I run into problems when the shareholders start to trade the
> > shares in between themselves. Since my numberingsystem is based on a
> > running sum of the shares held, changing the amount held by each
> > individual also changes the numbers of the shares following that
> > specific owner.
> >
> > I am looking for a way to transfer the shares, along with their initial
> > numbers from one owner to another while reducing the numbers for the
> > initial owner with the amount transferred, while at the same time
> > leaving the sharenumbers (amount and individual numbers) unchanged.
> > So my question is whether this is possible or not, and if it is, how do
> > I do it?[/color]
>
> Just how deep are you in this project. I mean, is this a production
> application that has been in use forever or is this a relatively new app in
> the test and debug mode? If test and debug, I'd scrap much of your
> structure. You seem to want to only have 3 tables in your application. You
>
> will have a long, hard, row to hoe with that concept.
>
> Here's a change I would do. In TblNew_Issues_S hares I would have a start
> and ending number, a next number to sell, perhaps remaining shares left
> fields. When shares are purchased, I would start at the next number and
> allocate the number of shares purchased and update the next number to sell
> to the ending number + 1. Once all shares have been fully
> allocated/purchased, I would set a flag to close that share. From then on,
> any shares would come from trading/purchases amoung the owners.
>
> I would have a transaction file of trades. I'd have a transaction file of
> purchase...or combine the two into 1 table.
>
> I would forego creating numbers via running sums. There is a better method
> I am sure. When the numbers are created, they should be stored in the new
> issues table and then calced when a purchase is made.
>
> This is a hint. I suggest all of your tables have an autonumber that is
> the primary key for the table. You can set relationships between tables.
> With queries, you can join the tables together. With transaction tables you
>
> can use DSUM() and DCOUNT() or use a TOTALS query (easily created in the
> builder.
>
> I think you are making this project more complex than it is. I would take a
>
> piece of paper out and flowchart the workflow....not hing fancy...but try to
> create the steps that are done....adding owners...adding new issues...the
> purchase flow....how do you expect to handle trades....what info do you
> need....do you require dates for historical info....etc. With some proper
> planning you come up with a useable app. Start from the top...work to the
> bottom. Know what results you want to achieve
>
> PS....there is a forger using my identity. Check the e-mail headers to
> ensure this does not come from an anonymous source. I will not provide
> links to you...that is one way you know this is not a forgery.
>
> Good luck.[/color]