Solved

I don't understand where I would add this. HELP!

Posted on 2004-10-09
10
302 Views
Last Modified: 2012-08-14
State

StateAbbrev   StateName     EnteredUnionOrder     StateBird      StatePopulation
    CT           Connecticut      5               American robin          7,458,116
    MI           Michigan             25                           Robin              10,229.277
    SD          South Dakota        40                 Pheasant              722,007
    TN           Tennessee           16               Mockingbird           4,999,002
    TX              Texas      23               Mockingbird          17,777.999

Capital

CapitalName     StateAbbrev  YearDesignated  PhoneAreaCode      CapitalPopulation
    Austin                   TX      1846                 627             777,222
  Hardford                   CT      1662                 899             139,999
   Lansing                   MI      1847                 517             122,332
  Nashville                   TN      1843                 615             477,388
    Pierre                   SD      1889`                 604               12,999

I need to add the attribute CountyName for the county or counties containing the state capital. I don’t understand how I could do this. The Primary Key for State is StateAbbrev and the Primary Key for Capital is CapitalName. The Foreign Key for Capital is StateAbbrev.
0
Comment
Question by:Potchitka
  • 5
  • 5
10 Comments
 

Author Comment

by:Potchitka
ID: 12268579
Clarification: I need to either add it to the Capital relation, the State relation or create a new relation. What should I do?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12268614
Are you trying to keep an histrorical record of the state capitals- that is, are there any states in your Capitals table that have 2 capitals?  If not, then 1) why are you keeping two separate tables, since each state in the State table has EXACTLY 1 record in the Capitals Table (making this a one-to-one relations)

2) assuming that you have a good reason for keep the two tables separate, why don't you simply add a field in the Capital table to hold the County of the Capital (are there any instances of a State capital belonging to two counties?)

AW
0
 

Author Comment

by:Potchitka
ID: 12268667
Arthur,

Thank you for responding so quickly. I would have to assume that the database will eventually store every county in the U.S., not just the counties that have state capitals. So, with this in mind, would it be better to create a new relation, add the CountyName attribute to it as the primary key and StateAbbrev as the foreign key? If so, why?

0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12268694
If that is the case, the  you would need a StateCounty Table to hold the StateAbbrev, CountyID, and CountName for each and every County in each and every state (that table should be available from some source, so that you do not need to create it yourself, as there are some 4800+ counties in the entire United States, and I doubt that you want to have to key in each entry.

Then you would add a field, in the Capital table to hold the CountyID of the County where the capital is located - since you already have the StateAbbrev in the Capital table, thus you can locate the County record in the StateCounty Table.

what do you mean by "If so, why?" you just answered that question.

AW
0
 

Author Comment

by:Potchitka
ID: 12268796
AW,

Your answers make perfect sense, but I still don't know why creating a new relation is needed. Is that because the new attribute needs a separate relation and should not be added to either of the other relations? If this is so, then why? Why is it important to add a new relation? I must say that I am very new to MS-Access. Your help is very much appreciated. I promise that this is my last question.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
ID: 12271018
the 'new relation' as you call it (truly it is a new TABLE), is to allow you to have a list of ALL of the COUNTIES, in ALL of the states, and then simply add a field, in your Capitals table, to link to the SPECIFIC county record, in that new table, to identify which SPECIFIC record was the one that was tied to the Capital of that state.

If you are NOT going to have a list of all of the counties in each state, then, as I indicated, all you really need is to add a CountyName field to the Capitals tabel, and then simply have the CoutyName in the Capital table, without the need for another table.  It all depends on the intended application, and what you are trying to accomplish.  You can't just make things up as you go along.  It usually requires some planning, and a design of what it is you are attempting to accomplish.  In a formal development process, those are refered to as REQUIREMENTS - what is the application SUPPOSED to do, what is it going to be used for?

AW
0
 

Author Comment

by:Potchitka
ID: 12305329
From this I wonder how I would be able to tell which counties have capitals in them?

State (StateAbbrev, StateName, EnteredUnionOrder, StateBird, StatePopulation)
Capital (CapitalName, CountyID, StateAbbrev, YearDesignated, PhoneAreaCode,
   CapitalPopulation)
   Foreign key: StateAbbrev to State relation
StateCounty (CountyID, StateAbbrev, CountyName)
   Forengn key: StateAbbrev to State relation
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12313128
The CountyID field in the Capitals table identifies, for each State Capital, the county of that city.

CountyID in the Capital Table is the Foreign Key for the StateCounty Table

AW
0
 

Author Comment

by:Potchitka
ID: 12328721
Now it makes sense. Thanks AW!
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12330457
glad to be of assistance.

AW
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now