ACCESS FEED: Re: [MS_AccessPros] Question on AutoNumber as Primary Key and Foreign Key on 2 large tables

Kamis, 19 Desember 2013

Re: [MS_AccessPros] Question on AutoNumber as Primary Key and Foreign Key on 2 large tables

Tim-

It's slow because your table design is wrong. You should not put the stock price in "Table 1". Table design should look something like this:

tblCompanies:

Ticker

CompanyName

.. other columns about company if necessary, like industry code.

Primary Key: Ticker

tblQuotes:

Ticker

QuoteDate

ClosingPrice

Primary Key: Ticker & QuoteDate

tblEPS:

Ticker

Year

CurrentEPS

ProjectedEPS

Primary Key: Ticker & Year

There should be 1-M relationships between tblCompanies and both tblQuotes and tblEPS on Ticker.

You should have to add rows daily only to tblQuotes - not reload it.

For "Ticker", are you using the Stock Symbol or the CUSIP? Both are relatively short. Using a text field Primary Key should impact performance only if you get in the neighborhood of 100,000 rows. Personally, I prefer to use a "real" Primary Key rather than something artificial like an AutoNumber.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Dec 19, 2013, at 3:09 PM, <timdbui@gmail.com> <timdbui@gmail.com> wrote:

Would any of you please educate me on this issue?

I have two tables in Access. Each table contains about 3000 rows and 100 columns. Currently the primary key for each table is the Stock Tickers.

For example, within Table 1, column A is Ticker, col B is Company Name, col C is stock price,....
Within Table 2, col A is Ticker, col B is Current Year EPS, col C is Next Year EPS.....

A friend told me that since I use the tickers (text) as primary key for both tables, my queries will by very slow, which they are. He suggested that I use Autonumber as primary key in table 1 and as foreign key in table 2.

My question are:

Since I have to delete Table 1 weekly and append it with new data, would the auto number in table 1 be "messed up" after each delete/append?

Secondly, if I use Autonumber in Table 1, how can I set up the foreign key in table 2 to make sure it matches the same numbers in table 1?

If any of you help to answer or send me where to study this subject, I would be much appreciative!

Thanks!
Tim