 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / August 2006Joins and loading from Excel
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. [quoted text clipped - 19 lines] > >This is my first post ... so please be patient with me. 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? > What is the structure of your spreadsheet? The proper Access structure > would be: [quoted text clipped - 20 lines] > > 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? > What is the structure of your spreadsheet? The proper Access structure > would be: [quoted text clipped - 20 lines] > > John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.