Split field in query + link to another table

[MadCatmk2]

Hi people

Access Ver. Access 97

I have a database table ([Contacts]) relating to contacts made to patients. In this table i have a field called primary_reason_for_care. This field is a three letter code that links to a codes database and to a table which lists the full description given the three letter code. I have already managed to update the contacts table to show the description as apposed to the code but i have a problem with another field. The field [Contacts].Other_reasons_for_care uses the same codes as the primary_reason_for_care, Only this field can have more than one code delimited by carriage returns.

What i would like to know is if it would be possible to have a query that would split up the field into the three letter codes and then relate each of these codes to the codes database. From that i want to return the descriptions to the field instead of the codes.

Hope this makes sense. Any help would be much appreciated.

Thanks in advance.

 

[scriverb]

Yes, you can do this. Create an initial select query to split the data field using the Mid$ function.

Select Mid$(A.fieldname,1,3) as AlphaCode, A.* 
FROM yourtablename as A;

Now name and save that query and take it into a new query where you can join this new AlphaCode field to your other table and pull in your description.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[MadCatmk2]

Thanks for the quick response.

Running the code that you provided, it seems to list all the rows and columns as before, but it creates an extra field (AlphaCode). AlphaCode stores the first value stored in the Other_reasons_Care field. What i need is to get each of the values held in this field. There can be any number of the three letter codes. What you have provided is certainly a start but i still have the main problem of getting each of the codes and checking the codes database for their descriptions.

Let me know if i'm not making sense (i know what i'm trying to say :O) )

Thanks again for your post.

 

[scriverb]

Sorry, I though it was just one three letter code.

Select Mid$(A.fieldname,1,1) as AlphaCode1, Mid$(A.fieldname,2,1) as AlphaCode2, Mid$(A.fieldname,3,1) as AlphaCode3, A.*
FROM yourtablename as A;

Now you can take this into a new query and link each of these alpha codes to a seperate lookup table to retrieve the descriptions. You do need to have three instances of the same table to make the linked connections. It is okay to do this and access will just create thee table giving them each a unique name. Then you can pull in the description corresponding to the correct linkeage.

Post back if you need more help.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[PHV]

You have to write your own Public function dealing with recordset.
A better approach is to normalize your table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MadCatmk2]

Thanks for your posts.

I actually finally done it through VB and an ADODB connection to the database. It all seems to have worked fine.

Thanks for all your help.