Evaluate String for Value from Table

I have a table [tbl_dc_dx] that contains a field for Primary Diagnosis but
only a semi-colon delimited list of Secondary Diagnoses. The list is variable
in length and the[sec_diag] codes are in no particular order.

[tbl_dc_dx] sample data:
Account sec_diag
1 5990;2851;6262;2809;6202
2 6262;2800;4019;2808;2469
3 2851;9100;9219;E8889;E8497
4 4111;2859;4019;
5 5855;42822;2724
6 25000;4019;4580;2801

A list of applicable diagnosis codes is available in a table called
[tbl_dx_codes].

[tbl_dx_codes] sample data:
diag_cd diag_desc
2800 280.0-CHR BLOOD LOSS ANEMIA
2801 280.1-IRON DEF ANEMIA DIETARY
2808 280.8-IRON DEFIC ANEMIA NEC
2809 280.9-IRON DEFIC ANEMIA NOS
2810 281.0-PERNICIOUS ANEMIA

I would like to evaluate the [tbl_dc_dx].[sec_diag] field to find the first
result that matches the [tbl_dx_codes].[diag_cd] and return the fields
[tbl_dx_codes].[diag_cd] and [tbl_dx_codes].[diag_desc]

So the query would return:
Account diag_cd diag_desc
1 2809 280.9-IRON DEFIC ANEMIA NOS
2 2800 280.0-CHR BLOOD LOSS ANEMIA
6 2801 280.1-IRON DEF ANEMIA DIETARY

You can try the following, which could give you multiple hits for each account

SELECT Account, Diag_CD, Diag_Desc
FROM tbl_dc_dx as D INNER JOIN tbl_dx_codes as C
ON D.Sec_Diag LIKE "*" & C.DiagCD & "*"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

yator wrote:
I have a table [tbl_dc_dx] that contains a field for Primary Diagnosis but
only a semi-colon delimited list of Secondary Diagnoses. The list is variable
in length and the[sec_diag] codes are in no particular order.

[tbl_dc_dx] sample data:
Account sec_diag
1 5990;2851;6262;2809;6202
2 6262;2800;4019;2808;2469
3 2851;9100;9219;E8889;E8497
4 4111;2859;4019;
5 5855;42822;2724
6 25000;4019;4580;2801

A list of applicable diagnosis codes is available in a table called
[tbl_dx_codes].

[tbl_dx_codes] sample data:
diag_cd diag_desc
2800 280.0-CHR BLOOD LOSS ANEMIA
2801 280.1-IRON DEF ANEMIA DIETARY
2808 280.8-IRON DEFIC ANEMIA NEC
2809 280.9-IRON DEFIC ANEMIA NOS
2810 281.0-PERNICIOUS ANEMIA

I would like to evaluate the [tbl_dc_dx].[sec_diag] field to find the first
result that matches the [tbl_dx_codes].[diag_cd] and return the fields
[tbl_dx_codes].[diag_cd] and [tbl_dx_codes].[diag_desc]

So the query would return:
Account diag_cd diag_desc
1 2809 280.9-IRON DEFIC ANEMIA NOS
2 2800 280.0-CHR BLOOD LOSS ANEMIA
6 2801 280.1-IRON DEF ANEMIA DIETARY

Try this --
SELECT tbl_dc_dx.Account, tbl_dx_codes.diag_cd, tbl_dx_codes.diag_desc
FROM tbl_dc_dx, tbl_dx_codes
WHERE tbl_dx_codes.diag_cd = (SELECT Min([XX].diag_cd) FROM tbl_dx_codes
AS [XX] WHERE (((tbl_dc_dx.sec_diag) Like "*" & [diag_cd] & "*")) ) ;

--
Build a little, test a little.


"yator" wrote:

I have a table [tbl_dc_dx] that contains a field for Primary Diagnosis but
only a semi-colon delimited list of Secondary Diagnoses. The list is variable
in length and the[sec_diag] codes are in no particular order.

[tbl_dc_dx] sample data:
Account sec_diag
1 5990;2851;6262;2809;6202
2 6262;2800;4019;2808;2469
3 2851;9100;9219;E8889;E8497
4 4111;2859;4019;
5 5855;42822;2724
6 25000;4019;4580;2801

A list of applicable diagnosis codes is available in a table called
[tbl_dx_codes].

[tbl_dx_codes] sample data:
diag_cd diag_desc
2800 280.0-CHR BLOOD LOSS ANEMIA
2801 280.1-IRON DEF ANEMIA DIETARY
2808 280.8-IRON DEFIC ANEMIA NEC
2809 280.9-IRON DEFIC ANEMIA NOS
2810 281.0-PERNICIOUS ANEMIA

I would like to evaluate the [tbl_dc_dx].[sec_diag] field to find the first
result that matches the [tbl_dx_codes].[diag_cd] and return the fields
[tbl_dx_codes].[diag_cd] and [tbl_dx_codes].[diag_desc]

So the query would return:
Account diag_cd diag_desc
1 2809 280.9-IRON DEFIC ANEMIA NOS
2 2800 280.0-CHR BLOOD LOSS ANEMIA
6 2801 280.1-IRON DEF ANEMIA DIETARY

both great soultions, I elected to use Karl's since it returns only one
result per row.
thanks for the help!!