New to Access - Help Required

[mccrimmon]

Hello,



I am hoping someone can help me.

I have recently just started using access and am trying a few ideas out that have previously been done in excel etc.

So heres my problem / question....

Apologies for the long description. I want to ensure that everyone understands what im trying to achieve.


I have 4 tables:

tblStatic

This has many fields, however, the ones we are interested in this example are:

• Fund
• Sedol
• Price Type

The unique keys are a combination of Fund and Sedol


tblPriceTypeMapping

This table has two fields:

• Price Type (Price Type 1, Price Type 2, Price Type 3 etc etc)
• Source Table (either tbl Universe or tblGlobal)

tblUniverse

So here comes the problem.

This table has Fund and Sedol along with numerous Price Types. The price Types are fields in this table.

For Example I will have Fields:

• Fund
• Sedol
• Price Type 1
• Price Type 2
• Price Type 3

etc etc


tblGlobal

This is similar to tblUniverse, however, just includes different price types.

• Fund
• Sedol
• Price Type 4
• Price Type 5

etc etc



I basically want to write a query that uses the Static Data table and returns the relevant price, based on the price type, fund and sedol.


The Price Type mapping table tells me what table to retrieve the price from (tblGlobal or tblUniverse) and the Price Type would then be a field header within that specific table.



So using example data.....

tblStatic

Record 1 Fund = BM01
Record 1 Sedol = 0101010
Record 1 Price Type = Price Type 2

tblPriceTypeMapping

Price Type 2 = tblGlobal


tblGlobal

Fund BM01 and Sedol 0101010 has a price of 1.5 in Price Type 2


Does anyone have any suggestions on how I would achieve this and if it is even possible?

Thanks

McCrimmon
Access newbie

[Rainlover]

I would suggest that your Table designs are not correct.

Please search "Normalisation" here and in Google to gain an understanding.

[mccrimmon]

Hi

Two of the tables are import tables (tblGlobal and tblUniverse)

The other two are static.

How would you arrange this sort of data?

Cheers

[AdventureBob]

A good place to start is to draw what youre output should look like. What are you trying to get to? That will drive your structure, it will also help us understand what youre trying to do.

[mccrimmon]

I have a list of multiple funds that hold multiple sedols.

I want to pull the relevant price for each of those fund and sedol combinations based on the price type held in the static data table.

Some funds and sedol combination will have the price on the Global Table and others will have the price on the Universe Table.

On each of the tables, there will be more than one price type as field headers.

So for example.

Fund "BM01" sedol "0101010" might have a price type of "Price Type 1" which is a field header on table "tblUniverse"

I then want to find the relevant fund and sedol from this table and return the price from the field header "Price Type 1"


Is that any clearer?

Thanks again

[Rainlover]

Have you read up on Normalisation yet.

Therein you may find the answers you are looking for.