Beginner question

[StenKoll]

Hi! I am fairly new to access and not very familiar with vba
programming. I am trying to setting up a database of shareholders in a
company. So far I have managed to build tables containing owner data,
data for the shares and a form where I register how many shares each
owner owns.

Now to the difficult(?) issue. I need to set a number for the shares
so I can make an ownership report showing wich sharenumbers people
owns. I have thougt about making a new table where I include a sorted
column for the Id for new issues of shares and a column number of
stocks for each owner in each new issue. Then I plan to have a
seperate column summing up the number of stocks in ownership column,
and finally a column which gives me a from - to value, symbolising the
unique sharenumbers for each owner in each new issue of shares..

It seems pretty straight forward, but I feel stuck. Can anyone please
help me to get on?

Thanks in advance!

[TC]

"StenKoll" <re****@asly.net> wrote in message
news:6d**************************@posting.google.c om...

Hi! I am fairly new to access and not very familiar with vba
programming. I am trying to setting up a database of shareholders in a
company. So far I have managed to build tables containing owner data,
data for the shares and a form where I register how many shares each
owner owns.

Now to the difficult(?) issue. I need to set a number for the shares
so I can make an ownership report showing wich sharenumbers people
owns. I have thougt about making a new table where I include a sorted
column for the Id for new issues of shares and a column number of
stocks for each owner in each new issue. Then I plan to have a
seperate column summing up the number of stocks in ownership column,
and finally a column which gives me a from - to value, symbolising the
unique sharenumbers for each owner in each new issue of shares..

It seems pretty straight forward, but I feel stuck. Can anyone please
help me to get on?

Thanks in advance!

[TC]

You will get better help if you show the desgn of your tables, including (in
particular) the primary key of each table.

Here is an example format:

tblShareholder
SharholderID (primary key)
Name, address etc.

I'm not being picky! It is difficult to make any sensible comments about a
table design problem, unless we know >what< tables you are trying to use;
and in particular, what you say is the primary key of each table.

HTH,
TC
"StenKoll" <re****@asly.net> wrote in message
news:6d**************************@posting.google.c om...

Hi! I am fairly new to access and not very familiar with vba
programming. I am trying to setting up a database of shareholders in a
company. So far I have managed to build tables containing owner data,
data for the shares and a form where I register how many shares each
owner owns.

Now to the difficult(?) issue. I need to set a number for the shares
so I can make an ownership report showing wich sharenumbers people
owns. I have thougt about making a new table where I include a sorted
column for the Id for new issues of shares and a column number of
stocks for each owner in each new issue. Then I plan to have a
seperate column summing up the number of stocks in ownership column,
and finally a column which gives me a from - to value, symbolising the
unique sharenumbers for each owner in each new issue of shares..

It seems pretty straight forward, but I feel stuck. Can anyone please
help me to get on?

Thanks in advance!

[StenKoll]

You're right! You're not being picky and I will take a deep breath and
rephrase myself….. :-)

Here we go:
tbl Name:
Name_ID (Primary key)
Firstname
Lastname

tbl New_Issues_Shares
Issue_ID (Primary key)
Date
Number_of_shares (for each new issue)

tbl Number_of_shares
Transaction_Counter
Name_ID
Issue_ID
Number_of_Shares

Through a query I have been able to have a running sum of all shares
listed in order by using a transactioncounter. My problem now is how
to number the individual shares so that transaction 1 shows shares
from 1-500, transaction 2 from 500 to 600, transaction 3 from 600 to
800 and so on.
The reason why I want it this way is to be able to issue individual
certificates showing which shares each individual owns.

After having re-read my previous post I realize that it was hard to
help by the information given, but I have made it easier for people to
help me now…

Thanks,
StenKoll

"TC" <a@b.c.d> wrote in message news:<1070078342.982907@teuthos>...

You will get better help if you show the desgn of your tables, including (in
particular) the primary key of each table.

