October 19, 2004 at 4:24 am
can any 1 tell show me some some samples with ms-access joins
inner
left
right
any webpage links
well i mean such a simple query i wrote and it doesn't work on ms-access.
see
select a.col1,b.col1,a.col2,b.col2 from table1 a join table2 b on
a.col1=b.col1
what about left joins, and right joins, i just wanted a syntax summary, with some examples to get ideas
i searched in ms-access help couldn't find it, searched net, couldn't find it.
becoz iam extremly used to sql-server, ms-access is just taking time for simple queries.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
October 19, 2004 at 5:21 am
Easiest way to learn about MS-Access joins is to open up Access and create a query. Place both tables in the query and then from either table (I prefer the left-most) drag the field you want to join the tables on to the corresponding field in the other table.
Now you should see a line connecting the two tables. Right-click on the line (may take a few times to get it) and click "join properties". This will show a window that describes all three different joins available INNER, LEFT, RIGHT.
If you are curious to see the underlying SQL after you have played with the different joins click the drop-down arrow in the left-most item on the toolbar and click "SQL view".
The main difference between Access and SQL is the syntax to get used to and the fact that most development in Access for queries is graphical. I used to be Access for over 13 years and am now SQL and find it hard to go back to the training wheels. I am very used to just bring up QA and going.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
October 20, 2004 at 3:32 am
Access (Jet) SQL queries are basically the same as T-SQL, except that 'complex' queries won't work. Saying exactly what will and won't work isn't really possible.
In terms of syntax, you have encountered pretty much the only two differences between Jet SQL and T-SQL. When aliasing, Access requiresthe use of 'AS', whereas for T-SQL it is optional. Also, Access requires that you explicitly specify the join type, INNER, LEFT, or RIGHT. So just change
select a.col1,b.col1,a.col2,b.col2 from table1 a join table2 b on a.col1=b.col1
to
select a.col1,b.col1,a.col2,b.col2 from table1 AS a INNER join table2 AS b on a.col1=b.col1
and Access should be happy. Otherwise the syntax for joins is the same as in T-SQL.
October 21, 2004 at 9:40 am
As AJ Aherns suggests, start by creating queries in Access and then viewing them in "SQL View". This will show you how Access creates queries.
Be aware though that Access can not perform a FULL OUTER JOIN.
Also, when creating joins using the graphical interface, do not mix up which tables you are pulling fields from to create the join. For example, don't pull field1 from table A and connect to table B and then pull field2 from table B and connect to table A. Use the same direction with dragging/dropping in creating the join criteria, otherwise you will get something that looks like this in SQL View and it will not perform optimally...
Select ... From TableA INNER JOIN TableB
ON TableA.Field1 = TableB.Field1
AND TableB.Field2 = TableA.Field2
Notice how the sequence of the tables are flipped. This is all due to the direction that you drop and drag to create the join.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply