Query limitations and not normalized tables

[fboehlandt]

Hi everyone,
I have the following three tables:

Table Assets
Code AUM_Date1 AUM_Date2 ...AUM_Date200
1001
1002
...
7000
this table contains the assets-under-management (AUM) for 6000 investment funds for 200 different months (i.e. 1200000 obs)

Table Returns
Code ROI_Date1 ROI_Date2 ... ROI_Date200
1000
1001
...
7000
this table contains the return-on-investments (ROI) for 6000 investment funds for 200 different months (i.e. 1200000 obs)

Table Information
Code Strategy Leverage
1001
1002
...
7000
this table contains additional information on 6000 investment funds with respect to investment strategy (30 to choose from) and leverage(yes/no)

The tables 'Assets' as well as 'Returns' have separate dates as separate fields (i.e. they are not normalized), which is obviously not ideal. In the example of the 'Assets' table it should look as follows:

Table Asset(revised)
Code Date Value
1001 Date1 AUM1
1001 Date2 AUM2
...
1001 Date200 AUM200
1002 Date1 AUM1
1002 Date2 AUM2
...
1002 Date200 AUM200
mutatis mutandi for all funds up to 6000

Combining the information contained in all three tables it should look something like this:

Query Result
Code Strategy Leverage Date Value Type
1001 xyz yes/no Date1 AUM1 AUM
1001 xyz yes/no Date1 ROI1 ROI
1001 xyz yes/no Date2 AUM2 AUM
1001 xyz yes/no Date2 ROI2 ROI
...
1001 xyz yes/no Date200 AUM200 AUM
1001 xyz yes/no Date200 ROI200 ROI
1002 xyz yes/no Date1 AUM1 AUM
1002 xyz yes/no Date1 ROI1 ROI
1002 xyz yes/no Date2 AUM2 AUM
1002 xyz yes/no Date2 ROI2 ROI
...
1002 xyz yes/no Date200 AUM200 AUM
1002 xyz yes/no Date200 ROI200 ROI
etc.

I have come up with the following query to achieve just that:

SELECT Information.Code, Information.Strategy, Information.Leverage, 'MM/DD/YYYY' As Date, Return.ROI_Date1 As Value, 'ROI'As Type
FROM Information INNER JOIN Return ON Information.Code = Return.Code
UNION
SELECT Information.Code, Information.Strategy, Information.Leverage, 'MM/DD/YYYY' As Date, Return.AUM_Date1 As Value, 'AUM'As Type
FROM Information INNER JOIN Assets ON Information.Code = Assets.Code
UNION
SELECT Information.Code, Information.Strategy, Information.Leverage, 'MM/DD/YYYY' As Date, Return.ROI_Date2 As Value, 'ROI'As Type
FROM Information INNER JOIN Return ON Information.Code = Return.Code
UNION
SELECT Information.Code, Information.Strategy, Information.Leverage, 'MM/DD/YYYY' As Date, Return.AUM_Date2 As Value, 'AUM'As Type
FROM Information INNER JOIN Assets ON Information.Code = Assets.Code
...
SELECT Information.Code, Information.Strategy, Information.Leverage, 'MM/DD/YYYY' As Date, Return.ROI_Date200 As Value, 'ROI'As Type
FROM Information INNER JOIN Return ON Information.Code = Return.Code
UNION
SELECT Information.Code, Information.Strategy, Information.Leverage, 'MM/DD/YYYY' As Date, Return.AUM_Date200 As Value, 'AUM'As Type
FROM Information INNER JOIN Assets ON Information.Code = Assets.Code;

Note that 'MM/DD/YYYY' is the manually entered date for the specific observation

Which works fine for smaller samples with a low number of dates considered. Eventually, the query restrictions with respect to max parameters kick in (I assume). So here are my questions:

1. Is there a way to streamline the above?
2. Is there a workaround for the parameter restrictions if not?
3. Is there a query/code snippet for VBA that can be used to create a normalized table (in the event that the sql is not possible)