Here is an example format:

tblShareholder
SharholderID (primary key)
Name, address etc.

I'm not being picky! It is difficult to make any sensible comments about a
table design problem, unless we know >what< tables you are trying to use;
and in particular, what you say is the primary key of each table.

HTH,
TC
"StenKoll" <re****@nospamasly.net> wrote in message
news:6d**************************@posting.google.c om...

Hi! I am fairly new to access and not very familiar with vba
programming. I am trying to setting up a database of shareholders in a
company. So far I have managed to build tables containing owner data,
data for the shares and a form where I register how many shares each
owner owns.

Now to the difficult(?) issue. I need to set a number for the shares
so I can make an ownership report showing wich sharenumbers people
owns. I have thougt about making a new table where I include a sorted
column for the Id for new issues of shares and a column number of
stocks for each owner in each new issue. Then I plan to have a
seperate column summing up the number of stocks in ownership column,
and finally a column which gives me a from - to value, symbolising the
unique sharenumbers for each owner in each new issue of shares..

It seems pretty straight forward, but I feel stuck. Can anyone please
help me to get on?

Thanks in advance!

[TC]

Ok. let's take an example.

- There is a person Fred Smith with Name_ID 111.
- There is a new issue of 10,000 shares on 1/1/2004 with Issue_ID 222.
- Fred Smith takes 3,000 of those shares in transaction number 444.

So in tblNumber_of_shares you have the following. (I've abbreviated the
names to ensure that they stay on one line):
Transaction Name_ID Issue_ID no_shares
444 111 222 3,000

And of course, that person might have taken other shares in different
transactions:

Transaction Name_ID Issue_ID no_shares
444 111 222 3,000
447 111 228 4,000
451 111 250 1,500

Correct?

Now I am not quite sure what you mean by: "how to number the individual
shares so that transaction 1 shows shares from 1-500, transaction 2 from 500
to 600, transaction 3 from 600 to 800 and so on." Your table structure does
not allow for numbering of individual shares (as far as I can see). And I
don't see what you mean by "shares 1-500". In the above example, each
transaction (say 444) shows a >total number< of shares (3,000). There is no
sense in which those shares are individually numbered, is there?

perhaps explain your problem using the actual example above.

Cheers,
TC
"StenKoll" <re****@asly.net> wrote in message
news:6d**************************@posting.google.c om...

You're right! You're not being picky and I will take a deep breath and
rephrase myself... :-)

Here we go:
tbl Name:
Name_ID (Primary key)
Firstname
Lastname

tbl New_Issues_Shares
Issue_ID (Primary key)
Date
Number_of_shares (for each new issue)

tbl Number_of_shares
Transaction_Counter
Name_ID
Issue_ID
Number_of_Shares

Through a query I have been able to have a running sum of all shares
listed in order by using a transactioncounter. My problem now is how
to number the individual shares so that transaction 1 shows shares
from 1-500, transaction 2 from 500 to 600, transaction 3 from 600 to
800 and so on.
The reason why I want it this way is to be able to issue individual
certificates showing which shares each individual owns.

After having re-read my previous post I realize that it was hard to
help by the information given, but I have made it easier for people to
help me now.

Thanks,
StenKoll

"TC" <a@b.c.d> wrote in message news:<1070078342.982907@teuthos>...

You will get better help if you show the desgn of your tables, including (in particular) the primary key of each table.

Here is an example format:

tblShareholder
SharholderID (primary key)
Name, address etc.

I'm not being picky! It is difficult to make any sensible comments about a table design problem, unless we know >what< tables you are trying to use; and in particular, what you say is the primary key of each table.

HTH,
TC
"StenKoll" <re****@nospamasly.net> wrote in message
news:6d**************************@posting.google.c om...

Hi! I am fairly new to access and not very familiar with vba
programming. I am trying to setting up a database of shareholders in a
company. So far I have managed to build tables containing owner data,
data for the shares and a form where I register how many shares each
owner owns.

