Full outer join issue

[FloggingDolphin]

MS Access is part of Microsoft Office and is probably installed on your computer. But, beside the point, I am amazed how many professional people resort to half way database like Access instead of working with real thing like SQL.

Access designed for very small businesses or students. Not sure how big it became in professional world. I assume it is European thing. I never heard of this wide use like on this site really… :)

hahaha, well we use MS Access sometimes to do quick and dirty queries. Problem is...I just read that Access doesn't have FULL OUTER JOINS in it's GUI. However, apparently there is still a way to get it with some extra SQL.

Anyway, I've been trying that ISNULL test. At first it side invalid identifier "ISNULL", so I tried IS NULL (with a space between) and then it kept saying about a missing expression. Sigh, I have a feeling it doesn't like ISNULL.

I can't try that test you gave me on its own because Agresso excellerator always requests that you specificy at least two columns ie. after the query we actually add the lines "query","column s (followed by the columns to display in each excel cell that were chosen in the SELECT statement) and then "query" or "group (column)" depending on your preference.

will keep trying...
Mark

[iburyak]

hahaha, well we use MS Access sometimes to do quick and dirty queries. Problem is...I just read that Access doesn't have FULL OUTER JOINS in it's GUI. However, apparently there is still a way to get it with some extra SQL.

Anyway, I've been trying that ISNULL test. At first it side invalid identifier "ISNULL", so I tried IS NULL (with a space between) and then it kept saying about a missing expression. Sigh, I have a feeling it doesn't like ISNULL.

I can't try that test you gave me on its own because Agresso excellerator always requests that you specificy at least two columns ie. after the query we actually add the lines "query","column s (followed by the columns to display in each excel cell that were chosen in the SELECT statement) and then "query" or "group (column)" depending on your preference.

will keep trying...
Mark

Did you try to execute my example in Access?
[PHP]
select null + 5 Sum_NULL, isnull(null, 0) + 5 Sum_With_Isnull [/PHP]


To have full join in access you should do following:

[PHP]select ...
from table1 left join table2 on .....
UNION
Select .....
from table1 right join table2[/PHP]

Good Luck.

[FloggingDolphin]

Did you try to execute my example in Access?
[PHP]
select null + 5 Sum_NULL, isnull(null, 0) + 5 Sum_With_Isnull [/PHP]


To have full join in access you should do following:

[PHP]select ...
from table1 left join table2 on .....
UNION
Select .....
from table1 right join table2[/PHP]

Good Luck.

Hi

Thanks for the fast reply. I couldn't test Access yet as it seems it's not installed on my PC. Due to some irritating red tape and licensing I have to wait for IT to get it for me.

I'll be sure to keep trying a way to get ISNULL to work. There must be a way for it to show two values from different tables side by side where sometimes one table has an employee but the other one does not.

kind regards,
FD

[iburyak]

I never saw such behavior.

Can you give me sample data and exact query you execute and result you get?
I don't think it should be an issue as you described.

I assume you do have access to SQL server.

[FloggingDolphin]

I never saw such behavior.

Can you give me sample data and exact query you execute and result you get?
I don't think it should be an issue as you described.

I assume you do have access to SQL server.

Hi,

Sure I can send you the data (with modified figures to prevent breach of confidentiality ). How would you like to receive them? In an Excel sheet or...?

Anyway, if all else fails, how would I do a query to show me all the employee numbers in the transactions of both tables (ie. in order smallest to biggest) without repeating themselves?

thanks again,
FD

[iburyak]

Send me a Private Message.
Or just show me couple of numbers you have, query you execute, result you get and desired result.