Help with right join

[usa_dreamer2002]

Hi all,
I have something similar to the following:

TABLE1
SVC CODE
001 HV
002 CH
003 9Q
007 NZ
...

TABLE2
SVC LOCATION
001 SW


002 NE
007 NE
...

im looking for something like this:

SVC CODE LOCATION
001 HV SW
002 NE NE
003 9Q NULL
007 NZ NE

I tried the following query and svc 003 is always excluded.

SELECT SVC, CODE, LOCATION
FROM TABLE1 RIGHT JOIN TABLE2
ON TABLE1.SVC = TABLE2.SVC

tried left, right joins and same result. Can any one give me some suggestion. In the real database. I have a report that pulls a list of services and I need to match them with another table that has some values. if the services are not performed on any given month, they are excluded and thereby being excluded in my query. However, I need to have them in a report for comparison purposes. I appreciate any input.

Thanks in advance.

[TheShabz]

Sadly, what you are looking for is a full outer join. SQL Server supports the FULL OUTER JOIN clause but Access doesn't. What you will have to do is run 3 separate queries to pull all cases (inner, left, right) and then a UNION query to pull it all together.

http://www.databasejournal.com/featu...oft-Access.htm

[usa_dreamer2002]

Thanks for replying. I tried your example but no luck it doesnt pickup one of the values

[TheShabz]

so you had
qry1
SELECT *
FROM table1 LEFT JOIN ....

qry2
SELECT *
FROM table1 RIGHT JOIN ....

qry3
SELECT *
FROM table1 INNER JOIN ....

qry4
SELECT *
FROM qry1
UNION
SELECT *
FROM qry2
UNION
SELECT *
FROM qry3