By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,599 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

How to Left Join (or outer join) a number of queries?

P: 58
I have the following query already working for me in Access:

Expand|Select|Wrap|Line Numbers
  1. SELECT qrycustnames2006.cusnum, qrycustnames2006.name, qrycustnames2007.name
  2. FROM qrycustnames2006 LEFT JOIN qrycustnames2007 ON qrycustnames2006.cusnum = qrycustnames2007.cusnum;
I would like to tie in two more queries that pull the results for 05 and 04 in the same tables. In the end I would like to see all data in each column regardless of whether or not an equivalent value exists in any other year. Can this be done? Would some kind of outer join be better? Any words of advise would be appreciated.

Thank you!
Jan 31 '08 #1
Share this Question
Share on Google+
6 Replies


Delerna
Expert 100+
P: 1,134
yes, left join returns all rows from the firs table but only records in the second table that are also in the first table

try FULL OUTER JOIN, I havn't used Access queries in a while but I think that will work.
I do all my queries in MS SQL Server these days and use access as a front end
Jan 31 '08 #2

P: 58
yes, left join returns all rows from the firs table but only records in the second table that are also in the first table

try FULL OUTER JOIN, I havn't used Access queries in a while but I think that will work.
I do all my queries in MS SQL Server these days and use access as a front end

My understanding is that Full Outer Joins don't work in Access. When I try it I get an error.
Feb 1 '08 #3

P: 58
Here my latest attempt:

Expand|Select|Wrap|Line Numbers
  1. SELECT customer.cusnum, qrycustnames2004.name, qrycustnames2005.name, qrycustnames2006.name, qrycustnames2007.name
  2. FROM (((customer LEFT JOIN qrycustnames2004 ON customer.cusnum = qrycustnames2004.cusnum) LEFT JOIN qrycustnames2005 ON customer.cusnum = qrycustnames2005.cusnum) LEFT JOIN qrycustnames2006 ON customer.cusnum = qrycustnames2006.cusnum) LEFT JOIN qrycustnames2007 ON customer.cusnum = qrycustnames2007.cusnum;
The only problem is that if there is no data in any of the columns other than the first I don't want the row to return. Is there a way to exclude that? If that doesn't make sense let me know and I will attempt a better description.
Feb 1 '08 #4

Delerna
Expert 100+
P: 1,134
is this what you mean
Expand|Select|Wrap|Line Numbers
  1. SELECT customer.cusnum, 
  2.        qrycustnames2004.name, qrycustnames2005.name, 
  3.        qrycustnames2006.name, qrycustnames2007.name
  4. FROM customer 
  5. LEFT JOIN qrycustnames2004 ON customer.cusnum = qrycustnames2004.cusnum) 
  6. LEFT JOIN qrycustnames2005 ON customer.cusnum = qrycustnames2005.cusnum) 
  7. LEFT JOIN qrycustnames2006 ON customer.cusnum = qrycustnames2006.cusnum) 
  8. LEFT JOIN qrycustnames2007 ON customer.cusnum = qrycustnames2007.cusnum
  9. WHERE qrycustnames2004.name is not null 
  10.   and qrycustnames2005.name is not null 
  11.   and qrycustnames2006.name is not null 
  12.   and qrycustnames2007.name is not null 
  13.  

I think I can see the purpose of this query now and if I am correct then a crosstab query would have been the simpler option. Did you consider using a crosstab or is there a reason for not going that way?
Feb 1 '08 #5

P: 58
is this what you mean
Expand|Select|Wrap|Line Numbers
  1. SELECT customer.cusnum, 
  2.        qrycustnames2004.name, qrycustnames2005.name, 
  3.        qrycustnames2006.name, qrycustnames2007.name
  4. FROM customer 
  5. LEFT JOIN qrycustnames2004 ON customer.cusnum = qrycustnames2004.cusnum) 
  6. LEFT JOIN qrycustnames2005 ON customer.cusnum = qrycustnames2005.cusnum) 
  7. LEFT JOIN qrycustnames2006 ON customer.cusnum = qrycustnames2006.cusnum) 
  8. LEFT JOIN qrycustnames2007 ON customer.cusnum = qrycustnames2007.cusnum
  9. WHERE qrycustnames2004.name is not null 
  10.   and qrycustnames2005.name is not null 
  11.   and qrycustnames2006.name is not null 
  12.   and qrycustnames2007.name is not null 
  13.  

I think I can see the purpose of this query now and if I am correct then a crosstab query would have been the simpler option. Did you consider using a crosstab or is there a reason for not going that way?
The urgency around this has passed. I pushed the dataset to Excel and sorted it by hand and was able to take care of the need. However, I am still interested for my learning and I appreciate your efforts in your responses.

In looking at the results that come from your suggestion above it only returns data where all four columns have data and this eliminates returns that were needed.

The only rows I want to exclude are those that don't have data in any of the four year columns. So if there is a way to modify the above for that I am still interested for learning sake. The select portion is grabbing the information I needed, its just also grabbing a lot of extra info.

As for a crosstab query, I don't know much about them. If you think it would work I am interested in learning about them. I have a book on them I could look into. Please tell me if you think I should pursue that.

Thanks again!
Feb 1 '08 #6

Delerna
Expert 100+
P: 1,134
Sorry, yes you are correct the query only returns the records where all 4 fields are not null. Try changing the AND's to OR's, that will return the records where any 1 or more of those fields is not null

As for crosstab queries, yes they are definitely worth the effort to learn. And they are not that difficult either. They are ideal for turning this
Expand|Select|Wrap|Line Numbers
  1. Cust Year Qty
  2.    1   2007  5
  3.    2   2007  8
  4.    1   2008  1
  5.    3   2008  9
  6.  
into this
Expand|Select|Wrap|Line Numbers
  1. Cust   2007      2008
  2.    1      5       1
  3.    2      8
  4.    3              9
  5.  
Feb 3 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.