ACCESS FEED: Re: [MS_AccessPros] Re: Create a report that relates a value to an item description

Kamis, 29 November 2012

Re: [MS_AccessPros] Re: Create a report that relates a value to an item description

Carlos-

Try this:

Desc1: DLookUp ("Short Description","Maint_Dx Codes V30","Diagnosis Code =
" & [ADM Dx])

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: "c.rivas" <karlytos_rivas@yahoo.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Thursday, November 29, 2012 9:22 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Re: Create a report that relates a value to an
item description

Hi John,
I follow your advise, however I get an error stating that my expression
contains an invalid syntax.
This is my scenario, please correct me if I'm wrong. The table "Patient
Coded Data" holds the [D1] field, this field is related (One To Many) to
the field "Diagnosis Code" (This field is also the Primary Key) in the
table "Maint_Dx Codes V30" This table also hold the field "Short
Description" which holds the description of [D1] and any other [D Field]
in the table "Patient Coded Data"
Below find an example of the expression I built in the query I made based
on your suggested expression. Do I have to put the Quotation Marks in the
expression? I tried with and without the Quotation marks and I get the
same error message. Please advice

Expr1: DLookUp (Short Description,Maint_Dx Codes V30,Diagnosis Code = [ADM
Dx])

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...>
wrote:
>
> Carlos-
>
> You have a bad table design. See also Duane's reply. One solution might
> be to use DLookup. For each description field, use:
>
> Description1: DLookUp("Description", "DescriptionTable", "Dkey = " [D1])
>
> Where "Decription" is the name of the description field in the linked
> table, "DescriptionTable is the name of the linked table, and "Dkey" is
> the name of the linking field, and D1 is the name of the first "D" field.
> Repeat for all 25 fields. It will run slowly, but you won't run into
> "query too complex" from having more than 16 tables in your query.
>
>
>
> -----Original Message-----
> From: "c.rivas" <karlytos_rivas@...>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Monday, November 26, 2012 9:40 PM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Re: Create a report that relates a value to an
> item description
>
>
>
> Hi Crystal, thanks for your help, The table that holds the "Coded Data"
> "D1, D2, D3..." have one Primary key, which is the Acct # for that record
> (This Table Relates to another table holding demographic Info) The D1,
>D2,
> D3... fields in the "Coded Data" table, are just regular fields holding
> these codes. I then related (One to Many) these individuals fields to the
> table "Dx Codes", this is the table that holds the description for each
>of
> D1, D2, D3 fields (the Primary Key of this table "Dx Codes" is an
> auto-number)
> I thought of creating duplicates tables that holds the description of
> these codes and relate each individual data field from the "Coded Data"
> table to a unique (duplicate) table, as John suggested, but I feel that's
> way too much duplicity, since there are 25 "D1, D2, D3" fields.
> There must be an easier way for Access 2007 to identify the value of the
> D1, D2, D3, fields in the "Coded Data" Table and look up their
>description
> in the table that holds the description of the codes.
> All I need is for the description of the codes selected to show in a
> report.
> Thanks for any help you can provide,and have a wonderful evening..!
> Regards, Carlos
>
> --- In MS_Access_Professionals@yahoogroups.com, Crystal
> <strive4peace2008@> wrote:
> >
> > what is the structure of the relevant tables and how are they related?
> >thanks
> >
> >
> > Warm Regards,
> > Crystal
> >
> > Learn Access on YouTube
> > http://www.youtube.com/LearnAccessByCrystal
> >
> > *
> > (: have an awesome day :)
> > *
> >
> >
> >
> > ________________________________
> > From: karlytos_rivas <karlytos_rivas@>
> > To: MS_Access_Professionals@yahoogroups.com
> > Sent: Saturday, November 24, 2012 7:17 PM
> > Subject: [MS_AccessPros] Create a report that relates a value to an
>item
> >description
> >
> > Hi,
> > I'm working on a project where I have to create a report based on a
> >query and data from different table/query. The query holds in many
> >records, and each record holds multiple values like D1, D2, D3,
> >Nonetheless the report needs to show these values D1, D2, D3 and the
> >description of each one, from another table. See sample below:
> >
> > Eg "D1" "Data 1 Description", "D2" "Data 2 Description", "D3" "Data 3
> >Description"
> >
> > The values are in one table/query and the description are in another
> >table. I have tried relating each Value in the Query/table to the Table
> >holding the descriptions, however all values bring the same description.
> >See below:
> >
> > Eg "D1" "Data Description 1", "D2" "Data Description 1", "D3" "Data
> >Description 1"
> >
> > I believe that there could be an expression or a Query that can help me
> >pull in the report the correct descriptions for each value. Thanks
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

------------------------------------

Yahoo! Groups Links