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


0
Utf
12/21/2009 9:53:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
892 Views

Similar Articles

[PageSpeed] 58

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
> 
> 
0
John
12/22/2009 12:25:57 AM
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
> 
> 
0
Utf
12/22/2009 5:40:01 PM
both great soultions, I elected to use Karl's since it returns only one 
result per row.
thanks for the help!!
0
Utf
12/28/2009 9:00:01 PM
Reply:

Similar Artilces:

Summing Values using multiple criertia
Does anybody know a formula I could use to sum a range of values based on multiple criertia? Example: Division Type Wage Bulk Driver 200.00 Bulk Admin 400.00 General Admin 500.00 Bulk Driver 100.00 I want to sum the wages for Divison "Bulk" & Type "Driver". How can I do this??? Thanks! Jane =SUMPRODUCT((A2:A4="Bulk")*(B2:B4="Driver")*(C2:C4)) If there are lots of such totals, you may want to consider a pivot table rather than formulas. On Mon, 27 Sep 2004 20:14:27 -0700, "Jane" <anonymous@dis...

how do i change the default value of measure from points to inche.
how do i change the default value of measure from points to inches when setting the width and hight of cells? You don't. Excel uses only points for these measures best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "yoyo4u" <yoyo4u@discussions.microsoft.com> wrote in message news:33420157-6E05-4A55-9003-088D731E495E@microsoft.com... > how do i change the default value of measure from points to inches when > setting the width and hight of cells? yoyo Row heights are measured in points. There are 72 points to an inch. Th...

Chart to show Portfolio Value over time?
I'm using Money 2003. I would like to be able to see the $ value I have in my portfolio over time. So that I can weep. Money does have a chart view that allows you to see the PRICE history for a given stock over time - but not the dollar value of your investment in the stock over time. In fact, I can't seem to find a view at all that shows you the net value of your portfolio/individual stocks changing over time. The best I've been able to do is to use the "Net Worth" report and unselect all the other accounts. This has insufficient granularity (months instea...

Insert empty numeric value
Dear all, In VB, I have three textbox which are amount1,amount2 and amount3. After user enter the value in the textbox, I will insert the value into Access table. The table have three columns amount1 , amount2 and amount3, and all are nummeric Type. However, if the user do not enter any value in textbox . The insert statement will become as follows: Insert into table1 (amount1,amount2,amount3) values (,,) Then access complain that there is syntax error in insert statement. Does that mean I cannot insert empty value for the numeric value in access.? How to solve this problem. Than...

Subtracting value from main form
I have a borrow module which will alow user to return item separately. So, I have get the structure of returning it separately. In my main form is the borrowing item, with the loaned quantity and the owed quantity (will be calculated). In the subform, there is the returning transaction. User will need to key in the quantity returned and it will be automatically deducted from the quantity owed. But how am I supposed to get the quantity deducted while it 1 is in main form and the other is in subform? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-fo...

Pivot Table with Quarter Subtotals across the top
Hello, I'm constantly creating pivot tables that show sales information by date across the top from left to right. I would love to insert a subtotal at the end of each calender quarter to subtotal the last three months. As it stands right now I just drop in the "Month" field from the data into the pivot table and the months flow through the pivot table. Is it possible to insert a quarter subtotal, say "1Q07" that will sum Jan-07, Feb-07 & Mar-07? Thanks in advance, Dza In 2007- Select any date heading, on the options ribbon click Group Field in the Group g...

How do I find a value on a line?
I made a line graph of data to use as a calibration. I know the y value and I want to find the X value. Is there a way that I find find this specifically on the line without using a trendline formula or guessing by looking at the gridlines? The only way to find a specific value is to use the formula. Rgs, Bou If you can accept piece-wise linear interpolation, see Interactive Chart http://www.tushar- mehta.com/excel/software/interactive_chart_display/index.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity s...

Suggest a solution
Hi all. I have a table named Articles that has the fields: ArticleID, ArticleName, Price and so on.... What I want to do is to make a new table that would hold some related Property records for each article. For example... Color, Weight, Length,.. This is not a problem, but what I want is to be able to change fields names for the second table (like PropertyID, PropertyName) and be able to search among those values from query. This is the most important part: ---------------------------------------------------------------- I would like to have them as a field in query from ...

Replace null value with the previous value?
I have a database that was just imported that has approximately 388000 records. The problem is that there is information about a person in multiple different records but the name did not come across with each record. (So I have 10 records with information for a certain name, but the name only appears in field 1 of the first record and not the subsequent 9, etc.) I need to create a query or expression that will fill field 1 with the preceding value if it is null. This way I will have all the information for field 1 in a manner that I can link and combine data. Simply I need to fil...

Calculating values for empty cells.
Hello. I have a very simple problem that I cannot find the answer to. I have data in two columns, some of the data in one of the columns is missing and I want to automatically extrapolate what the data should be based on the trend. How can I get Excel to fill in empty values without overwriting the known values. Below is a sample of my data. 1500 1600 1700 1800 4000 1887 5700 1900 5500 1910 7300 1912 8100 1920 8800 1926 10100 1930 11900 1936 12200 1938 -- Ryan Taylor rtaylor@stgeorgeconsulting.com Not sure what yo...

auto filter data within pivot table
Hello, Does anyone know how can we filter data within a pivot table? or sort the data from ascending to decending order withing a pivot table??? Can we do that? It doesnt seems to work?! Thanks. Elaine. You can add fields to the page area, and use them to filter the pivot table's data. To sort a field, select a cell in a column, and click the A-Z button on the Excel toolbar. elaine wrote: > Does anyone know how can we filter data within a pivot table? or sort > the data from ascending to decending order withing a pivot table??? > Can we do that? > > It doesnt se...

Default value for custom field?
How can I populate a custom field with a default value? Specifically, I have a custom field "DisplayName" associated with the Quote Detail object. I want initially to populate this field with the value of the product name field when a new product is added to a quote. The user can then edit the DisplayName custom field if desired. The DisplayName custom field will be used as the product name on a Crystal Reports quote form. ...

look way to map form at CRM frontend to tables at CRM database
How can I find out what tables which new record has been added on when I enter data through a form in CRM front end? I try to find way to map form in the CRM frontend to the tables at the CRM database at backend? It is CRM 3.0 Thank in advances for help! ...

Editing more than 1 table at a time with SQL.
Say I have two tables in a DB called TABLE1, and TABLE2. TABLE1 [IDNUMBER] [FIRSTNAME] 1 Ryan 2 Jennifer 3 Monica TABLE2 [IDNUMBER] [LASTNAME] 1 Johnson 2 Snipes 3 Smith My question is would I be able to use SQL in code to Edit, Delete or Insert data to both tables in one string rather than one table at a time? For instance what if I want to change both tables with a [IDNUMBER] = 2 to a [IDNUMBER] = 4, OR change the ...

Table headers in a list box (Custom Report)
I have a big table with many fields (more than fifty). They are not in one table but related. Now user's come with a requirement of a separate type of report everyday which has different fields. because of which i have to design a report for them with the required query. I was therefore wondering can i make a form which has two list boxes the one on left side will have all the fields of the table (only headers needed, not data). Users can select multiple no. of fields from that list box which will appear in another list box. Users should be able to then select the order of fi...

Do you need MS Access to query on an Access table?
I'm asking these questions because I have looked at a lot of stuff in the discussion groups and still confused. I am fairly good at Excel programming but haven't been able to get Excel and Access to talk. First, I have Office 2003 Professional at work and Office 2003 without Access at home. I want to work on developing Excel programming at home which will get data from Access to Excel with either MS Query or with programming. Is it possible to just have the .mdb files on the home computer for Excel to work with, or do I need the Access program too? The info I want ...

