Solved

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

Posted on 2004-10-09
10
311 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

932 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

11 Experts available now in Live!

Get 1:1 Help Now