Query for 'confirmation'

Please help. I am trying to make updating a 'confirmation' routine quick and
easily updateable by some one else.

I have 2 tables, one of which I would like to use to confirm data.

tblTitle: tblState:
RecordID RecordID
Title ParentID
State State

Unfortunately, I cannot use a 'real' relationship to define 'tblTitle.State'
using 'tblState'. 'tblTitle' is imported from a text file.

What I would like to do, preferably using only SQL, is to program a field
similar to this expression (using pseudo code) 'St: IIf(tblTitle.State not
included in tblState.State; "«Unrecognized State»"; tblTitle.State)'

So other users can update 'tblState' as necessary to account for changes in
the exported text file, I wish to avoid:
nested IIf without using tblState
additinal VB code to loop through tblState


(A little background, this frontend does, hopefully, every thing but the
data collection for project tracking. Why on earth anyone would make a
'Nice' project tracking software with out reporting more than lists of
records is beyond my comprehension.)

thanks, Rogge

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

If the table tblTitle is imported, then it IS an Access table and
relationships can be created between other tables. But, you really
don't need that. If I understand your problem statement correctly, you
want to substitute "«Unrecognized State»" for the state when the
tblTitle State is not in tblState. You could write a query like this:

SELECT T.State As Title_State, Nz(S.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

Since you need to know which state is missing from tblState the above
query shows only which states are missing. If you want to see all
states, including missing states, remove the WHERE clause.

The Nz() function returns the 2nd parameter when the 1st parameter is
null. The LEFT JOIN returns all rows from tblTitle and NULL rows from
tblState when there isn't a equivalent State in tblState. If the
tblState.State column is NULL the Nz() function will return
"«Unrecognized State»."
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

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

iQA/AwUBQl7L8YechKqOuFEgEQIsyACeLXYawn5cnF4hjk5VrH6ijf tXT9sAoJvE
HImLsgHGenZNqYm0hiw9kRL0
=66PA
-----END PGP SIGNATURE-----

rogge wrote:
Please help. I am trying to make updating a 'confirmation' routine quick and
easily updateable by some one else.

I have 2 tables, one of which I would like to use to confirm data.

tblTitle: tblState:
RecordID RecordID
Title ParentID
State State

Unfortunately, I cannot use a 'real' relationship to define 'tblTitle.State'
using 'tblState'. 'tblTitle' is imported from a text file.

What I would like to do, preferably using only SQL, is to program a field
similar to this expression (using pseudo code) 'St: IIf(tblTitle.State not
included in tblState.State; "«Unrecognized State»"; tblTitle.State)'

So other users can update 'tblState' as necessary to account for changes in
the exported text file, I wish to avoid:
nested IIf without using tblState
additinal VB code to loop through tblState


(A little background, this frontend does, hopefully, every thing but the
data collection for project tracking. Why on earth anyone would make a
'Nice' project tracking software with out reporting more than lists of
records is beyond my comprehension.)

thanks, Rogge

I do use Nz(); I find it very usefull

But my problem lies in a 'non-recognized' value not a null value.

Lets say 'tblTitle.State' has these values:

10. Initiation
20. In Progress
30. Waiting for Feedback
31. Waiting for Feedback - Check
40. Completed

and 'tblState.State' has, as these 'official options' for the 'State' field:

10. Initiation
20. In Progress
30. Waiting for Feedback
40. Completed

