Solved

create relationship from table to view

Posted on 2013-06-23
8
548 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Report Expression 4 27
Save Selections in MS Access 3 31
Turning off query prompts on runtime version of Access 2016 5 18
vb.net dbnull syntax 1 9
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

730 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