By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

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

P: n/a
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_Shares 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_shares

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_Shares
Issue_ID (Primary key)
Date
Number_of_shares (for each new issue)

TblNumber_of_shares
Transaction_Counter
Name_ID
Issue_ID
Number_of_Shares
Start_no and End_no
Nov 12 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a
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_Shares 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_shares

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_Shares
Issue_ID (Primary key)
Date
Number_of_shares (for each new issue)

TblNumber_of_shares
Transaction_Counter
Name_ID
Issue_ID
Number_of_Shares
Start_no and End_no


Maybe create another table for shares traded?

Nov 12 '05 #2

P: n/a
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***************@together.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_Shares 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_shares

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_Shares
Issue_ID (Primary key)
Date
Number_of_shares (for each new issue)

TblNumber_of_shares
Transaction_Counter
Name_ID
Issue_ID
Number_of_Shares
Start_no and End_no


Maybe create another table for shares traded?

Nov 12 '05 #3

P: n/a
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***************@together.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_Shares 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_shares

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_Shares
Issue_ID (Primary key)
Date
Number_of_shares (for each new issue)

TblNumber_of_shares
Transaction_Counter
Name_ID
Issue_ID
Number_of_Shares
Start_no and End_no


Maybe create another table for shares traded?


Nov 12 '05 #4

P: n/a
U N Me <un**@together.com> wrote in message news:<3F***************@together.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***************@together.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_Shares 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_shares
>
> 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_Shares
> Issue_ID (Primary key)
> Date
> Number_of_shares (for each new issue)
>
> TblNumber_of_shares
> Transaction_Counter
> Name_ID
> Issue_ID
> Number_of_Shares
> Start_no and End_no

Maybe create another table for shares traded?

Nov 12 '05 #5

P: n/a
StenKoll wrote:
U N Me <un**@together.com> wrote in message news:<3F***************@together.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.
Nov 12 '05 #6

P: n/a
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(PrimaryKey)
Firstname
Lastname

tbl New_Issues
IssueID(PrimaryKey)
Date
Number_of_shares 'per issue

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

tblShares_Traded ' 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***************@together.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!
Nov 12 '05 #7

P: n/a
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(PrimaryKey)
Firstname
Lastname

tbl New_Issues
IssueID(PrimaryKey)
Date
Number_of_shares 'per issue

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


tblShares_Traded ' containing changes to tblShares_Owner
add TradedID (autonumber), primary
OwnerID
IssueID
Start_No
End_No
add a date sold, amt traded....unless 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....unless
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.
Nov 12 '05 #8

P: n/a
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(PrimaryKey)
Firstname
Lastname

tbl New_Issues
IssueID(PrimaryKey)
Date
Number_of_shares 'per issue

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


tblShares_Traded ' containing changes to tblShares_Owner
add TradedID (autonumber), primary
OwnerID
IssueID
Start_No
End_No
add a date sold, amt traded....unless 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....unless
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!
Nov 12 '05 #9

P: n/a
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?
Nov 12 '05 #10

P: n/a
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 tblNewIssuesShares.
Obviously I also want to combine the two, in order to find how many
shares each individual owns. This is kept in tblNumberOfShares.
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 tblNumberOfShares, 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 <un**@together.com> wrote in message
news:<3F**************@together.com>...
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?


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

P: n/a
Sten Koll wrote:
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 tblNewIssuesShares.
Obviously I also want to combine the two, in order to find how many
shares each individual owns.
You don't want to combine tblOwer with tblNewIssueShares. You want to
combine tblOwner with TblNumber_of_shares.
This is kept in tblNumberOfShares.
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 tblNumberOfShares, 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?


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_Shares 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....nothing 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.
Nov 12 '05 #12

P: n/a
Sten Koll wrote:
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 tblNewIssuesShares.
Obviously I also want to combine the two, in order to find how many
shares each individual owns.
You don't want to combine tblOwer with tblNewIssueShares. You want to
combine tblOwner with TblNumber_of_shares.
This is kept in tblNumberOfShares.
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 tblNumberOfShares, 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?


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_Shares 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....nothing 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.


Nov 12 '05 #13

P: n/a
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 <un**@together.com> wrote in message news:<3F***************@together.com>...
Sten Koll wrote:
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 tblNewIssuesShares.
Obviously I also want to combine the two, in order to find how many
shares each individual owns.


You don't want to combine tblOwer with tblNewIssueShares. You want to
combine tblOwner with TblNumber_of_shares.
This is kept in tblNumberOfShares.
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 tblNumberOfShares, 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?


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_Shares 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....nothing 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.

Nov 12 '05 #14

P: n/a
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 <un**@together.com> wrote in message news:<3F***************@together.com>...
Sten Koll wrote:
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 tblNewIssuesShares.
Obviously I also want to combine the two, in order to find how many
shares each individual owns.


You don't want to combine tblOwer with tblNewIssueShares. You want to
combine tblOwner with TblNumber_of_shares.
This is kept in tblNumberOfShares.
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 tblNumberOfShares, 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?


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_Shares 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....nothing 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.

Nov 12 '05 #15

P: n/a
I am about to tear my hair out, because I can't find a way to do this,
and then i tried to include the following in a reply here, and it
showed up as a new post - twice...

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

Patrick Finucane <pa*************@mindspring.com> wrote in message news:<3F***************@mindspring.com>...
Sten Koll wrote:
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 tblNewIssuesShares.
Obviously I also want to combine the two, in order to find how many
shares each individual owns.


You don't want to combine tblOwer with tblNewIssueShares. You want to
combine tblOwner with TblNumber_of_shares.
This is kept in tblNumberOfShares.
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 tblNumberOfShares, 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?


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_Shares 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....nothing 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.

Nov 12 '05 #16

P: n/a
Sten

I just emailed you an mdb that will give you ideas on how to approach this
let me know if you need more help
U N Me <un**@together.com> wrote in message news:<3F***************@together.com>...
Sten Koll wrote:
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 tblNewIssuesShares.
Obviously I also want to combine the two, in order to find how many
shares each individual owns.


You don't want to combine tblOwer with tblNewIssueShares. You want to
combine tblOwner with TblNumber_of_shares.
This is kept in tblNumberOfShares.
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 tblNumberOfShares, 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?


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_Shares 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....nothing 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.

Nov 12 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.