Joins and loading from Excel

  • Thread starter Thread starter CanFlightSim
  • Start date Start date
C

CanFlightSim

I have a database project where there are exiting Brokers,
FundPurchases and Investors in three tables.

A Broker sells to many investors who can have many transactions. Each
Investor usually has his/her own broker.

The keys are AgentID, PurchaseID,InvestorID

The exiting data is all linked because they typed it into forms with
the joins in place.

Now things have grown and they get the data a few thousand records at a
time on Excel spreadsheets.

The new data for all three tables ends up on one Escel sheet by the
time it is broken up and cleaned.The only unique item in each record is
the investor's Social Insurance number. SIN. With that, I am able to
populate the Investor table and link it to fundPurchases. An investor
can purchase many funds. But I can't figure a way to populate the
existing Brokers table and have each broker link to each transaction
and thereby each investor.

This is my first post ... so please be patient with me.

Thnks
Will
 
I have a database project where there are exiting Brokers,
FundPurchases and Investors in three tables.

A Broker sells to many investors who can have many transactions. Each
Investor usually has his/her own broker.

The keys are AgentID, PurchaseID,InvestorID

The exiting data is all linked because they typed it into forms with
the joins in place.

Now things have grown and they get the data a few thousand records at a
time on Excel spreadsheets.

The new data for all three tables ends up on one Escel sheet by the
time it is broken up and cleaned.The only unique item in each record is
the investor's Social Insurance number. SIN. With that, I am able to
populate the Investor table and link it to fundPurchases. An investor
can purchase many funds. But I can't figure a way to populate the
existing Brokers table and have each broker link to each transaction
and thereby each investor.

This is my first post ... so please be patient with me.
Click to expand...

If the investor's SIN is unique in the table, then they can - by
definition - have only one record in the table, so they can make only
one purchase!!

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]
 
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?
 
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?
 
Back
Top