A basic Access Database Structure question

[Travstar]

Hi Guys,

Sorry for such a simple question; Im new to access. Here is the setup:

I am currently working on a basic database to store client details and their current funds for a financial advisor.

Proposed Table Structure:

tblDetails:
Given_Name
Surname
Address
DOB
Phone_Number
Customer_Type <-- combo box linked to tblCust_type
Lodgement_Date
Date_Closed

tblLodgement:
fund_name <-- Combo box linked to tblfund
Amount <-- ($)
Trail <-- ($)

tblfund:
fund_name <-- list of 20 or so fund names to choose

tblCust_type:
Customer_type: <-- Either A, B or C

Ok, here is my problem. I was originally told that every customer could only choose ONE fund name (and associated amount and Trail). Because of this I (stupidly?) originally combined the tblLodgement and tblDetails into one big table. This worked just fine. I am now told that one person can have as many Fund_name(s) (and associated Amount and Trails) as they wish. This means now i will have to set up something similar to the above table structure.

Having little clue about the relationships required, i was just wondering what relationships will have to be set up for this to now work correctly. Secondly, will I have any trouble modifying my existing form which simply has the original tbldata (tblDetails and tblLodgement combined)?

Thanks in advance for any advice on this guys

Travstar

 

[THWatson]

Travstar
Basically, you need a method to make each client record unique. This would become the Primary Key in tblDetails, and would be the Foreign Key in tblLodgement. The join would be one-to-many, so that each client in tblDetails could have many transactions in tblLodgement.

As for how much you will have to modify your form, I would imagine that you might set things up with a main form for clients and a subform for transactions. But once you have your tables properly built, that would fall into place.

Tom

 

[Travstar]

Thanks for your reply.

ok, so now i have added an 'ID' field (autonumber) to tblDetails and to tblLodgement and made both the primary keys (should i have done this?). The problem i have is when i try to link them up in a one-to-many relationship, i can only make it a one-to-one.

Is this because i have made them both primary keys?

Secondly, if I make a second form for the tblLodgement, how do i set it up so on the main form, all the user does is click a command button while on a particular record and only sees the Lodgement data relating to the current record? would this be the ideal way to set it up?


Thanks in advance.

 

[THWatson]

Travstar
It's okay to have an ID field in each of the tables...to identify each individual record in those tables. But you don't make the relationship join on those two fields; you need to go a touch further. You need to make the ID primary key field in tblDetails a foreign key in tblLodgement.

Thus, if Client John Smith in tblDetails has an ID number of 10, then any transactions he has in tblLodgement will have 10 in the foreign key field.

I don't know the name of the fields, so can only use an example. Say that your ID field in tblDetails is called ClientID. You put that exact same field in tblLodgement but make it a Number field (not AutoNumber) and then make the one-to-many join on those two fields. Also set Referential Integrity so that you can never have record in tblLodgement that does not have a corresponding record in tblDetails.

I'm not sure how many records you already have in the two tables. If you have only a few in tblLodgement, you might be best to start over in that table once you have set up the Foreign Key. If you have quite a number of records in tblLodgment, you need to have some way to update the Foreign Key in tblLodgement with the appropriate Primary Key from tblDetails...maybe using an Update query.

Regarding your second question: When you have made the form from tblLodgement, set it up as a subform on the form you made from tblDetails...BUT you will have to get the two tables fixed up first.

Tom

 

[willir]

Travstar

First, you have done a dynamite job so far.

Are you just going to manually enter the total fund amount? And manually maintain the balances.

You should have a price table. You can easily review performance of each fund, and calculate values if and when requried. Does not have to be too detailed, perhaps monthly or weekly.

tblClient
ClientID - primary key
Given_Name
Surname
Address
DOB
Phone_Number
Customer_Type <-- combo box linked to tblCust_type
Lodgement_Date
Date_Closed

tblFund
FundCode - primary key
fund_name
FundType - equity, divident, bond...
RiskLevel

tblCust_type
Customer_type - primary key

tblFundPrice
FundPriceID - primary key
FundCode - foreign key to tblFund
PriceDate - date field
Price - currency
TaxSheltered

By using the price per share, you just need to the number of shares or units a customer has to calculate their current value.

I am going to side-step tblLodgement for the moment. "Amount" make sense but I dont understand "Trail".

tblPortfolio
PortfolioID - primary key
ClientID - foreign key to tblClient
FundCode - foreign key to tblFund
NumberOfUnits
FundStartedDate

You could add to this a transaction table. Usually this type of thing would be done by the fund management. It is pretty tricky stuff.

tblTransaction
TransID - primary key
ClientID
FundCode
PortfolioID
TransUnits
TransPrice
TransDate
Commission

This is fairly simple, and does not follow the double entry system, but it may suffice.

For further reading...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

Micro$oft's answer...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Richard

 

[Travstar]

Hi willir,

Thanks for such a detailed answer!
In answer to your questions:

"Are you just going to manually enter the total fund amount? And manually maintain the balances."

No, I was planning on making this process automated, but I had not really though too much into it yet. I just figured a simple addition formula some place would be enough. Saying that, your price table is a great idea. i will give it a go tonight and hopefully nut it out.

"I am going to side-step tblLodgement for the moment. "Amount" make sense but I dont understand "Trail"."

That makes two of us. All they told me is that trail is another $ amouont needed to associate with a particular fund. Who am to argue ?

I think i have a fun night ahead getting this database to work


Travstar