Multiplying fields from different tables

[mor]

Firstly apologies as I'm a novice at Access and am still familiarising myself so I appreciate any help you can provide.

I'm trying to create a query that will return a calculated field called "PnL".

The formula for "PnL" is as follows: PnL = notional*management_fee*NAV*

The "notional" field is a value derived from a table called "Deal_information" (primary key deal_id) and has an associated "management_fee" and "product" field. So obviously finding the product of the notional field and the management_fee field isn't a problem.

However "NAV" field is from another table (VL_information) that contains the historical Net Asset Value of each product. Fields are (VL_id, vl_date, product, NAV).

Therefore for each "notional" record I would like to return the historical PnL.

By way of example. Lets say that the notional valuefor a particular record is $100, with a corresponding management fee of 10% (the managment fee is product dependent). Then I would like to find the evolution of PnL by multiplying this record by the evolution of the NAV. This would do so for each notional value.

Can anyone help me in how to achieve this? I have attached a snapshot of the table relationships for reference.

Many thanks,

MOR

 

[Galaxiom]

Sorry. i have read your description several times but your field name abbreviations make it too difficult to understand your question. You use far more characters for the full word "information" in every table name than you do for the significant part of the name. (All tables contain information so why use the word at all?)

I imagine that the problem is you need to compare the value of a record in one table with a value selected by a matching range in the other table.

If so you need to use a non equal join in the query to connect the tables with the rates to the value of the product.

One way is to select the rate in an aggregate query that returns the minimum product value from a query with a join to values that are greater than the product value.

This greater than join can only be done in SQL view.

It looks like you probably need to pick the records with the maximum dates in a subquery first.

Sorry but my explanation is probably as confusing as your question.

 

[mor]

I imagine that the problem is you need to compare the value of a record in one table with a value selected by a matching range in the other table.

That's right but ok... Will try to simplify things as I have obviously committed some awful MS access crimes...

Table X has the following fields: deal_id, deal_date, no_shares, management_fee, product_purchased

Lets say that for each record in this table I need to return the value in a calculated field [no_shares]*[management_fee].

In another table, Table Y has the following fields: NAV_id, product, date, net_asset_value.

Therefore, Table Y gives an evolution of prices for all products available.

For a given deal which has an associated product in Table X, I need to find the corresponding product in Table Y and the price evolution of that product in Table Y and multiply each relevant record by [no_shares]*[management_fees] from the date of purchase of the product.

Does this make it clearer what I am trying to do?

 

[Pat Hartman]

Structurally your query won't work as you expect. It will produce a Cartesian Product since although each table is related to Product, they are not related to each other. These are parallel relationships. The first child table may have 2 rows and the second may have three. The query would produce 2 * 3 or 5 rows.

 

[mor]

Thanks Pat for the clarification.

How do you suggest I go about rectifying this? Obviously I need to create a relationship between the two child tables but I'm not entirely sure how?

Thanks,
mor

 

[Pat Hartman]

You can't create a relationship where one does not exist. Simply having a common foreign key doesn't cut it. Think of three tables - tblStudents, tblStudentPets, tblStudentVehicles. Both Pets and Autos are related to Students but they have nothing to do with each other. Whether a student has a dog or a cat or both isn't related in any way to whether he also has a motorcycle or a pickup truck or a bicycle or all three.

You might be able to solve this problem by creating a query that finds the correct vl record for a deal. Then join that query to the product table.

 

[Galaxiom]

My description a greater than join in an aggregate query is what you need. Post a cut down database with just the essential tables and a sufficient sample of the data.

I will write the query for you.

 

[Pat Hartman]

A greater than join could still return multiple rows causing a Cartesian Product. When you keep price history, the simplest solution is to keep both start and end effective dates (your table has only one date and it isn't clear whether it is the start date or the end date). Then your join can be >= start and <= end and if the data is correct, that will always return a single row.

 

[Galaxiom]

My impression is that the date is not the issue but rather as I said in my first post:

"to compare the value of a record in one table with a value selected by a matching range in the other table".

This could be dealt with in the same way as Pat suggested by maintianing the start and end values for the range and using >= the lowest and <= the highest.

However this would technically breach normalization and require careful validation to ensure there was no overlap or gap between ranges.

Rerurning the Min of records greater than the figure (or the Max of those less than) is an effective way to avoid these issues.

 

[mor]

My description a greater than join in an aggregate query is what you need. Post a cut down database with just the essential tables and a sufficient sample of the data.

I will write the query for you.

That would be immensely helpful Galaxiom. Just running a query with the following fields from the respective tables does however return what I want:

[deal].[deal_id], [product].[product_name], [VL].[vl_date], [VL].[valeur_liquidative]

This returns the deal ID with the associated product bought, as well as the evolution of the price of each investment product bought. Is there anything wrong with this in terms of what you have described?

Thanks!
MOR

 

[Galaxiom]

I think I still don't really understand the data structure and goal.

If you are getting the result you want then I guess you must have it right.

 

[mor]

Ok I will explain from the very start.

The company I work for sells investment products. Each client will invest in one of these products on a given date (each assigned a product_id and date of deal) and this is registered as a deal with a given deal_id.

The price of the investment products are subject to change and the value is verified every day. The company I work for takes a fee which is a percentage of the value of the product from the client. This fee is calculated daily based on the daily value of the product.

Therefore, I am trying to create a query which returns the dates for which a client has invested in the deal and the value on those dates, such that I can calculate the total fees over the time period invested by the client.

Now, as you can see from the table relationships, the deal has an associated product, which has an associated set of dates and values over those dates but there is no direct relation between the deal and the vl table.

So, for example, a client buys product A on the 01/01/2013 at an initial value of 1000. This product's value changes day on day. Lets say it is now 01/03/2013 (UK). I would like to return the values from 01/01/2013 - 01/03/2013 for this product so that the management fees can be worked out for this particular deal since the deal opened.

Does this make sense now?