Conbining Data from different Forms / Tables

[KW99]

Hi,

I wonder if there is anyone who can help with this one. It should be quite easy but I cannot seem to get my head round it at the moment.

I am trying to add an investment section to a database I have designed.

So far I have tables as follows:

Table 1 Client Name
Table 2 Fund Name
Table 3 Fund Name / Bid price / Date
Table 4 Client Name / Fund Name / Units

What I need to end up with is, the client name, all funds held, units held for each fund and the fund value for a specific date (bid price * Units).

I have connected table 1 and 4 by client so that all of the funds entered for a client are stored under that client with their unit holding.

What I am having difficulty with is then connecting the clients holding with the fund data to get the clients fund value for a specific date. eg.

A Client / Gold Fund / Date of valuation / Bid price / Fund Value (Bid price * Units)

Some sort of set up is needed to if a date is entered i.e. 03/03/2009, the database will look up the fund prices for that date and multiply them by the clients units to get a fund value for that date. I know that the latter can be calculated on a form or report, however it is just getting it to connect with the date and bid price for that date I am struggling with.

Does anyone know if there is an easy way to do this as I am sure I am trying to overcomplicate it somehow.

Thanks in advance.

 

[mary.h]

Hi,

it is a good way to establish relationships first. To do this, you must index the fields, that belong to the relationship. For example:

Table T_Clients
Field: ClientNumber (indexed) - I would use a number field as master field
Field: ClientName

Table T_Funds
Field: FundNumber (indexed)
Field: FundName

Table T_FundsByClients
Field: ClientNumber
Field: FundNumber
Field: Units

Table T_BidsByFunds
Field: FundNumber
Field: BidPrice
Field: Date

Now establish ( 1 : many)-relationships between
T_Clients (ClientNumber) to T_FundsByClients (ClientNumber)
T_Funds (FundNumber) to T_FundsByClients(FundNumber)
T_Funds (FundNumber) to T_BidsByFunds(FundNumber)

Now open a new query and show all three tables. You will see that Access automatically shows the "links" according to the relationships.

Create a form with a subform. The "master" form can be linked to T_Clients and the subform's record source is set to your query.

Subform to masterform are linked by clientnumber.

You can read a lot about "How to set up a master with subform" here in thisf forum (search for i.e. subform)

Hope this did help you a bit.
Regards.