No Lookups in a Table

[oleBucky]

I have seen the light and no longer use Lookups in tables. However, this brings up an issue only solved by experience of which I have very little.

I have a table representing four different situations on each of 25 different occurrences. Each of these 100 alternatives consists of a symbol and a location (that’s right (200 fields + 2 ID fields). The location is an integer (1-9) and the symbol is one of 35 text options. It is my goal to eliminate typos on data entry by creating a relationship between a table holding symbols and a table with the occurrence fields for use on a data entry form. Therein lies the rub.

As you can see on the screen capture of my relationships, I’ve created a table holding the 35 symbol options (tblSymbols). A much larger table holds the 100 alternatives (tblPlayersHitting). When I started creating the relationships between tblPlayersHitting and tblSymbols, Access created “pseudotables” called tblSymbol_1, tblSymbol_2, tblSymbol_3, etc. I call them pseudotables (I don’t know the correct term) as they exist in the relationships but not as actual tables listed with the other tables.



My specific question is “Is this the appropriate and best approach?” Clearly, the relationship window will be very cluttered should I go ahead and add the other relationships for all the symbols on all the occurrences. To throw fuel on the fire, the db will eventually have a “tblPlayersPitching” with the same number of relationships with tblSymbols as tblPlayersHitting.

[RuralGuy]

You do *not* want more than one table to have the same PrimaryKey. You can put the Primarykey in another table as a ForeignKey but that 2nd table will have its own PrimaryKey.

[oleBucky]

Thanks for the heads-up. I would have gone on blissfully until something detonated! I think I fixed the Key Field issue in tblPlayersHitting and the tblRateImport is a temporary table I used with an update query. I am assuming when I get rid of that table, the duplicate Primary Key will go away.

Is it necessary to have a Key Field in a Junction Table?

[RuralGuy]

Is it necessary to have a Key Field in a Junction Table?

The simple answer is NO since it already contains two PrimaryKeys.

[oleBucky]

Also, what is a best practices policy regarding data type for Primary Keys? I think if you let Access defaults play, Access uses AutoNumber as a long integer. This presents a problem on enforcing referential integrity in relationships due to different data types if, like I have, the user defines their own primary key as a Number data type, not an AutoNumber.

[oleBucky]

Come on guy. Throw me a bone here!

I'm a newbie and am simply looking for some general advise on db design. I am not looking for detailed solutions, simply directional guidance from those more experienced than I.

How best do I go about tackling the problems as noted in the original post? I will in no way be offended if what I am doing is dumb and someone tells me so.

[orange]

See
http://www.rogersaccesslibrary.com/forum/topic238.html
see the section on
Unique Indexes and Surrogate Keys
http://www.rogersaccesslibrary.com/T...ate%20Keys.doc

[RuralGuy]

Typically *you* would define the tables and their structure including Primary and Foreign Keys. I am not in favor of letting a user do that without them also having a complete copy of Access and defining the rest of the system as well. Sorry.

[oleBucky]

I am not in favor of letting a user do that without them also having a complete copy of Access and defining the rest of the system as well.

Might you expound on your statement. Specifically, what is a complete copy of Access (versus an incomplete copy) and what do you mean by "defining the rest of the system"?

[RuralGuy]

Might you expound on your statement. Specifically, what is a complete copy of Access (versus an incomplete copy) and what do you mean by "defining the rest of the system"?

Basically what I am saying is a developer defines what you are describing and not the end user. It is *your* system after all so you can let the end user do anything you want them to do. Enjoy.