One huge table of 1,000 Equities with 20 different fields, or (1 Viewer)

access2010

Registered User.
Local time
Today, 05:28
Joined
Dec 26, 2009
Messages
1,019
Could I please receive your suggestion as to why our database is not producing the results we would like to receive, linking two tables by Investmentl_ID.

We have a database containing about 1,000 Equities which we will be following.
===
Each equity has about 20 different criteria’s for research.

Should we create one huge table of 1,000 Equities with 20 different fields, or tables that are linked as below?
===

Table = Name_Stock_T = which would hold the Equity’s Code and Stock Name
Table = Symbol_Stock_T = which would hold the Equities Research and this table would be linked by = Symbol_Stock

or

Table = Name_Stock_T = which would hold the Equity’s Code and Stock Name
Table = ID_Stock_T = which would hold the Equities Research and this table would be linked by = Investmentl_ID

===
Your suggestion will be appreciated on which of the 3 choices will be better.

Thank you,
Nicole
 

Attachments

  • Stock_Symbol_ID=292.mdb
    336 KB · Views: 161

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:28
Joined
Feb 19, 2002
Messages
42,970
1,000 rows is trivial as far as table size goes. The analysis might be easier if you had 2 tables with the criteria being on separate rows. So instead of 20 columns in the single table. The master table has the Stock ID and name and the child table has CriteriaID, CriteriaName, StockID, CriteriaValue

My suggestion would be considered to be the normalized solution if the Criteria are all the same data type. I couldn't make sense out of your alternates.
 

mike60smart

Registered User.
Local time
Today, 12:28
Joined
Aug 6, 2017
Messages
1,899
Could I please receive your suggestion as to why our database is not producing the results we would like to receive, linking two tables by Investmentl_ID.

We have a database containing about 1,000 Equities which we will be following.
===
Each equity has about 20 different criteria’s for research.

Should we create one huge table of 1,000 Equities with 20 different fields, or tables that are linked as below?
===

Table = Name_Stock_T = which would hold the Equity’s Code and Stock Name
Table = Symbol_Stock_T = which would hold the Equities Research and this table would be linked by = Symbol_Stock

or

Table = Name_Stock_T = which would hold the Equity’s Code and Stock Name
Table = ID_Stock_T = which would hold the Equities Research and this table would be linked by = Investmentl_ID

===
Your suggestion will be appreciated on which of the 3 choices will be better.

Thank you,
Nicole
Hi Nicole

The Stock_Symbol_T Table has data as shown below:-

Investmentl_IDSymbol_StockF1
2123NAME 123
3234NAME 234
4345NAME 345

So For each InvestmentID you would have Many Equities?

If this is the case then you need another table Stock_Symbol_T_Equities

You would then create a Main Form based on Stock_Symbol_T
with a Subform based on Stock_Symbol_T_Equities
 

plog

Banishment Pending
Local time
Today, 07:28
Joined
May 11, 2011
Messages
11,611
You really need to demonstrate with better data for us to get a grasp of this. I understand a stock has a symbol and a stock has a company, so I can understand those values. What I don't understand is what values will go into all those other F fields. You left those blank so you're database doesn't help much. So I will go by your words:

We have a database containing about 1,000 Equities which we will be following.
===
Each equity has about 20 different criteria’s for research.

That means you need 2 tables:

tblEquities - Equity ID (autonumber), stock symbol, stock name
tblCriteria - CriteriaID (autonumber), EquityID, fields for criteria
 

Users who are viewing this thread

Top Bottom