How to structure a Table with many, many, many Fields?

[ryguy7272]

I’m starting a new thread here, because the last one is getting kind of old
and I’m not sure people are viewing it anymore. That was towards the end of
January; it was so last-month! LOL!

Anyway, I have a table with several security symbols. I’m setting this up
today with 30 stock symbols, but the list will likely grow to over 1,000
stock symbols. For now, I am using one single Field, and I named it Stocks.
From my former post, I got some great feedback and it was suggested that I
have a Table with stock symbols listed in rows. This kind of makes sense.
What doesn’t make sense, is how to create a Table with just dates to show
stock returns. I believe I would need a field in that Table to show the
stock name, and then the returns, by closing price each day, under the stocks
names. So, why would I need a separate Table with just stock names? Why
wouldn’t I put everything in one table? I understand normalization, but if I
have just stock names in one table and just dates and returns in another
Table, how the heck would Access know how to link the names to the correct
returns? I’ve heard that properly normalized Tables rarely have more than 30
or so Fields, and now I have 30 Fields, but this is going to grow over time.
If I eventually track 1,000 stocks, I’ll never be able to put those in an
Access Table as the maximum number of fields is 255, right.

Seeking advice on how to structure this.

Thanks!
Ryan--

 

[Jeff Boyce]

I'm wondering what it was about the advice you received earlier that you
feel doesn't fit your situation?

Normalization is part art, part science.

What are the "things" about which you wish to keep information? Based your
post (and previous posts), you are interested in [Stocks], and in
[HistoricalStockPrices]. These would be tables in your database.

What facts do you have/need to have about [Stocks]? Again, guessing, you
need {StockSymbol} and {StockName}.

And about [HistoricalStockPrices]? How about {which stock}, {which date},
and {what price}?

So how do you get {StockName} if you don't have it in the
[HistoricalStockPrices] table? Simple! Use a query to join the two tables
together. Join them on a field they share in common (e.g., {StockSymbol}).

Here's one way to design your tables, but look back over the other
suggestions you've already received in your earlier thread):

tblStock
StockSymbol (primary key, text)
StockName

trelStockPrice
StockPriceID (primary key, autonumber)
StockSymbol
PriceDate
ClosingPrice

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Duane Hookom]

Stocks probably have some sort of natural primary key such as the symbol.
They might have other attributes such as a title and other comments that are
unique to the stock and probably change very little over time. This is your
table of stocks.

Then, you have a stock returns table that typically might store the stock
table primary key, the date, and one or two "return" type values. I'm not
sure what you are tracking. Perhaps you would have fields for daily high,
low, average, volume,...

So far these tables have a maximum of less than 10 fields. Where do all of
your fields come from?

 

[Steve]

Hello Again Ryan,

Here are the tables you need:

TblSecurity
SecurityID
SecuritySymbol
SecurityInActive (Yes/No)
<other security data fields>

TblSecurityPriceHistory
SecurityPriceHistoryID
SecurityID
PriceDate
Price

Steve
(e-mail address removed)

 

[ryguy7272]

I am struggling with this because I am thinking that the Fields come from the
stock names. 1,000 Stocks = 1,000 Fields. I've been using Access for a
number for years, but I've never done this kind of project before. I just
can't figure out how to set this up. Kind of frustrating. There must be an
easy solution; I just can't think of what it is.

 

[ghetto_banjo]

are you confusing fields with records?

1000 stocks = 1000 records.....

 

[ryguy7272]

Ok, so I built two tables, based on Jeff's recommendation. Basically, I'm
trying to put my Excel data in Access. My stocks and prices are in Excel;
stock symbols (30 total) in row 3 and dates (252 total) in Column A. In
Excel, B4:AE254, I have all the closing prices for each day for one year
(today - 365; obviously there are far fewer trading days in the year than
there are days in the year.) Now, how do I get all those dates and all those
closing prices for all those stocks into an Access Table that has one
PriceDate and one ClosingPrice, per stock. That's the disconnect for me.

I built a simple Query to pull the data from these two tables together;
here's the SQL:
SELECT tblStocks.StockSymbol, tblStockHistory.PriceDate,
tblStockHistory.ClosingPrice
FROM tblStockHistory INNER JOIN tblStocks ON tblStockHistory.StockSymbol =
tblStocks.StockSymbol;

The price is correct for one Stock and for one PriceDate and for one
ClosingPrice. I want to view historical records, not just one date and one
closing price. I guess that's the problem.

 

[Jeff Boyce]

The fact that you've done this in Excel is probably why learning
"normalization" and "relational" database design is so difficult.

Here's an example of what you could end up with (untested):

Here's tblStock with two stocks in it:

StockSymbol StockName
IBM Itty Bitty Machine
A American Telephone and Television

If you have more than two stocks, just keep adding records (i.e., rows), not
columns.

Here's some stock history records (again, untested, example data):

HistoryID StockSymbol PriceDate Price
1 IBM 1/1/2010 $100.00
2 IBM 1/2/2010 $101.00
3 IBM 1/3/2010 $102.00
4 A 1/1/2010 $17.95
5 A 1/2/2010 $18.50
6 A 1/3/2010 $19.95

Notice that you can just keep adding new "day's" stock prices for as many
stocks as you are tracking. ... and you do that by adding rows, not
columns.

I'm still curious what didn't work for you with the earlier suggestions you
got... We can't improve our assistance if we don't know what's wrong with
it!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

1,000 Stocks = 1,000 Fields.

You're mistaken.

1000 stocks = 1000 ROWS (Records).

 

[John W. Vinson]

Ok, so I built two tables, based on Jeff's recommendation. Basically, I'm
trying to put my Excel data in Access. My stocks and prices are in Excel;
stock symbols (30 total) in row 3 and dates (252 total) in Column A. In
Excel, B4:AE254, I have all the closing prices for each day for one year
(today - 365; obviously there are far fewer trading days in the year than
there are days in the year.) Now, how do I get all those dates and all those
closing prices for all those stocks into an Access Table that has one
PriceDate and one ClosingPrice, per stock. That's the disconnect for me.

A good Spreadsheet can be a very bad Table (and, I confess, vice versa).

Access works best with "tall-thin", normalized table data; it's much more
difficult to work with "wide-flat" spreadsheet data.

I'd really suggest migrating your data from Excel into a normalized table
structure. A couple of folks have given you suggestions for the structure of
the table.

In order to migrate the data into a form which Access can use, you can use a
"Normalizing Union Query". Use File... Get External Data... Link to link to
the spreadsheet. It's easiest if you have a header row so that column A is
TradeDate; row 1 has (I presume) the stock name, and the (say) C35 has the
closing price for whatever stock is in column C (IBM let's say), row 35 (March
12, 2008).

Create a new Query - don't add any tables yet. Instead immediately select
View... SQL.

Edit this into the query window, using your stock names:

SELECT TradeDate, "ADM" As Stock, [ADM]
FROM linkedspreadsheet
WHERE [ADM] IS NOT NULL
UNION ALL
SELECT TradeDate, "AIG" As Stock, [AIG]
FROM linkedspreadsheet
WHERE [AIG] IS NOT NULL
UNION ALL
SELECT TradeDate, "BXZX" As Stock, [BXZX]
FROM linkedspreadsheet
WHERE [BXZX] IS NOT NULL
UNION ALL

<etc through all 30 columns>

This query will "unravel" your wide-flat into a tall-thin. You can save it as
a named query, and then base an Append query upon it to populate your
tall-thin table.

 

[Duane Hookom]

You can use either a normalizing union query like:

SELECT [Datefield], "MSFT" as StockSymbol, [MSFT] as StockValue
FROM YourSpreadsheet
UNION ALL
SELECT [Datefield], "GOOG", [GOOG]
FROM YourSpreadsheet
UNION ALL
--- etc ---
SELECT [Datefield], "ZEP", [ZEP]
FROM YourSpreadsheet;

The other option is to write some code that opens your spreadsheet data and
loops through records and fields appending records to a normalized table.

 

[Steve]

If you email me your Excel data, I will create the database and import your
Excel data for a very nominal fee.

Steve
(e-mail address removed)

 

[John... Visio MVP]

If you email me your Excel data, I will create the database and import
your Excel data for a very nominal fee.

Steve
(e-mail address removed)

Don't you mean questionable results at unreasonable prices?



Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP

 

[pietlinden]

I am struggling with this because I am thinking that the Fields come fromthe
stock names.  1,000 Stocks = 1,000 Fields.  I've been using Access for a
number for years, but I've never done this kind of project before.   I just
can't figure out how to set this up.  Kind of frustrating.  There must be an
easy solution; I just can't think of what it is.

Ryan,
if you post an example (say 5-10 records), I can probably knock this
out in a few minutes. I had to do this kind of thing a couple of years
ago. Essentially, my example was repeating "chunks" of

[patientID], [treatmentdate], ..., [toxicity] [relatedness]
[causality]

the problem was that the "toxicity" field was the actual name of a
symptom, which I wanted IN the table, not on it. If that's what you
have, then you have to walk the fields collection of the Table (Use
DAO), and then you can grab the info and write it into your database
using filtered append queries.

I can probably fix it in about 15 minutes. The hard part is if you
have weird patterns. Otherwise, it's very straightforward.