Please note that I run the query as part of OLE DB query in Excel (importing from Access) where the above query is the command text. Thus, the query should ideally com in one piece. Also, there are some additional components to the real query that I ommitted for simplicity's sake that roughly double the required UNION statements. Any help with this is greatly appreciated. Thanks in advance...

 

[jzwp22]

If I understand what you are doing, with a properly structured database, you should be able to do this with a simple query (i.e. not a UNION query).

tblStrategies
-pkStrategyID primary key, autonumber
other fields relative to the strategy

It appears that an asset can have only one strategy associated with it (correct me if I am wrong)

tblAssets
-pkAssetID primary key, autonumber
-AssetCode
-fkStrategyID foreign key to tblStrategies

An asset can have many transactions. The transactions can be either of two types ROI or AUM. To take care of other types of transactions in the future, I would put the types in a table as records

tblTransTypes
-pkTransTypeID primary key, autonumber
-txtTransType

tblAssetTransactions
-pkAssetTransID primary key, autonumber
-fkAssetID foreign key to tblAssets
-fkTransTypeID foreign key to tblTransTypes
-dteTrans (your date field)
-currTransAmt

Regarding your query, I do not know what you mean by this:

Note that 'MM/DD/YYYY' is the manually entered date for the specific observation

If you want to select records based on a certain date or date range that belongs in the WHERE clause of the query, not the SELECT clause

In addition, the word "date" is a reserved word in Access, so it should not be used as a field name or as an alias in a query.

 

[fboehlandt]

@jzwp22
Thanks very much for your quick reply. Here some additions to clarify:

1. each fund can have only one strategy associated with it (1 to 1)
2. each fund can either be leveraged or not (1 to 1)

this information is contained in the table 'information'

1. c) each fund can have several ROI (return observations) attached to it. For example: from January 1999 to January 2010 (1 to n)
2. d) each fund can have several AUM (asset observations) attached to it (1 to n)
3. Each ROI (return observation) has a corresponding AUM (asset observation)

With respect to the comments on reserved words in Access, mea culpa. I oversimplified the example here for clarity reasons. I, of course, do not use reserved words as field names.

On the issue of different observations for returns/assets. Each date is given as a different field (i.e. return_January1999/asset_January1999 as field name and the respective observations as field values). In other words, the tables are not stacked as indicated in Table Assets(revised) in my original post. Thus, when building the query I used UNION to stack the observations. The corresponding date had to be entered manually as 'return_January1999' is no valid date format.

Also, I have no influence on the format/layout of the original tables. They come from an external database provider (they don't update the records but send a new table every time). Whilst not impossible to declare the keys and relationships every time, it is a bit cumbersome.

Latly, don't worry to much about returns and assets (that I can sort out myself). The core issue is that:

• 1 fund can have n returns
• the returns are kept in a separate table
• this table contains the following fields: fundname, fundID (foreign key), and a field for every return observation (about 200)
• this table should contain the following four fields only: fundname, fundID, MyDate, return observation

So if the dimensions of the original table were Nfunds x (Ndates + 2) for rows and cols, the new table should contain (Nfunds x Ndates) x 4 for rows and cols. I hope that clearifies the problem. Any ideas how to create a simple query/union query/cross-table to do that? Thanks in advance

 

[jzwp22]

From the sound of it, the number of fields in your table will continue to increase as new data is obtained, you will eventually run into the 256 fields in a table limit in Access. I think that you are wasting a lot of effort trying to get things to work properly due based on a non-normalized table design.

They come from an external database provider (they don't update the records but send a new table every time)

Since the data is coming from an outside source, my recommendation is to create a new database that is properly normalized and migrate the data that you receive into the new database and run your queries from there. You can automate the data migration portion and in the long run things will be much easier to handle as your dataset grows.

 

[fboehlandt]

yes, that is exactly what I am doing now. Best way to handle this in the long-run. Thanks for your input, it is greatly appreciated