More than 255 Fields in a Table

[ryguy7272]

I just read this:
http://blogs.msdn.com/access/archive/2006/06/05/access-2007-limits.aspx

I guess the number of fields in a Table hasn’t changed b/w Access 2003 and
2007. I’m thinking of a way to work with security symbols, and may have a
little over 1,000 in my Access 2007 DB. I was going to have the symbols in
the fields and the historical prices on the rows underneath. However, the
255 limit would prevent this (I’m eventually going to add 1,000 positions).
I could put all symbols in rows in a Table (and never come close to the limit
of rows) but then how would I identify these securities and pull what I need
into a query, along with the historical prices? Old symbols will be deleted,
and new ones will be added, on a regular basis. Now, I’m importing symbols
and historical prices into Excel 2007 (from finance.yahoo.com). Would it
just be better to leave everything in Excel 2007, where I have many more
columns than I need? I am working with someone who is leaning towards using
Access for reporting. Does this make sense?

Thanks,
Ryan---

 

[Steve]

Access is not a spreadsheet! In Access, data is stored in a list not across
columns. Your table needs to look something like:
TblSecuritySymbol
SecuritySymbol
SecuritySymbol
HistoricalPrice

Steve
(e-mail address removed)

 

[kc-mass]

Hi,

I do not believe that it is ever necessary or structually logical to have
250 fields in a single table.

If you have 1000 positions, that might require one field/column and 1000
rows.

You should share your initial table design with the many, very smart folks
that congregate here.

They are willing and able to help you with your design. I think they could
help you tremendously with the transition from Excel to Access, two very
different beasts.

Regards

Kevin

 

[Jeff Boyce]

As others have pointed out, Access is a relational database. If you already
know/use Excel, I'm sorry!<g> You'll probably have a tougher time picking
up Access than someone without Excel experience!

If "relational" and "normalization" aren't familiar terms, plan to spend
some time brushing up on them -- Access' features/functions "expect"
well-normalized data, not 'sheet data.

If you are saying that you can have a security (with a symbol), and a
Price-on-a-date, then a 'tall/narrow' table could contain all the
information you need:

trelHistoricalPrices
SecurityID
PriceDate
Price

To handle new securities, you'd also want a:

tblSecurity
SecurityID
Symbol
SecurityName

?Add a new security? Add it to tblSecurity!

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.

 

[ryguy7272]

Yes, list format. I agree 100%. But, how will I match up my securities with
other data? I will need a PK, such as 1=MSFT, 2=SBUX, 3=IBM. However,
tomorrow, I may have totally different securities. Perhaps at the end of the
week, 1=BA, 2=GE, 3=CSCO 4=CAT, and 5=IBM. Should I just keep everything in
Excel? Seems like it would be easier that way.

Thanks!
Ryan---

 

[Jeff Boyce]

Why would you do that?

If MSFT has a primary key of 1 today, why would you change it? The whole
notion of a primary key is that it DOESN'T change.

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]

Yes, list format. I agree 100%. But, how will I match up my securities with
other data? I will need a PK, such as 1=MSFT, 2=SBUX, 3=IBM. However,
tomorrow, I may have totally different securities. Perhaps at the end of the
week, 1=BA, 2=GE, 3=CSCO 4=CAT, and 5=IBM.

Certainly not! The security code meets all the requirements of a Primary Key:
it's unique, it's stable, and it's short.

Create a table of Securities with a Text(12) - or whatever size is needed -
primary key. This may have thousands of rows, and would have one record per
security, e.g.

MSFT Microsoft
IBM Itty Bitty Machines

etcetera.

Your table of historical prices would be related one-to-many to this table,
with fields Security, PriceDate (Date/Time), Price.

 

[Arvin Meyer [MVP]]

Considering Stock Symbols are short and never change, they are one of the
few really good candidates for a natural key.

A table with the key might look like:

tblStocks
Symbol Primary Key
CompanyName Text
... other fields

and the data the OP is looking to store would be exactly like you pointed
out with the Symbol as a foreign key.

 

[Jeff Boyce]

Arvin

I'm not familiar with the stock market, but is there a chance that when
CompanyA gets bought out by CompanyB, or when CompanyC reorganizes and
renames itself CompanyD, that the stock symbol would change?

(inquiring minds want to know...)

Jeff B.

Considering Stock Symbols are short and never change, they are one of the
few really good candidates for a natural key.

A table with the key might look like:

tblStocks
Symbol Primary Key
CompanyName Text
... other fields

and the data the OP is looking to store would be exactly like you pointed
out with the Symbol as a foreign key.

 

[John W. Vinson]

I'm not familiar with the stock market, but is there a chance that when
CompanyA gets bought out by CompanyB, or when CompanyC reorganizes and
renames itself CompanyD, that the stock symbol would change?

(inquiring minds want to know...)

Yes... but it becomes a different stock. You can sell your apples and buy
oranges, but that doesn't make them into apples... <g>

 

[Steve]

Yes, and that's why it's a bad idea to use the stock symbol as a natural
primary key.

Steve

Arvin

I'm not familiar with the stock market, but is there a chance that when
CompanyA gets bought out by CompanyB, or when CompanyC reorganizes and
renames itself CompanyD, that the stock symbol would change?

(inquiring minds want to know...)

Jeff B.

 

[Clif McIrvin]

Yes, and that's why it's a bad idea to use the stock symbol as a
natural primary key.

I completely disagree. Stock symbols never change. When the company
changes, it is *necessary* to change the stock symbol, because the
company is no longer the same ... hence, a different stock symbol for a
different stock.

A very well behaved natural primary key, indeed.

Clif

 

[Arvin Meyer [MVP]]

Yes, but in each of those instances, it is in fact, a new company. And, if
it were not and all that changed was the Primary and Foreign keys, a
cascading update would properly change everything.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin

I'm not familiar with the stock market, but is there a chance that when
CompanyA gets bought out by CompanyB, or when CompanyC reorganizes and
renames itself CompanyD, that the stock symbol would change?

(inquiring minds want to know...)

Jeff B.

 

[Arvin Meyer [MVP]]

Sorry, that's incorrect. The stock symbol always changes. It IS a different
company. Even if you needed to change your records (That shouldn't happen)
you could do a cascading update.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

I built a small database to track my stock. It's not very sophisticated, but
it does the job. Here's the structure:

tblStockSymbols
StockSymbol Text
CompanyName Text
CUISP Text
IndustryID Long Integer
tblStock
StockID Autonumber
StockSymbol Text
AcquireDate Date/Time
AcquireCost Currency
ShareCount Long Integer
IndustryID Long Integer
RecommendID Long Integer
SellDate Date/Time
SellPrice Currency
Roth Boolean
tblCurrentStockPrices
DetailID Autonumber
Symbol Text
PriceDate Date/Time
Price Currency
tblIndustry
IndustryID Autonumber
IndustryName Text
tblRecommend
RecommendID Autonumber
RecommendedBy Text

The first field in each table is the Primary Key Subsequent fields with
matching names are Foreign keys.

 

[Jeff Boyce]

Thanks, John. I wasn't certain of the rationality of Wall Street...

Jeff B.

 

[David W. Fenton]

I wasn't certain of the rationality of Wall Street...

While the computer trading systems may be rationally organized, Wall
Street itself is clearly just Las Vegas/Atlantic City in lower
Manhattan. There is nothing rational at all about the way American
stock markets work -- they bear no relation whatsoever to the real
world and are only understandable in their own terms (and even that
is not really possible, I'd say).

I've been called in to do consulting and troubleshooting in quite a
few financial firms on Wall Street (literally) of all kinds, from
low-level penny stock firms to prestigious organizations that you've
actually heard of. I found uniformly that the people in the retail
end of it (i.e., the people pushing stocks) are uneducated in
general and vastly ignorant of the very stocks they are pushing (ask
me about the conversation about Artisoft I had with a broker at a
company with the initials SB c. 1995-96).

And did I mention the fistfights?

Or the Mafia influence on thinly-traded stocks?

It's a dirty, dirty business and the image that it's run by rocket
scientists is just completely bonkers.

The people at the top of the financial industry in the US may have
high IQs, but the are complete idiots. This was obvious to me 15
years ago when I was dealing with them directly. It should be clear
to everyone after the events of the last several years.

 

[ryguy7272]

Thanks to everyone who contributed to this discussion. I left this and
started a new one, to appear higher on the list, because some time had passed
before I could re-look at this and this thing got pushed down lower and
lower. Anyway. . . I think I have my answer: UnionQuery.
That should do it.

If anyone is still following this and has some follow up questions about
stocks, and the stock market in general, you can send me an email and I'll
try to answer all your questions. I have my MBA in finance and 10-years of
work experience in the financial sector. . .what’s left of it. . .
(e-mail address removed)