-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That's simple enough:

SELECT T.*, "«Unrecognized State»" As State_State
FROM tblTitle As T LEFT JOIN tblState As S
ON T.State = S.State
WHERE S.State IS NULL

If this doesn't satisfy your needs, please post the complete definition
of the tables (column names & their data types) and some sample data
from both tables with column names over each data column.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmPyD4echKqOuFEgEQIPbACgt9ZmEr2zenlWPjPmvBZqe5 dL1JUAn3tN
L0TzRl0bgH3a8iPZ2Kx7mU5H
=mKnk
-----END PGP SIGNATURE-----

rogge wrote:
The entire record needs to be returned where unrecognized values are shown as
«Unrecognized State» with out replacing the data.

for instance this is the data in the table

Title: State
check oil 10. initiation
replace oil filter 30. Waiting for Feedback
replace oil 31. Waiting for Feedback - Check

what i want to return is...
Title: State
check oil 10. initiation
replace oil filter 30. Waiting for Feedback
replace oil «Unrecognized State»

Thank you for your time and effort.


"MGFoster" wrote:


MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Run my query. You'll find that it does what you want. Read up on LEFT
JOIN in the Access Help articles on Microsoft Jet SQL Reference Data
Manipulation Language LEFT JOIN, RIGHT JOIN Operations.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On further thought you could run a query like the following. It finds
all States in tblTitle that are not in tblState. Same thing I had
before just w/o the Nz() function.:

SELECT T.State As Title_State, "«Unrecognized State»" As State_State
FROM tblTitle As T LEFT JOIN tblState As S
ON T.State = S.State
WHERE S.State IS NULL

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmCmMoechKqOuFEgEQLXdgCfTgCzi506w70m/r/fxgmc7OclDB0AnjGZ
lXY8gjObMp07a3gsiEr+Mmzu
=nwV8
-----END PGP SIGNATURE-----