Now to the difficult(?) issue. I need to set a number for the shares
so I can make an ownership report showing wich sharenumbers people
owns. I have thougt about making a new table where I include a sorted
column for the Id for new issues of shares and a column number of
stocks for each owner in each new issue. Then I plan to have a
seperate column summing up the number of stocks in ownership column,
and finally a column which gives me a from - to value, symbolising the
unique sharenumbers for each owner in each new issue of shares..

It seems pretty straight forward, but I feel stuck. Can anyone please
help me to get on?

Thanks in advance!

[StenKoll]

Thank you for trying to understand TC, but actually there is a reason
why I want to find the shares' individual numbers. According to local
(Swedish-) law, a company is to issue certifictes for the shares
purchased in a company such as the one I am managing. The certificate
shall include the totalnumber of shares as well as their individual
numbers.

Therefore, Fred Smiths 3,000 shares in transaction number 444 might be
the shares with numbers from 100,001 to 103,000.

So when transaction 443 shows a total of shares being 100,000 and
transaction 445 shows total of shares being 103,001, I want a table
saying that Fred Smith owns stocks in the interval 100,001 to 103,000.
Is there a way this can be done, or is manual input of sharenumbers
the only alternative?
Is there a way I can get my results in two columns giving From_Share
To_share?

Thanks again TC!

StenKoll

"TC" <a@b.c.d> wrote in message news:<1070255091.386049@teuthos>...
Now I am not quite sure what you mean by: "how to number the individual
shares so that transaction 1 shows shares from 1-500, transaction 2 from 500
to 600, transaction 3 from 600 to 800 and so on." Your table structure does
not allow for numbering of individual shares (as far as I can see). And I
don't see what you mean by "shares 1-500". In the above example, each
transaction (say 444) shows a >total number< of shares (3,000). There is no
sense in which those shares are individually numbered, is there?

perhaps explain your problem using the actual example above.

Cheers,
TC

[TC]

Sten, let's keep all the example details in each post. Otherwise I have to
search back for them on my newsserver. I have re-inserted them, so I can
refer to them here. Just add your reply to the top of this text (but retain
all the existing text).

If the shares must be individually numbered, you need to answer the
following question: Are all the shares in an issue (say issue 222), >always<
numbered in >strict sequence< (whatever, whatever + 1, whatever + 2, & so
on)? Or is there any case where the shares in an issue might not be numbered
in strict sequence?

If they are always numbered in strict sequence (and you can rely on that
being true, and remaining true, forever), I would do this:

tbl New_Issues_Shares
Issue_ID (Primary key)
Date
Number_of_shares << DELETE
Start_Number << ADD
End_Number << ADD

Now you know the start & end share number for each issue. You need not save
the number of shares in the issue, because you can calculate that from
End_Number - Start_Number, whenever you need to.

Now you need to know >which< shares are allocated to each person. Again,
even if the shares >in the issue< were sequentially numbered, we need to
know whether the shares >from that issue< that are allocated in a
transaction, >are also sequential<. I'll assume they >are< sequential. (For
example, if an issue had shares 500, 501, 502, 503 ... 1000, and a person
was issued just 3 shares, he might be given 505, 506 & 507, but he would
never be given non-sequential numbers like 505, 507 & 508). On that
assumption:

tbl Number_of_shares
Transaction_Counter
Name_ID
Issue_ID
Number_of_Shares << DELETE
Start_Number << ADD
End_Number << ADD

Now you know which shares were allocated in each transaction.

Then, the following query weould list >all< shares that are currently held
by each person:

SELECT n.*,
s.Start_Number,
s.End_Number,
s.Transaction_Counter,
s.Issue_ID
FROM tblName AS n,
tblNumber_of_shares AS s
WHERE s.Name_ID = n.Name_ID
ORDER BY n.Name_ID,
s.Start_Number

Does that help?