I am having trouble keeping numbers formatted in a Pivot Table
I have a pivot table that won't stay formatted. The data is formatted as currency in the worksheet that the Pivot Table is drawing off of. It shows up as a general number in the Pivot. I've tried formatting it in the Pivot but it doesn't stay when I refresh the data. "Preserve formatting" is checked on the Pivot table, so it SHOULD be preserving any formatting I apply to the pivot table, but that doesn't seem to be happening. I know I could record a macro to format this, but it is happening on multiple Pivot tables in the same workbook and that would be a pain ...

Limit value for Storage Groups
Hello, we are running Exchange 2003 Standard edition. The limitation on Storage Groups for e.g. "Issue warning at(KB)" is default set by 2097151. I read that i can change this value by using ADSI Edit and edit the values mDBOverHardQuotaLimit mDBOverQuotaLimit mDBStorageQuota But each of them has a lots of values and i could not find out which one i have to edit. Could you please give me an advice? TIA helpi If you want to set a value bigger than 2GB for all users on an entire mailbox store, bring up the properties of that mailbox store in ADSIEdit and set the mDBStorageQuot...

Pivot table field dropdown list reset #2
I would like to know if there is a way to reset a field dropdown list in a pivot table. In some field sometimes I delete data from the source and some items of the field will no longer be there. However, when I refresh the pivot table the dropdown list will still show the old items even if they're not in the source data anymore. I really need your help with this. Thanks a lot in advance for your help, Francisco Molina ...

Extract Values from a Column
I have a worksheet used to track time spent on various jobs. One column is for the JOB # while others are for descriptions, etc. I would like to have formulas or possible a macro (if necessary) to sum the time spent on different jobs. For example, say (for simplicity) that each row is equal to 1 unit of time. Then in this row I put 342 for JOB # 342. After the entire day I have worked on say 5 different jobs. I would like to have a cell that says "Total time spent on Job # 342" and then next to it a formula that would look at the column and count all the values that are equal t...

Value shading map issue
I am creating a map in Excel using MS Map to show a US map. When using the value shading function, I can't figure out how to keep the states that have no values blank. What happens is the key shows a range of values (0- 10, 11-20). I need the states that have NO value to have their own color (or lack of color) and I am not sure how to do this... Any suggestions? Chris In your data, you could calculate the state name in a new column, and use the calculated name in the map. For example, if state names are in column D, and values are in column E: =IF(E2=0,"",D2) Th...

custom report not showing values
I have uploaded a very simple SRS report that includes "out of box fields" and custom fields. When I run the report, instead of my custom field values showing in the report, I get the id of the value. What am I missing? -Melissa ...

How do I compare output data from 2 pivot tables in a graph forma.
I have a pivot table created for 2004 that shows the number of inspections we received on a particular day of the week (ie. 5 on saturdays, 2 on wednesdays, etc.) I am creating the same pivot table for 2005 and am looking for a way to graph the data (bar graph) of the pivot table from 2004 to compare with my 2005 data (ie. last year we received 5 inspections on saturdays, this year we received xx amount on saturdays). Is this even possible to do? I am using Office Pro 2003 and any suggestions would be greatly accepted. Doobi, One option is to combine the pivot tables for 2004 and...

How to purge all the Integration Manager log tables?
I went through and purged all the log files from Integration Manager using the procedure outlined in the IM User's Guide. However, after I was finished I looked at the tables in the im.mdb Access database and noticed that the following tables still have quite a few records in them: LogDocumentActivity LogActivity LogDocuments The Log table was empty, which makes sense since I purged all the logs. However, these other tables seem to have retained their records. Is there a way to purge these tables, too? -- Bud Cool, Accounting System Manager HDA, Inc. Hazelwood, MO GP 9.0, SP2 B...

averages without including zero values
={AVERAGE(IF(J7:J21<>0, J7:J21,""))} Trying to use a array formula similar to the above to calculate an average of numbers from a column without including zero value fields in the average. but I am not successful. Any ideas -- lighting ------------------------------------------------------------------------ lighting's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29656 View this thread: http://www.excelforum.com/showthread.php?threadid=493664 Hi Lightning, The Average function ignores empty cells, so try: = Average(J7:J21) --- R...