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

Senin, 26 November 2012

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

You can use a normalizing union query to join to the "table that holds the description for each"

SELECT [Acct #], D1 as D, 1 as Num
FROM TableThatHoldsTheCodedData
UNION ALL
SELECT [Acct #], D2, 2

FROM TableThatHoldsTheCodedData

UNION ALL
SELECT [Acct #], D3,3

FROM TableThatHoldsTheCodedData

UNION ALL
... etc ...
SELECT [Acct #], D25, 25

FROM TableThatHoldsTheCodedData;

Then join this union query to "the table that holds the description" using the D column/field.

I would consider normalizing the data permanently but I'm a bit retentive with my applications.

Duane Hookom MVP
MS Access

> To: MS_Access_Professionals@yahoogroups.com
> From: karlytos_rivas@yahoo.com
> Date: Mon, 26 Nov 2012 20:40:33 +0000
> 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
>
>
>

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