Improve company productivity with a Business Account.Sign Up

x
?
Solved

Microsoft Database table relationship question

Posted on 2009-04-03
18
Medium Priority
?
3,138 Views
Last Modified: 2012-05-06
I have the tables and relationships shown in the attached screenshot.
I simply want to replace the "Opportunities" table with the "keystoneopportunities" table.
I want all the new relationships to match how they were for the "Opportunities" table.
I'm new to MS Access and i'm having trouble doing this.
Hoping someone can walk me through this.  I have Access 2007.
DB-relationship.gif
0
Comment
Question by:GTC-KTX
18 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 24060275
Why is the table name so significant?

Rename the exsiting table if you feel it is necessary. Based on what we can see the fields are the same.
0
 

Author Comment

by:GTC-KTX
ID: 24060306
The new table has many additional fields.  Will renaming it still work in that case?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24060307
if you are not certain of what you are doing, Create a backup copy of your DB

in the Realationship,
create the same relationship of the tables Customers, Employees, and DocAttachments to table keystoneopportunities",
when done, delete the table Opportunities
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24060314
renaming the table will break the relationship
0
 

Author Comment

by:GTC-KTX
ID: 24060549
so i rename the table "opportunity to something else, just to break the relationships it has.
then i establish those same relationships with the KeystoneOpportunities table.
I know its propbably a stupid question, but i don't know how to rename the table.
Also, to establish those same relationships with the new table, i assume that i go to the database tools tab, and then choose relationships.  But this choice is grey'd out (wont let me pick it).  Any suggestions?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24060618
you have to select Tables from the Navigation pane.. then click on the relationship icon

how did you get the image of the relationship posted above?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24060636
did you see my comment at http:#a24060307
0
 

Author Comment

by:GTC-KTX
ID: 24061234
Thanks for the post capricorn.....yes i did see your comment, and admittedly i'm struggling with this a bit.
I'm not sure why the relationship option was initially grey'd out, but i have it open now.  So i now have the same view that i posted in the image.  I see from your post that i have to break the relationships to the opportunities DB.  You say that renaming will break the relationship.  I'm having trouble figuring out how to rename.  I also seem to have added a relationship from my keystoneopportunities to the customer table, but it doesn't have the "one to many" designation (i think thats what it is).  I attached another screenshot.
DBrelationship3.gif
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24061363
double click on the link (the line that connects the table) that you created to edit the relationship.
0
 

Author Comment

by:GTC-KTX
ID: 24061531
i can get there to edit this new relationship.
There are 3 choices for join type.
1.  only incl rows where joined fileds from both are equal.
2.  Incl ALL records from 'Customers' and only those records from 'Keystoneopportunities' where joined fields are equal.
3.  Include ALL records from 'Keystoneopportunities' and only those records from 'Customers' where the joined fileds are equal.

would it be the 3rd choice?

Also, can you help me rename the Opportunities table so i can break the relationships to that table?
I thought i'd be able to find a place to rename it in the properties, but can't seem to find a place to do that.

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 24061574
GTC-KTX,

view first the link that is connected to table "opportunities" and take note of the setting,


<Also, can you help me rename the Opportunities table so i can break the relationships to that table?>

do it later when you are done creating the relationship with 'Keystoneopportunities'
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24061637

GTC-KTX,,

I don't think you are viewing the table Relationship of your database.
If i am not mistaken you are looking at a Query  designer window
0
 

Author Comment

by:GTC-KTX
ID: 24061721
OK.  I think i have all the relationships the same.  The only thing i'm not sure about is that i don't have the sideways 8 symbol (the many symbol?) on my new relationships, even though the settings appear to be the same.  I've attached another screen shot.
relationship4.gif
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24061776
please read my last post..
0
 

Author Comment

by:GTC-KTX
ID: 24061846
I did readit and i checked.  It says "Relationships"  for the tab that i am on.  Also, I hit "Edit Relationships" on the design tab to establish these new links.  I then hit "creat new", and then edited the "join type"
0
 
LVL 5

Assisted Solution

by:Mike77
Mike77 earned 1000 total points
ID: 24062565
To have the one to many symbols, you have to activate the referencial integrity option by checking the corresponding checkboxes in the edit relationship window.

Also, renaming the table does not delete the relationship (i have access 2003 but i don't think that this has changed in 2007, correct me if i'm wrong), it will just make the table dissapear from the relationship window. It can be re-displayed by pressing the "show all relationships" button of the toolbar (always with access 2003 ... this may have changed). To delete a relationship, you have to click on the relation line and press the delete key on your keyboard.

Hope it helps ...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24062659
yes, Mike is right, just checked it.
0
 

Author Closing Comment

by:GTC-KTX
ID: 31566251
thanks guys.  appreciate the help.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

584 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