Solved

create relationship from table to view

Posted on 2013-06-23
8
525 Views
Last Modified: 2013-06-25
hey guys i'm just curious. in the relationships table i can add both tables and queries to the canvass.

1) can i create a relationship from a query to a table or i can't, it's just there for me to visually see the select query?

(i tried creating a relationship from the select query to the table but the below is what it shows me)

2) i've heard about views. is a select query a view? so all my select queries in access are views right? if they're not, what's the difference between a view and a select query? thanks!

viewtotable
0
Comment
Question by:developingprogrammer
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39269245
Yes,  you can create a relationship between a query and a table (in the query designer or SQL view).  When you try to create the relationship and double click on the line joining the two tables, it will display the table/query names at the top and the fields that are currently linked in the section above the check boxes.

A "view" is the term that SQL Server uses for saved queries, it is not usually a term associated with Access queries.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 50 total points
ID: 39269259
The difference is that when using a query, the relationsship is just informal and for display only - that why the three textboxes are greyed out.

/gustav
0
 

Author Comment

by:developingprogrammer
ID: 39270253
hrmm ok let me try and develop the queries for my DB and see if i need relationships and as such would have real test cases to better understand this. but thanks for your help gustav and fyed!! let me get back to yall in awhile ya! = )
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39270926
When you are building table relationships, it is generally to ensure some form of referential integrity (ie, you must have a matching record in one table for an entry in another table).  An example of this would be a States table and an Address table.  With a properly defined relationship, you would not be able to enter ZZ as a [StateCD] field in tblAddresses if there is not a matching [StatesCD] value in tblStates.

However, when a relationship is defined in a query, it is normally so that you can retrieve other information from a second table, based on the value in the first table.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 11

Accepted Solution

by:
datAdrenaline earned 350 total points
ID: 39272362
"Yes,  you can create a relationship between a query and a table"

You are not creating a Relationship in this scenario.  You are merely defining how you want the AutoJoin feature of the Access UI will JOIN when using both the Query object and Table object in another Query object.

Remember, a Relationship (with R/I, of course) is a constraint that governs data going into the database must adhere to.  A JOIN is how the data is presented to the user.  A "Relationship" without R/I is, again, a simply a suggested JOIN for the AutoJoin feature.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 39272639
datAdrenaline,

Your are, of course, correct about the difference between a "relationship" and "join".  I stand corrected, and should have used the word JOIN in both my earlier posts when referring to queries.

Dale
0
 

Author Closing Comment

by:developingprogrammer
ID: 39272682
whao guys, thanks so much for your help and guidance and the culture here is really humbling and wonderful seeing how we all learn from each other and progress along together. thanks guys once again and yall are really wonderful!! = )
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 39275022
You're welcome!  Good luck on your project!
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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

911 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

21 Experts available now in Live!

Get 1:1 Help Now