Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 815
  • Last Modified:

How to graphically represent query relationships within Access

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
daquino0
Asked:
daquino0
  • 5
  • 4
  • 2
  • +5
1 Solution
 
nico5038Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
MitchellVIICommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LenaWoodCommented:
How about doing a screen capture of the relationship window.
0
 
shanesuebsahakarnCommented:
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
 
MitchellVIICommented:
Thanks Shane, you do know everything :)

M
0
 
daquino0Author Commented:
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
 
daquino0Author Commented:
Nico5038  I believe you are on the right track -- when I select the Relationships for my query it only graphcially displays the query...



0
 
stengeljCommented:
"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
 
stengeljCommented:
"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
 
nico5038Commented:
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
 
Gustav BrockCIOCommented:
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
 
daquino0Author Commented:
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
 
stengeljCommented:
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
 
daquino0Author Commented:
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
 
stengeljCommented:
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
 
stengeljCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 5
  • 4
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now