if you want, e-mail me the file (or part of it) and I'll post the
module code to fix it.

 

[ryguy7272]

Thanks, one and all, for the valuable insight. I totally forgot about Union
Queries. I haven’t touched on these in over a year. Yeap. That should do
it. Sorry to be so thick everyone. I think I can manage from here.
Ryan--

 

[ryguy7272]

Thanks. What is your email? I tried, a couple times, to send an email to:
(e-mail address removed); it keeps bouncing back. Please send me an email and
I will respond. (e-mail address removed)



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

I am struggling with this because I am thinking that the Fields come from the
stock names. 1,000 Stocks = 1,000 Fields. I've been using Access for a
number for years, but I've never done this kind of project before. I just
can't figure out how to set this up. Kind of frustrating. There must be an
easy solution; I just can't think of what it is.

Ryan,
if you post an example (say 5-10 records), I can probably knock this
out in a few minutes. I had to do this kind of thing a couple of years
ago. Essentially, my example was repeating "chunks" of

[patientID], [treatmentdate], ..., [toxicity] [relatedness]
[causality]

the problem was that the "toxicity" field was the actual name of a
symptom, which I wanted IN the table, not on it. If that's what you
have, then you have to walk the fields collection of the Table (Use
DAO), and then you can grab the info and write it into your database
using filtered append queries.

I can probably fix it in about 15 minutes. The hard part is if you
have weird patterns. Otherwise, it's very straightforward.

if you want, e-mail me the file (or part of it) and I'll post the
module code to fix it.
.

 

[Jeff Boyce]

Ryan

For your future consideration ... posting your (or anyone else's) email
address in these newsgroups invites "harvesting" by spammers.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible

Thanks. What is your email? I tried, a couple times, to send an email
to:
(e-mail address removed); it keeps bouncing back. Please send me an email
and
I will respond. (e-mail address removed)



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

I am struggling with this because I am thinking that the Fields come
from the
stock names. 1,000 Stocks = 1,000 Fields. I've been using Access for
a
number for years, but I've never done this kind of project before. I
just
can't figure out how to set this up. Kind of frustrating. There must
be an
easy solution; I just can't think of what it is.

--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''..

:
Stocks probably have some sort of natural primary key such as the
symbol.
They might have other attributes such as a title and other comments
that are
unique to the stock and probably change very little over time. This
is your
table of stocks.

Then, you have a stock returns table that typically might store the
stock
table primary key, the date, and one or two "return" type values. I'm
not
sure what you are tracking. Perhaps you would have fields for daily
high,
low, average, volume,...

So far these tables have a maximum of less than 10 fields. Where do
all of
your fields come from?

--
Duane Hookom
Microsoft Access MVP

:

I'm starting a new thread here, because the last one is getting
kind of old
and I'm not sure people are viewing it anymore. That was towards
the end of
January; it was so last-month! LOL!

Anyway, I have a table with several security symbols. I'm setting
this up
today with 30 stock symbols, but the list will likely grow to over
1,000
stock symbols. For now, I am using one single Field, and I named
it Stocks.
From my former post, I got some great feedback and it was suggested
that I
have a Table with stock symbols listed in rows. This kind of makes
sense.
What doesn't make sense, is how to create a Table with just dates
to show
stock returns. I believe I would need a field in that Table to
show the
stock name, and then the returns, by closing price each day, under
the stocks
names. So, why would I need a separate Table with just stock
names? Why
wouldn't I put everything in one table? I understand
normalization, but if I
have just stock names in one table and just dates and returns in
another
Table, how the heck would Access know how to link the names to the
correct
returns? I've heard that properly normalized Tables rarely have
more than 30
or so Fields, and now I have 30 Fields, but this is going to grow
over time.
If I eventually track 1,000 stocks, I'll never be able to put those
in an
Access Table as the maximum number of fields is 255, right.

Seeking advice on how to structure this.

Thanks!
Ryan--

Ryan,
if you post an example (say 5-10 records), I can probably knock this
out in a few minutes. I had to do this kind of thing a couple of years
ago. Essentially, my example was repeating "chunks" of

[patientID], [treatmentdate], ..., [toxicity] [relatedness]
[causality]

the problem was that the "toxicity" field was the actual name of a
symptom, which I wanted IN the table, not on it. If that's what you
have, then you have to walk the fields collection of the Table (Use
DAO), and then you can grab the info and write it into your database
using filtered append queries.

I can probably fix it in about 15 minutes. The hard part is if you
have weird patterns. Otherwise, it's very straightforward.

if you want, e-mail me the file (or part of it) and I'll post the
module code to fix it.
.