I would like "31. Waiting for Feedback - Check" to appear as "«Unrecognized
State» (touch the data again - I'll send Luca Brasi.)"

Well maybe I'll leave my personal comments out of the database.... LOL

Have a great weekend!
-r

"MGFoster" wrote:

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

If the table tblTitle is imported, then it IS an Access table and
relationships can be created between other tables. But, you really
don't need that. If I understand your problem statement correctly, you
want to substitute "«Unrecognized State»" for the state when the
tblTitle State is not in tblState. You could write a query like this:

SELECT T.State As Title_State, Nz(S.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

Since you need to know which state is missing from tblState the above
query shows only which states are missing. If you want to see all
states, including missing states, remove the WHERE clause.

The Nz() function returns the 2nd parameter when the 1st parameter is
null. The LEFT JOIN returns all rows from tblTitle and NULL rows from
tblState when there isn't a equivalent State in tblState. If the
tblState.State column is NULL the Nz() function will return
"«Unrecognized State»."
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

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

iQA/AwUBQl7L8YechKqOuFEgEQIsyACeLXYawn5cnF4hjk5VrH6ijf tXT9sAoJvE
HImLsgHGenZNqYm0hiw9kRL0
=66PA
-----END PGP SIGNATURE-----

rogge wrote:
Please help. I am trying to make updating a 'confirmation' routine quick and
easily updateable by some one else.

I have 2 tables, one of which I would like to use to confirm data.

tblTitle: tblState:
RecordID RecordID
Title ParentID
State State

Unfortunately, I cannot use a 'real' relationship to define 'tblTitle.State'
using 'tblState'. 'tblTitle' is imported from a text file.

What I would like to do, preferably using only SQL, is to program a field
similar to this expression (using pseudo code) 'St: IIf(tblTitle.State not
included in tblState.State; "«Unrecognized State»"; tblTitle.State)'

So other users can update 'tblState' as necessary to account for changes in
the exported text file, I wish to avoid:
nested IIf without using tblState
additinal VB code to loop through tblState


(A little background, this frontend does, hopefully, every thing but the
data collection for project tracking. Why on earth anyone would make a
'Nice' project tracking software with out reporting more than lists of
records is beyond my comprehension.)

thanks, Rogge

-----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.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

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

iQA/AwUBQmALLoechKqOuFEgEQKjpQCeMs1Xk/x90OzKocN/55EZiQFyP/UAnir/
cAEuhwANbWZ745LoAub0CZaD
=LOmm
-----END PGP SIGNATURE-----

rogge wrote:
I do use Nz(); I find it very usefull

But my problem lies in a 'non-recognized' value not a null value.

Lets say 'tblTitle.State' has these values:

10. Initiation
20. In Progress
30. Waiting for Feedback
31. Waiting for Feedback - Check
40. Completed

and 'tblState.State' has, as these 'official options' for the 'State' field:

10. Initiation
20. In Progress
30. Waiting for Feedback
40. Completed

I would like "31. Waiting for Feedback - Check" to appear as "«Unrecognized
State» (touch the data again - I'll send Luca Brasi.)"

Well maybe I'll leave my personal comments out of the database.... LOL

Have a great weekend!
-r

"MGFoster" wrote:


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

If the table tblTitle is imported, then it IS an Access table and
relationships can be created between other tables. But, you really
don't need that. If I understand your problem statement correctly, you
want to substitute "«Unrecognized State»" for the state when the
tblTitle State is not in tblState. You could write a query like this:

SELECT T.State As Title_State, Nz(S.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

Since you need to know which state is missing from tblState the above
query shows only which states are missing. If you want to see all
states, including missing states, remove the WHERE clause.

The Nz() function returns the 2nd parameter when the 1st parameter is
null. The LEFT JOIN returns all rows from tblTitle and NULL rows from
tblState when there isn't a equivalent State in tblState. If the
tblState.State column is NULL the Nz() function will return
"«Unrecognized State»."
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

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

iQA/AwUBQl7L8YechKqOuFEgEQIsyACeLXYawn5cnF4hjk5VrH6ijf tXT9sAoJvE
HImLsgHGenZNqYm0hiw9kRL0
=66PA
-----END PGP SIGNATURE-----

rogge wrote:

Please help. I am trying to make updating a 'confirmation' routine quick and
easily updateable by some one else.

I have 2 tables, one of which I would like to use to confirm data.

tblTitle: tblState:
RecordID RecordID
Title ParentID
State State

Unfortunately, I cannot use a 'real' relationship to define 'tblTitle.State'
using 'tblState'. 'tblTitle' is imported from a text file.

What I would like to do, preferably using only SQL, is to program a field
similar to this expression (using pseudo code) 'St: IIf(tblTitle.State not
included in tblState.State; "«Unrecognized State»"; tblTitle.State)'

So other users can update 'tblState' as necessary to account for changes in
the exported text file, I wish to avoid:
nested IIf without using tblState
additinal VB code to loop through tblState


(A little background, this frontend does, hopefully, every thing but the
data collection for project tracking. Why on earth anyone would make a
'Nice' project tracking software with out reporting more than lists of
records is beyond my comprehension.)

thanks, Rogge

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-----

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-----