Table confusion!!

[sillylady]

Hi All,


I have an excel sheet like this:

Ratio		            1	2	3	4	5
Profitability							
NIM	20%	UL	10	7.49	5.49	3.49	2.49
		LL	7.5	5.5	3.5	2.5	1
ROAA	30%	UL	50	39	29	19	9
		LL	40	30	20	10	0
ROAE	30%	UL	50	39	29	19	9
		LL	40	30	20	10	0
							
Asset Quality							
Gross NPL 50%	UL	50	39	29	19	9
		LL	40	30	20	10	0
Net NPL	 50%	UL	9	19	29	39	50
		LL	0	10	20	30	40

I want to convert this into a table. Please can any one help me?

[Hack]

I want to convert this into a table. Please can any one help me?

What kind of table?

[sillylady]

What kind of table?

MSSQL table. How can i convert this excel structure in meaningful table. I want a table structure based on this given excel sheet. There are 2 groups "Profitability" and "Asset Quality" each group has different ratios (like NIM), each ratio has a weightage (like 20%) then each ratio has UL (upper limit) LL (lower limit) and based on the upper & lower limit there is a point between 1 to 5.

[salvelinus]

In Access you could do a DoCmd.TransferSpreadSheet call. Don't know if SQL server has something similar. If your fields are calculated, that could be a problem.

[jeroen79]

How about this?
You'd need 5 records per sheet, each record is one column.
If you prefer one record per sheet then you just add each field five times and add a number to it.

Filling the table would be done with a procedure that sends the right cells to the right fields.

tbl_Table
    Sheet_Id
    Column_Id
    Profitability_NIM_20_UL
    Profitability_NIM_20_LL
    Profitability_ROAA_30_UL
    Profitability_ROAA_30_LL
    Profitability_ROAE_30_UL
    Profitability_ROAE_30_LL
    AssetQuality_GrossNPL_50_UL
    AssetQuality_GrossNPL_50_LL
    AssetQuality_NetNPL_50_UL
    AssetQuality_NetNPL_50_LL

Are the weightings variable?

[sillylady]

How about this?
You'd need 5 records per sheet, each record is one column.
If you prefer one record per sheet then you just add each field five times and add a number to it.

Filling the table would be done with a procedure that sends the right cells to the right fields.

tbl_Table
    Sheet_Id
    Column_Id
    Profitability_NIM_20_UL
    Profitability_NIM_20_LL
    Profitability_ROAA_30_UL
    Profitability_ROAA_30_LL
    Profitability_ROAE_30_UL
    Profitability_ROAE_30_LL
    AssetQuality_GrossNPL_50_UL
    AssetQuality_GrossNPL_50_LL
    AssetQuality_NetNPL_50_UL
    AssetQuality_NetNPL_50_LL

Are the weightings variable?

Thanks for ur suggestion. I think for each complete record like (Ratio Name, UL, UL, Wieghtage and Point) and that's what u have suggested. Is there any other way to simplify it, even if more than 1 table is needed.

[jeroen79]

Depends.
Do you always have the same fields? No more, no less?

If the sheets are always the same then I see little use in creating more tables.
Sure, each individual table might have few fields but all tables combined would have the same amount plus some extra fields to keep things linked.

Now if the amount of data on these sheets is variable then a multi-table setup might be useful.

Who will be using this data and how do they want to use it?

[sillylady]

Depends.
Do you always have the same fields? No more, no less?

If the sheets are always the same then I see little use in creating more tables.
Sure, each individual table might have few fields but all tables combined would have the same amount plus some extra fields to keep things linked.

Now if the amount of data on these sheets is variable then a multi-table setup might be useful.

Who will be using this data and how do they want to use it?

This is just an excel sheet which is used as a reference. The Fields will remain same. I have been told to convert this into a table and there will be one more table (GRADE) that will hold the GRADE_Name and Point for each grade. Based on the point of excelsheet and after some calculation there will be another point which will be compared with the Point in the grade table and a GRADE will be awarded. Hope I am clear.

[jeroen79]

What do these grades and points do?
How do they relate to these sheets?

[sillylady]

What do these grades and points do?
How do they relate to these sheets?

I don't these 2 tables has any direct relation. Based on a calculation POINT will be picked up and it will be calculated again and the result will be compared with in GRADE table POINT field and then a grade will be awarded.

For example:

Table RANGE has fields named Ratios Group, Ratio Name, UL, LL, Weightage,Point)

Table GRADE has fields named Grade and Point

P.S. Field point is not the same in both the table.

[LinXG]

It will be a rare day when you can import or convert an excel spreadsheet so that the table looks and behaves the way your spreadsheet does.

Here is a table which I believe accomplishes what you're after, no garantees but you can allows play around with it to get it to do what you want.

Here are the field names with sample data for the first 3 items of the first line in the sample you provided.

Field name        1                2               3       
====================================================            
 
Col_0               Profitability  Profitability  Profitability                     
Col_1               NIM            NIM            NIM                               
Col_2_Percent       0.2            0.2            0.2                               
Limit_Type          UL             UL             UL                              
Ratio               1              2              3                                    
Data_Val            10             7.49           5.49

Each to the three columns 1-3 represents one complete record in your database table.