TC
"StenKoll" <re****@asly.net> wrote in message
news:6d**************************@posting.google.c om...

Thank you for trying to understand TC, but actually there is a reason
why I want to find the shares' individual numbers. According to local
(Swedish-) law, a company is to issue certifictes for the shares
purchased in a company such as the one I am managing. The certificate
shall include the totalnumber of shares as well as their individual
numbers.

Therefore, Fred Smiths 3,000 shares in transaction number 444 might be
the shares with numbers from 100,001 to 103,000.

So when transaction 443 shows a total of shares being 100,000 and
transaction 445 shows total of shares being 103,001, I want a table
saying that Fred Smith owns stocks in the interval 100,001 to 103,000.
Is there a way this can be done, or is manual input of sharenumbers
the only alternative?
Is there a way I can get my results in two columns giving From_Share
To_share?

Thanks again TC!

StenKoll

"TC" <a@b.c.d> wrote in message news:<1070255091.386049@teuthos>...
== added excised example details ==

Ok. let's take an example.

- There is a person Fred Smith with Name_ID 111.
- There is a new issue of 10,000 shares on 1/1/2004 with Issue_ID 222.
- Fred Smith takes 3,000 of those shares in transaction number 444.

So in tblNumber_of_shares you have the following. (I've abbreviated the
names to ensure that they stay on one line):
Transaction Name_ID Issue_ID no_shares
444 111 222 3,000

And of course, that person might have taken other shares in different
transactions:

Transaction Name_ID Issue_ID no_shares
444 111 222 3,000
447 111 228 4,000
451 111 250 1,500

Correct?
======
Now I am not quite sure what you mean by: "how to number the individual
shares so that transaction 1 shows shares from 1-500, transaction 2 from 500 to 600, transaction 3 from 600 to 800 and so on." Your table structure does not allow for numbering of individual shares (as far as I can see). And I don't see what you mean by "shares 1-500". In the above example, each
transaction (say 444) shows a >total number< of shares (3,000). There is no sense in which those shares are individually numbered, is there?

perhaps explain your problem using the actual example above.

Cheers,
TC

== added more excised stuff ===
"StenKoll" <re****@asly.net> wrote in message
news:6d**************************@posting.google.c om... You're right! You're not being picky and I will take a deep breath and
rephrase myself... :-)

Here we go:
tbl Name:
Name_ID (Primary key)
Firstname
Lastname

tbl New_Issues_Shares
Issue_ID (Primary key)
Date
Number_of_shares (for each new issue)

tbl Number_of_shares
Transaction_Counter
Name_ID
Issue_ID
Number_of_Shares

Through a query I have been able to have a running sum of all shares
listed in order by using a transactioncounter. My problem now is how
to number the individual shares so that transaction 1 shows shares
from 1-500, transaction 2 from 500 to 600, transaction 3 from 600 to
800 and so on.
The reason why I want it this way is to be able to issue individual
certificates showing which shares each individual owns.

After having re-read my previous post I realize that it was hard to
help by the information given, but I have made it easier for people to
help me now.

Thanks,
StenKoll

"TC" <a@b.c.d> wrote in message news:<1070078342.982907@teuthos>...

You will get better help if you show the desgn of your tables, including

(in particular) the primary key of each table.

Here is an example format:

tblShareholder
SharholderID (primary key)
Name, address etc.

I'm not being picky! It is difficult to make any sensible comments about a table design problem, unless we know >what< tables you are trying to use; and in particular, what you say is the primary key of each table.

HTH,
TC
"StenKoll" <re****@nospamasly.net> wrote in message
news:6d**************************@posting.google.c om...

Hi! I am fairly new to access and not very familiar with vba
programming. I am trying to setting up a database of shareholders in a
company. So far I have managed to build tables containing owner data,
data for the shares and a form where I register how many shares each
owner owns.

