Union/Join issue

[Michelle]

I have one table that gets updated daily by an outside source I created
anohter table that never changes however it has some of the same data as the
first table.
Table1 - inmtinfo
IN_INMNUM
IN_NAME
IN_RACE
IN_BLDING
IN_SECTION
IN_CELLDRM
IN_BEDNUM
SEP
Table 2 - tblCellData
CellID
Section
Cell#
Bed
Table 1 lists all inmates and inforamtion for all units in the prison.
Table 2 lists only the Cell data for our RHU.
What I need to do is to join the two tables and return all the data from
Table 2 even if there is no matching data from Table 1.
(It should return IN_INMNUM, IN_NAME, IN_RACE & SEP from Table 1 if
IN_BLDING = H and all the data from Table 2)
This is the code I have now and it is only pulling the data from Table 2
that has matching data in Table 1. I need all of Table 2 and only the data
that matches from Table 1.
Example.
IN_INMNUM - IN_NAME - IN_RACE - IN_BLDING - Section - Cell# - Bed - SEP
EX1234 Smith W H A 1001
01 na
A
1002 01
EX2345 Davis B H A
1002 01 na

MY SQL Code

SELECT inmtinfo.IN_INMNUM, inmtinfo.IN_NAME, inmtinfo.IN_RACE,
inmtinfo.IN_BLDING, tblCellData.Section, tblCellData.[Cell#],
tblCellData.BED, inmtinfo.SEP
FROM inmtinfo RIGHT JOIN tblCellData ON (inmtinfo.IN_SECTION =
tblCellData.Section) AND (inmtinfo.IN_CELLDRM = tblCellData.[Cell#]) AND
(inmtinfo.IN_BEDNUM = tblCellData.BED)
WHERE (((inmtinfo.IN_BLDING)="H"));

 

[KARL DEWEY]

See my response to yesterday's post.