Re: Joins and loading from Excel

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks John for your patience I am a new kid on the block.

I have a pdf screenshot of the relations and it is worth a 1000 words.
I actually assigned a separate ID to the investor for a Pkey
The Broker has a Pkey called AgentID
I created an autonumber Pkey called TransactionID for the FundPurchase
table just to be prudent. There is no transaction date for us. We have
closings each quarter, but we close the fund at the end of the tax
year. So a fund is 2006FTS for example. All the shares are the same
price so we only worry about units, fund and investor#

So this is how it works ...

A Broker has many Investors
An Investor has purchased many units of many funds

The table in the middle (fundpurchase) is joined to Brokers by the
AgentID
and Joined to the Investors by the InvestorID

They have always typed the data into a form in the past. So of course
it goes in linked through record autonumbering.

The company has grown so fast that now it is ludicrous to retype all
the stuff (3,000+) rows from the Excel sheets.

I can get the excel data (I put it in a temporary table in the
database) to populate the FundPurchase table and put the corresponding
InvesterID in the Fundpurchase table but I cannot get it to populate
the AgentId field.

I can't see a way except to compair the agent's firstname lastname to
those in the temp table. Except Access won't let me turn them into a
key or join them. And it's a very unreliable query anyway.

Do you think I'm Hooped on this one?

What is the structure of your spreadsheet? The proper Access structure
would be:

Investors
InvestorSIN <Primary Key>
LastName
FirstName
<other bio information>

Brokers
AgentID <primary key>
<broker bio info>

FundPurchases
InvestorID <primary key>
TransactionDate <primary key>
Fund <primary key>
AgentID
<other info about this purchase, e.g. price>

That's a three-field joint primary key; each transaction must be a new
record in the table.


John W. Vinson[MVP]

.

Relevant Pages

  • Re: Joins and loading from Excel
    ... I actually assigned a separate ID to the investor for a Pkey ... I created an autonumber Pkey called TransactionID for the FundPurchase ... So a fund is 2006FTS for example. ... InvestorSIN <Primary Key> ...
    (microsoft.public.access.forms)
  • Re: Joins and loading from Excel
    ... A Broker sells to many investors who can have many transactions. ... Investor usually has his/her own broker. ... InvestorSIN <Primary Key> ... That's a three-field joint primary key; each transaction must be a new ...
    (microsoft.public.access.forms)
  • Peloton Partners in $2bn assets sale
    ... "It is the classic story of when leverage goes wrong," one investor ... Peloton Partners put the assets of its $2bn flagship fund up for sale ... year after betting against low-quality mortgages. ...
    (misc.invest.stocks)
  • Re: Way OT: the mutual fund and stock market scam
    ... >On the mutual fund /stock market scam. ... the objectives of a fund may not match the objectives of the investor. ... That works well for the investor in an up market but can hurt ya bad ...
    (rec.crafts.metalworking)
  • Re: Tax Cuts?
    ... managers make from the profits of the fund really are capital gains. ... What you're advocating is that the investor and the fund ...
    (rec.sport.golf)