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

I don't care to even find a solution that doesn't support/promote normalizing your data, however...

If the D fields are numeric then try:

Desc1: DLookup("[Short Description]","[Maint_Dx Codes V30]","[Diagnosis Code] = " & [D1])

If the D fields are text then try:

Desc1: DLookup("[Short Description]","[Maint_Dx Codes V30]","[Diagnosis Code]= """ & [D1] & """")

Duane Hookom MVP
MS Access

> To: MS_Access_Professionals@yahoogroups.com
> From: JohnV@msn.com
> Date: Thu, 29 Nov 2012 21:34:55 +0100
> Subject: 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
>
>
>
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

[Non-text portions of this message have been removed]