Now to the difficult(?) issue. I need to set a number for the shares
so I can make an ownership report showing wich sharenumbers people
owns. I have thougt about making a new table where I include a sorted
column for the Id for new issues of shares and a column number of
stocks for each owner in each new issue. Then I plan to have a
seperate column summing up the number of stocks in ownership column,
and finally a column which gives me a from - to value, symbolising the
unique sharenumbers for each owner in each new issue of shares..

It seems pretty straight forward, but I feel stuck. Can anyone please
help me to get on?

Thanks in advance!

[Sten Koll]

It is starting to look like the sollution I had a vague picture of in
the back of my head! ;-)

Since I am really a novice, would you care to explain in detail about
the << ADD and << DELETE you have mentioned in your latest response?

you are right in assuming that the shares are issued in strict order. as
for now, there are no one trading the shares, so I just have to keep
track of who has which shares.

Thanks again for helping me on track.

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

[Sten Koll]

Hmmm- strange... I cannot see my own reply.
Well I'll try again. It looks like it is starting to make sense. You are
right in your assumption about the shares being in sequence, both in the
tblNew_Issue_Shares as well as tblNumber_of_shares.

What I don't understand now is what you mean by << ADD and << DELETE...
I'm a complete novice remember, so please be patient and take it s l o w
l y ;-)

Thanks for your time TC

Sten Koll

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

[cafe]

I was suggesting to modify your table structures by adding the fields marked
<< ADD, and deleting the fields marked << DELETE :-)

Sten, I won't be able to help you any more, unless you start retaining the
text of the previous posts. Just hit the Reply To button on your newsreader,
and >add< your reply to the existing text of the previous posts. >Do not
delete< the text of the previous posts. That makes it just too hard to
follow the thread of conversation.

Cheers,
TC
"Sten Koll" <re****@asly.net> wrote in message
news:3f***********************@news.frii.net...

It is starting to look like the sollution I had a vague picture of in
the back of my head! ;-)

Since I am really a novice, would you care to explain in detail about
the << ADD and << DELETE you have mentioned in your latest response?

you are right in assuming that the shares are issued in strict order. as
for now, there are no one trading the shares, so I just have to keep
track of who has which shares.

Thanks again for helping me on track.

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

[Sten Koll]

OK.. I'll try again. I didn't delete anything thoug, but something
obviously happened when I replied at www.developersdex.com I have copied
it all below, so let's give it yet another shot! ;-)

It is starting to look like the sollution I had a vague picture of in
the back of my head! ;-)

Since I am really a novice, would you care to explain in detail about
the << ADD and << DELETE you have mentioned in your latest response?

you are right in assuming that the shares are issued in strict order. as
for now, there are no one trading the shares, so I just have to keep
track of who has which shares.

Thanks again for helping me on track.
From: TC
Date Posted: 12/1/2003 7:03:00 PM

Sten, let's keep all the example details in each post. Otherwise I have
to
search back for them on my newsserver. I have re-inserted them, so I can
refer to them here. Just add your reply to the top of this text (but
retain
all the existing text).

If the shares must be individually numbered, you need to answer the
following question: Are all the shares in an issue (say issue 222),

always< numbered in >strict sequence< (whatever, whatever + 1, whatever + 2, &
so
on)? Or is there any case where the shares in an issue might not be
numbered
in strict sequence?

If they are always numbered in strict sequence (and you can rely on that
being true, and remaining true, forever), I would do this:

tbl New_Issues_Shares
Issue_ID (Primary key)
Date
Number_of_shares << DELETE
Start_Number << ADD
End_Number << ADD

Now you know the start & end share number for each issue. You need not
save
the number of shares in the issue, because you can calculate that from
End_Number - Start_Number, whenever you need to.

Now you need to know >which< shares are allocated to each person. Again,
even if the shares >in the issue< were sequentially numbered, we need to
know whether the shares >from that issue< that are allocated in a
transaction, >are also sequential<. I'll assume they >are< sequential.
(For
example, if an issue had shares 500, 501, 502, 503 ... 1000, and a
person
was issued just 3 shares, he might be given 505, 506 & 507, but he would
never be given non-sequential numbers like 505, 507 & 508). On that
assumption:

tbl Number_of_shares
Transaction_Counter
Name_ID
Issue_ID
Number_of_Shares << DELETE
Start_Number << ADD
End_Number << ADD

Now you know which shares were allocated in each transaction.

Then, the following query weould list >all< shares that are currently
held
by each person:

SELECT n.*,
s.Start_Number,
s.End_Number,
s.Transaction_Counter,
s.Issue_ID
FROM tblName AS n,
tblNumber_of_shares AS s
WHERE s.Name_ID = n.Name_ID
ORDER BY n.Name_ID,
s.Start_Number

Does that help?

TC
"StenKoll" <re****@asly.net> wrote in message
news:6d**************************@posting.google.c om... Thank you for trying to understand TC, but actually there is a reason
why I want to find the shares' individual numbers. According to local
(Swedish-) law, a company is to issue certifictes for the shares
purchased in a company such as the one I am managing. The certificate
shall include the totalnumber of shares as well as their individual
numbers.

Therefore, Fred Smiths 3,000 shares in transaction number 444 might be
the shares with numbers from 100,001 to 103,000.

So when transaction 443 shows a total of shares being 100,000 and
transaction 445 shows total of shares being 103,001, I want a table
saying that Fred Smith owns stocks in the interval 100,001 to 103,000.
Is there a way this can be done, or is manual input of sharenumbers
the only alternative?
Is there a way I can get my results in two columns giving From_Share
To_share?

Thanks again TC!

StenKoll

"TC" <a@b.c.d> wrote in message news:<1070255091.386049@teuthos>...
== added excised example details ==

Ok. let's take an example.

- There is a person Fred Smith with Name_ID 111.
- There is a new issue of 10,000 shares on 1/1/2004 with Issue_ID 222.
- Fred Smith takes 3,000 of those shares in transaction number 444.

So in tblNumber_of_shares you have the following. (I've abbreviated the names to ensure that they stay on one line):
Transaction Name_ID Issue_ID no_shares
444 111 222 3,000

And of course, that person might have taken other shares in different
transactions:

Transaction Name_ID Issue_ID no_shares
444 111 222 3,000
447 111 228 4,000
451 111 250 1,500

Correct?
======
Now I am not quite sure what you mean by: "how to number the individual shares so that transaction 1 shows shares from 1-500, transaction 2 from
500 to 600, transaction 3 from 600 to 800 and so on." Your table structure
does not allow for numbering of individual shares (as far as I can see). And
I don't see what you mean by "shares 1-500". In the above example, each transaction (say 444) shows a >total number< of shares (3,000). There is
no sense in which those shares are individually numbered, is there?

perhaps explain your problem using the actual example above.

Cheers,
TC

== added more excised stuff ===
"StenKoll" <re****@asly.net> wrote in message
news:6d**************************@posting.google.c om... You're right! You're not being picky and I will take a deep breath and
rephrase myself... :-)

Here we go:
tbl Name:
Name_ID (Primary key)
Firstname
Lastname

tbl New_Issues_Shares
Issue_ID (Primary key)
Date
Number_of_shares (for each new issue)

tbl Number_of_shares
Transaction_Counter
Name_ID
Issue_ID
Number_of_Shares

Through a query I have been able to have a running sum of all shares
listed in order by using a transactioncounter. My problem now is how
to number the individual shares so that transaction 1 shows shares
from 1-500, transaction 2 from 500 to 600, transaction 3 from 600 to
800 and so on.
The reason why I want it this way is to be able to issue individual
certificates showing which shares each individual owns.

After having re-read my previous post I realize that it was hard to
help by the information given, but I have made it easier for people to
help me now.

Thanks,
StenKoll

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