Right JOIN Problems

Hi,
being still relative novice in Access I live with great daily surprises. Here is one that has left my head shaking. I have a relatively simple query which is to serve as Bank Deposit control sheet. The database manages condo data, and the payments come from two sources (owners paying their fees, and concessions paying theirs).

I have run into an unexpected problem. Two of the fields in the query are calculated based on the value in the "payments type". It should be fairly simple. As the payment records are one of two kinds the account numbers of the not-selected fields are going to be NULLs. For the world of me I can't seem to be able to get this right The SQL generated for the attached QBE looks like this:



Not surprisingly, there were no records displayed. Payments by owners were discarded with NULLs on Owner's accounts and likewise with Concessions.
I played with the design, trying a different sequence but nothing seemed to work. Adding the Concession records killed the query. I almost got there with a RIGHT JOIN on Concessions (there is only a couple of of records in the set), and voila it displayed all the rows with Owners, ....alas no Concession records. Pardon me ? WTF ! I did some digging and found out that the MS SQL (which I suspect is related to the Access variety of SQL) does not allow RIGHT JOINS except nested. Ok, I placed the RJOIN for Concessions on the inside of the SQL string but then I got told that this type of JOIN is not supported ! Wow !

Would any of the esteemed SQL experts here know how to write the string in Access so both type of rows are selected ? Much obliged !

Best,
Jiri

Hello Solo712, With your Screen Shot I am unable to determine what/where the exact problem is or might be.. If incase you could upload a stripped down (falsified records) version of your DB or provide some sample data of how it is at the moment and what is the result you wish to see. Help might be available a bit sooner..

Having said that, let me just point some basics.. JOINS might be a bit tricky..
* INNER Join will display records when the fields are equal.
* LEFT Join will include all data from the table on the Left side of the join even if there is no match on the other table
* RIGHT Join will include all data from the table on the Right side of the join even if there is no match found on the other table.

What you need is a FULL Join.. But MS Access being MS Access does not support FULL join, but does not mean that it cannot be achieved.. FULL join can be mimicked in other words Data of RIGHT join and Data of LEFT Join put together.. So to achieve this design a Query that will have the Right Join and another for Left Join and use a UNION Query to merge them into one..

If this is not what you are looking for as explained earlier, try uploading a dummy version of your DB with fake data and the result you wish to see..

pr2-eugin said:

Hello Solo712, With your Screen Shot I am unable to determine what/where the exact problem is or might be.. If incase you could upload a stripped down (falsified records) version of your DB or provide some sample data of how it is at the moment and what is the result you wish to see. Help might be available a bit sooner..

Having said that, let me just point some basics.. JOINS might be a bit tricky..
* INNER Join will display records when the fields are equal.
* LEFT Join will include all data from the table on the Left side of the join even if there is no match on the other table
* RIGHT Join will include all data from the table on the Right side of the join even if there is no match found on the other table.

What you need is a FULL Join.. But MS Access being MS Access does not support FULL join, but does not mean that it cannot be achieved.. FULL join can be mimicked in other words Data of RIGHT join and Data of LEFT Join put together.. So to achieve this design a Query that will have the Right Join and another for Left Join and use a UNION Query to merge them into one..

If this is not what you are looking for as explained earlier, try uploading a dummy version of your DB with fake data and the result you wish to see..

Click to expand...

Hi pr2-eugin,
thanks for replying. I do have some background in SQL, enough in fact to know that RIGHT JOIN was needed to fix the situation. Normally, (with T- or P-SQL) I would have expected the SELECT statement to work by simply making the first 'Concession' a RIGHT JOIN. It did not work. The baffling result was that all the non-Concession rows were included but Concession ones were not......why would that be: this is not an expected result of a RIGHT JOIN ? I guess, throwing this out on the Forum was to find how the joins ( or don't work) work in Access. Why e.g. would Access allow RIGHT JOIN in the outer filter but not inside the nested ones ?

Generically, my problem is as follows: I am creating a query for Bank Deposits. There are two table sources of payments for Deposits, one by Condo Owners, the other by what I call 'Concessions', i.e. monetary sources other than condo fees. I need the name of the payee in the query field irrespective of which of the two situations arises. I have a workaround for this, a field defined by a selection of a built-in function, sth like:

IIf([tblPmtsRcvType]="Condo Fees and Services", GetOwnerName([tblPmtsRcvOAcct]), GetConcessionName([tblPmtsRcvCAcct])).

But I would like to do this with a SELECT statement (for greater portability). Any further help in understanding Access SQL rules would be appreciated.

Best,
Jiri

Access Queries are more likely to be the same as the traditional SQL with a slight variation of character usage and some functionality unavailability.. Other than that it would work the same..

Again is it possible to show some sample data/upload a stripped down version of your DB? It really will help us understand a lot better..

pr2-eugin said:

Access Queries are more likely to be the same as the traditional SQL with a slight variation of character usage and some functionality unavailability.. Other than that it would work the same..

Again is it possible to show some sample data/upload a stripped down version of your DB? It really will help us understand a lot better..

Click to expand...

I will not be sending out samples of my database beyond what I have shown. I think I have explained the problem clearly enough to get an answer from someone who has expertise in the subject. I reckon this to be a fairly trivial issue. Thanks again for your interest, pr2-eugin.

Jiri