Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Camp Lejeune, North Carolina, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup values (Access 2000)

    (Subject edited by HansV to aid future searches).

    I have a database at work which has a patient information table. I use ACCESS DB for mail merge, but I use abbreviated names from a look up column already established in the database. But I physically have to change the abbreviated names to the full names for correctness in creating the mailing addresses.

    How do I get access to use the 2nd column in the lookup wizard for the Unit names instead of column one which has just the abbrevations.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup values (Access 2000)

    I'm not sure I understand the situation completely. I think you should be able to create a query based on the patient information table and the lookup table, joined on the lookup field. Add the fields from the patient information table to the query, plus the full name from the lookup table. Use the query as data source for the mail merge.

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Location
    Camp Lejeune, North Carolina, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup values (Access 2000)

    The lookup values are already created in the patient information table. The staff just see the first column and choose from the abbreviated names. I used a lookup wizard and typed the data into 2 columns abbreviated and full name but one the lookup columns were completed and saved access asked which column I want as default and stated the second column would be available for later use. How do you pull information using the second column for mail merge letters.

    hope this helps, told you I am confused all the time.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup values (Access 2000)

    I'm afraid I don't understand. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Location
    Camp Lejeune, North Carolina, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup values (Access 2000)

    Yes but it will take awhile, multitasking presently.

    Thank you for your immediate attention

  6. #6
    New Lounger
    Join Date
    Jun 2003
    Location
    Camp Lejeune, North Carolina, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup values (Access 2000)

    I have created a report which fills in data from my screening information table. I have the document working as I want except for the diagnosis paragraph below. I would like to use the diagnosis already in the lookup columns under DiagETOH and DiagDrug. The first columnV71.09) which the database uses daily is just the number code for DSM IV, but my second column ("no diagnosis") has the diagnosis spelled out in english. I should be able to tell it to look and use the second column to supply the diagnosis in last part of the text box. I know I want it to pick diagnosis from column 1 not column 2 when filling in the diagnosis automatically without typing it in. Presently we type it in, but the information is available on the lookup boxes on the Frm_Screen Subform. Now I really have both of us confused <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

    I want to finish the report with the diagnosis entered using the spelled out diagnosis. The remainder of the code in the text box would have to allow for one or multiple diagnosis under DiagETOH & DiagDrug, because our patients sometimes might have an alcohol diagnosis with one or multiple drug diagnosis also.

    Below is the code typed into the report as a text box and we physically type in the diagnosis. Below that are the choices from the DiagETOH & DiagDrug lookup boxes for the screening information form/table.

    TEXT BOX CODE IN BODY OF REPORT:

    ="1. The above individual was evaluated per reference (a) on" & " " & Format([Date],"mmmm dd"", ""yyyy") & " " & "as a result of a command referral. The individual's case was reviewed by a credentialed provider on" & " " & Format(Date(),"mmmm dd"", ""yyyy") & "." & " " & "The following diagnosis was made:" & " " & [Enter diagnosis]


    LOOKUP IN DiaETOH SCREENING FORM
    V71.09;"No Diagnosis";305.00;"ETOH Abuse";303.90;"ETOH Dependence";303.00;"Acute ETOH Intox"


    LOOKUP IN DiagDRUG SCREENING FORM:

    V71.09;"No Diagnosis";304.10;"Sedative, Hyponotic, or Anxiolytic Dependence";304.20;"Cocaine Dependence";304.30;"Canabis Dependence";304.40;"Amphetamine Dependence";305.20;"Canabis Abuse";305.60;"Cocaine Abuse";305.50;"Opioid Abuse,Unspecificed";304.00;"Opioid Dependence";304.80;"Polysubstance Dependence";305.90;"Other mixed";304.90;"Unspecified Drug Dependence";305.30;"Hallucinogen Abuse";304.50;"Hallucinogen Dependence";305.70;"Amphetamine Abuse";305.40;"Sedative, Hypnotic, or Anxiolytic Abuse";304.60;"Inhalant Dependence"

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup values (Access 2000)

    If I understand your description correctly, the Row Source Type of the combo boxes is Value List. It would be much more convenient to store the DSM IV codes and their descriptions in a table, you can then use this table for retrieving the description belonging to a code.

    <table border=1><td align=center>Code</td><td align=center>Description</td><td>V71.09</td><td>No Diagnosis</td><td align=right>304.10</td><td>Sedative, Hyponotic, or Anxiolytic Dependence</td><td align=right>304.20</td><td>Cocaine Dependence</td><td align=right>304.30</td><td>Canabis Dependence</td><td align=right>304.40</td><td>Amphetamine Dependence</td><td align=right>305.20</td><td>Canabis Abuse</td><td align=right>305.60</td><td>Cocaine Abuse</td><td align=right>305.50</td><td>Opioid Abuse,Unspecificed</td><td align=right>304.00</td><td>Opioid Dependence</td><td align=right>304.80</td><td>Polysubstance Dependence</td><td align=right>305.90</td><td>Other mixed</td><td align=right>304.90</td><td>Unspecified Drug Dependence</td><td align=right>305.30</td><td>Hallucinogen Abuse</td><td align=right>304.50</td><td>Hallucinogen Dependence</td><td align=right>305.70</td><td>Amphetamine Abuse</td><td align=right>305.40</td><td>Sedative, Hypnotic, or Anxiolytic Abuse</td><td align=right>304.60</td><td>Inhalant Dependence</td></table>
    The Row Source Type of the combo boxes would change to Table/Query and the Row Source to the name of the diagnoses table or of a query based on this table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •