?
Solved

How to graphically represent query relationships within Access

Posted on 2005-03-24
17
Medium Priority
?
799 Views
Last Modified: 2008-02-01
I would like to be able to extract the graphical [design view] of a query to generate an ERD but I do not see the capability within Access.  The Visio reverse engineering does not display the joins of the query it just shows the tables and fields -- and the answers to similar questions within EE do not really tell me how to accomplish this either in Access or Visio.

0
Comment
Question by:daquino0
[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
  • 5
  • 4
  • 2
  • +5
17 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 13627065
Normally you use the RelationShip window (See button when you have the database window active) to define the relations between tables.
Queries can be added when you want.

Need more info ?

Nic;o)
0
 
LVL 85
ID: 13627074
If you're talking about graphically representing a query, I don't think Visio can do this. Beside, the graphical representation is simply a drawing; Vision would have to be able to parse the Table names and determine relationships. I know that only certain versions of Visio can do things like this (I use the Professional version and haven't yet explored all the features) but couldn't comment on whether this is even doable.

You might check Total Access Analyzer from www.fmsinc.com ... I know it gives the option to build diagrams, but I'm not sure this addresses your problem.
0
 
LVL 5

Expert Comment

by:MitchellVII
ID: 13627080
I don'y know what an ERD is, however, I don't believe that what you are seeking to do is feasible.  I can't imagine what use this could be.  If you are simply looking to save a visual representation of how the Query looks, just click 'Print Screen' on your keyboard, then paste the capture into your picture program and create a .bmp.

BTW, future reference, when using acronyms like ERD it is usually a good idea to explain what that is so the people helping have a better frame of reference.

M
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:LenaWood
ID: 13627081
How about doing a screen capture of the relationship window.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13627185
ERD = Entity Relationship Diagram

Briefly mentioned in the one semester module that I ever took on databases ten years ago...!
http://folkworm.ceri.memphis.edu/ew/SCHEMA_DOC/comparison/erd.htm
0
 
LVL 5

Expert Comment

by:MitchellVII
ID: 13627226
Thanks Shane, you do know everything :)

M
0
 

Author Comment

by:daquino0
ID: 13627295
Relationships within MS Access is the closest thing to what I am looking for with respect to graphically documenting the relationship between tables in a given query.
Visio has a reverse engineering feature that links to MS Access but the relationships [joins] don't come along for the ride.
Also, Analyze / Documenter has detailed data but not a graphical representation...


0
 

Author Comment

by:daquino0
ID: 13627322
Nico5038  I believe you are on the right track -- when I select the Relationships for my query it only graphcially displays the query...



0
 
LVL 9

Expert Comment

by:stengelj
ID: 13627816
"I know that only certain versions of Visio can do things like this ..." -LSMConsulting

I'll confirm this and mention that it is very slick. It is way better than anything you'll get out of the Relationships screen in access.  

Here's some destructions: http://office.microsoft.com/en-us/assistance/HP010181781033.aspx

They're for 2003 but I know I've done it in previous versions using the same instructions.
0
 
LVL 9

Expert Comment

by:stengelj
ID: 13627825
"The Visio reverse engineering does not display the joins of the query..."

Yes it does.  But, you may have to do it manually after the initial import.  I could swear you can set it up to automatically show the type of joins prior to the import but I don't have the program anymore so I can say this for sure.  Look for something about "crow's feet".  Here's a little more: http://office.microsoft.com/en-us/assistance/HP895500971033.aspx
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13628380
The "normal" way to get relations "visible" in queries is to start with the definition of the relations (and their properties like referential integrety/cascading update/etc) and then the graphical query editor will show these relations automatically as a join when placing two related tables.

When there's no relation Access will try to propose a JOIN on fields with e.g. ID in their name.

But it looks to me that you have queries and want to see the relations. This will have to be in the graphical query editor and can only be for one query at a time. Besides that the relation will only show when a JOIN has been defined.
When the query has two tables and realizes the JOIN by a "WHERE table1.key1 = table2.key1", there will be no relation visible...

I guess that when you try to create an ERD from query information you'll have to check them for JOIN's, but also the above mentioned "WHERE construction" and place the relation in the relationship diagram manually...

Nic;o)
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 13628935
Here is the direct method:

  http://www.lebans.com/windowtobitmap.htm

" .. a class to allow you to capture and save the entire contents of the Relationship window to a disk based Bitmap file. This is roughly a 4800 x 4800 pixel window. The Bitmap is saved as a 16 color BMP in order to reduce file size."

/gustav
0
 

Author Comment

by:daquino0
ID: 13631114
stenglj  -- thank you very much for your posts...

I feel like I am very close -- I have VISIO Pro 2002. I read the link that you provided and I created a "simple query" between (2) tables with (1) join.

I did the following:

Database > Reverse Engineer >selected my database> selected all of the objects to reverse engineer>selected all the tables and query > and then said place the tables and queries on the page...

I also set the database > document options > to crows feet

The end result is the view structure and the table structure but no graphical relationships between fields.  

0
 
LVL 9

Expert Comment

by:stengelj
ID: 13631168
Can you explain what you mean by "no graphical relationships between fields"?  

Maybe I'm missunderstanding what you are looking for.  
I am envisioning somthing like: http://www.utexas.edu/courses/mis325/tutorial/visio/visio31.gif
0
 

Author Comment

by:daquino0
ID: 13631181
that is exactly what I am looking for... the tables show up but the line that links tthe 2 fields between the tables within the query are not present...
0
 
LVL 9

Expert Comment

by:stengelj
ID: 13631203
Does this page help?  About half way down it discusses setting up relationships and annotations.

http://www.utexas.edu/courses/mis325/tutorial/visio/visio.htm
0
 
LVL 9

Accepted Solution

by:
stengelj earned 2000 total points
ID: 13631233
Something else...I wish I had Viso running on this machine so that I could confirm but...I think that is you only have the default relationships setup in Accees (i.e., you have not defined relationship type, referential integrity, etc.), Visio does not display the relationships automatically on a reverse engineer.  You would have to set them up manually.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

752 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