Amalgamating linked tables

[Charles Naylor]

Hi,

I have a series of portfolios, each of which contains a
different, overlapping set of stocks. I also have
separate excel files which contain portfolio-specific
information on each of these stocks.

My database is currently set up with information on the
stocks, not the portfolios, at the forefront. I then
have a table to keep track of which stocks are in which
portfolio (call it "portfolio&stock").

What's my best choice to add data (eg. current weighting)
from separate excel files into the "portfolio&stock"
table? Changing the excel files' structure is not an
option.

I was thinking maybe I can set up the field I want
in "portfolio&stock", then write a macro to populate it
from the correct table. Is this the only/best way to do
it?

Thanks,
CN

 

[Jeff Boyce]

Charles

Are you saying that the "weighting" factor you wish to update is a
characteristic of an entire portfolio, a single stock, or the placement of a
particular stock in a particular portfolio? Where you "update" depends on
your response.

Do you have a mechanism (i.e., an ID#) in Excel that you could use to "find"
the correct row to update in your database? If so, you could simply add a
link to the Excel datasource, and use an update query to modify the
appropriate rows.

Good luck!

Jeff Boyce
<Access MVP>

 

[Charles Naylor]

Thanks for your response!

The weighting factor is a characteristic of individual
stocks within individual portfolios. Eg:

Portfolio A Weight Portfolio B Weight Portfolio C W
Stock 1 33% Stock 2 10% Stock 1 33%
Stock 3 40% Stock 3 33% stock 2 50%
Stock 4 27% Stock 5 57% Stock 4 17%

So, different portofolios can be made up of different
selections of stocks. Most of the information is stored
with the stocks forming the primary key ("Equity Name"
field).

The correct row in the table I'm looking for is
determined by two fields: portfolio and stock-- for the
example above, this table would contain entries
like "Portfolio A ; Stock 1" "Portfolio B ; Stock 2"
(with ';' delimiting different fields).

What I'm hoping to do is add an additional weighting
field to this table, then populate the weights based on a
separate linked table for each portfolio. (as the
underlying excel files are separate). Thus, the linked
tables themselves contain the information about which
portfolio they refer to, and the data inside them
denotes 'Stock Name' and 'Weight'.

I could find the correct row given portfolio and fund
name, but can I then have multiple updating queries, one
for each linked table, all updating parts of the master
table?

Thanks again for your help,
CN

 

[Jeff Boyce]

Charles

Without more detail about how the data is structured, both in your Excel
source and your Access mdb, it'll be tough offering specific suggestions...

Perhaps some of the other newsgroup readers can see a solution here.

Jeff Boyce
<Access MVP>