UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Join Properties    
 
   
AC2Designs
post Dec 8 2006, 03:11 PM
Post#1



Posts: 1,048
Joined: 17-December 08
From: Fort Worth, Texas


I've read what Access Help has to say about Joins, but I still do not quite understand. And to further confuse me it seems like you can set one Join Type in your Relationships, and yet another when creating Queries.
The actual descriptions make sense, but to what extent? In other words, I can set my join type to #2 (All rows from first table and only rows in 2nd table that are equal); however if I do a SQL query I have access to everything in each table.
I just reread my post above and now I'm even more confused. sad.gif I guess I just need someone to explain the in and outs of Join Types, and maybe some good reading material on them..
Thanks,
Go to the top of the page
 
adamsherring
post Dec 8 2006, 03:17 PM
Post#2


VIP Emeritus
Posts: 1,750
Joined: 16-June 05
From: The Great White North


Hello,
Join is telling Access how to group and connect data from different tables. The basic syntax is ____ JOIN tableName ON tableaname.ColumnName = anotherTablename.ColumnName
where ____ is the join type. The join type tells access how to include/exlude records. Access supports INNER, LEFT, and RIGHT joins (there are other types I believe access doesnt support). An Inner join will only return records that are equal in both tables (Records that contain matching values in the Columname arguement), a LEFT join will return all records in the first table, but only matching records in the second, and a right join will do the same, but with the sides reveresed.
Setting the join type in your relationships window will tell access what join to use by default. You can always go in and change it in the query screen, or in the SQL. The first thing you need to learn about JOIN's is normalization. Once properly normalized, a database can make a much fuller use of JOIN's. The next step is to look at your two tables, and ask what you want returned, then to use the proper join to accomplish that.
I hope I havent confused you too much,
Adam
Go to the top of the page
 
ScottGem
post Dec 8 2006, 03:17 PM
Post#3


UtterAccess VIP / UA Clown
Posts: 32,217
Joined: 21-January 04
From: LI, NY


An Inner join only returns records where the key fields match. That is the first option. An Outer join returns all records from one side and just the matching records from the other side. Those are the other two option.
ets give some examples.
Scenario A. You have and employee database and want a list of employees that have taken Health coverage 1. You set up an inner join between the employee table and the coverages table filtering for coverage 1
Scenario B. You want a list of all employees and what coverage they chose even if they didn't choose any. You set up an outer join select all employees but only matching records from the coverage table. You would then add the Coverage Type from the coverage table.
Go to the top of the page
 
AC2Designs
post Dec 8 2006, 03:52 PM
Post#4



Posts: 1,048
Joined: 17-December 08
From: Fort Worth, Texas


Does it matter then if I set them up during the initial Relationship Join process? I ask because when I create Queries I can change it, and also if I use SQL statements in code I can verify exactly what I want and how I want it. So it seems like I really should not bother with them until setting up Queries in Access.
Go to the top of the page
 
ScottGem
post Dec 8 2006, 04:05 PM
Post#5


UtterAccess VIP / UA Clown
Posts: 32,217
Joined: 21-January 04
From: LI, NY


Relations and joins are similar but different. Relations are generally set up as matches. The only reason to formalize relations is so you can impose referential integrity.
You never know when you will need an inner or outer join in a query.
Go to the top of the page
 
btalada
post Jul 29 2019, 07:28 AM
Post#6



Posts: 1
Joined: 29-July 19



The "Join Properties" dialog box has frustrated me for decades but I finally figured it out today. It mislead me because it pops up from what I thought was defining foreign keys, with the referential integrity options and all. You can create joins on most anything without enforcing constraints! This is super power that I never expected. Basically, Access lets you define all the potential joins ahead of time that you will likely ever use. I have never seen anyone use this feature. Just think how many apps have tables with user id columns that are not foreign keyed. With this feature you can pre-design your queries to pull in a username.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd October 2019 - 05:53 AM