Mutiple Table Query

[Guest]

I have created three tables, Shares_Granted, Shares_Exercised, and
Shares_Cancelled. The first table has 1 unique record for each stock option
grant (Num) indicating the total number of options for that particular grant.
Each of the second two tables has multiple records for each stock option
grant (Num), based on the date and the number of shares from the grant that
were either exercised or cancelled.

I am trying to create a query showing by Num the following: Shares_Granted,
total Shares_Exercised (by Num), and total Shares_Cancelled (by Num). For
each unique stock option grant (Num) I will then calculate total Shares
Outstanding (by Num). No matter how I try this I get mutiples of the
numbers in all three fields for each Num.

Thanks for any help you can provide me.

 

[Jeff Boyce]

Stephen

Your description mentions "total number of options" in your first table, and
"number of shares" in the second two. Are these supposed to be the same
thing? How are these related?

You have one table to hold "Shares_Exercised", and another table to hold
"Shares_Cancelled". This seems like how you'd need to design a spreadsheet,
but Access is a relational database. Don't use table names to categorize
your data.

Instead, use a single table and add a field for "ActionTaken" (e.g.,
"Exercised", "Cancelled").

I can't tell from your description whether the same consolidation would be
appropriate for "Granted" shares, too.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

Sorry for the confusion. Each of the three tables have multiple fields.
The first table lists, by option grant number (Num), employee, date of grant,
the total number of shares granted, exercise price, and the vesting
medothology. The option grant number (Num) has been designated as key. This
number tracks all activity under a particular stock option grant. In this
table there is therefore only one record per grant number (Num).

The second table tracks, by option grant number(Num) the date and number of
shares exercised by employee. Therefore employee A may exercise 10,000
options on Date1, 5,000 options on Date2...all for the same option grant
number (Num). In this file there are multiple records for each Num, one for
each date on which stock options were exercised for a particular grant. The
third file is simlar to the 2nd, but for dates on which a certain number of
shares, under an option grant (num) may expire.

What I am utimately looking for is a report/query that can be run at a point
in time that will show me for each option grant (Num), the number of shares
initially granted, the total number of shares exercised or cancelled and the
number of shares remaining outstanding. I can get the report to one line per
option grant, but in doing so it multiplies (not adds) the table values in
one table , by the number of records in one or the other of the exercises or
cancelled tables.

For example, if there was a grant for 1000 shares, 1 exercise for 200 shares
and 2 cancellations for 100 each, my report shows; 2000 shares granted(2x),
400 shares exercised(2x) and 200 cancelled (correct). What I want it to
produce is ; 1000 granted, 200 exercised and 200 cancelled with 600 remaining
outstanding. No matter how I structure my query I can't get it to sum each
tables values correctly by option grant number (Num) with 1 line per each Num.

Thanks again

Stephen