I totally agree with John and Crystal regarding your table structures. You wouldn't need a huge union query if your tables were normalized. Notice, I used made up table and field names in my suggested SQL. This is your clue to provide the actual names.
What are your results when you try the union query?
Duane Hookom MVP
MS Access
> To: MS_Access_Professionals@yahoogroups.com
> From: JohnV@msn.com
> Date: Mon, 26 Nov 2012 22:18:22 +0100
> Subject: Re: [MS_AccessPros] Re: Create a report that relates a value to an item description
>
> Carlos-
>
> Duane's query will help you "normalize" your data, but it won't solve your
> immediate problem.
>
> You also need to answer Crystal's question: What is the structure of your
> tables?
>
> 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: Monday, November 26, 2012 10:15 PM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Re: Create a report that relates a value to an
> item description
>
> Hi Duane, thanks for responding, I'm going to try your suggestion,
> however, this would be the first time I'm writing code in many, many
> years. Nonetheless, correct me if I'm wrong, I'm going to create a query
> (query design), add the tables that holds the coded data and the codes
> description to it and then click on the button that labeled "Union" and
> write the code you suggested for each D1, D2, D3 fields, correct?
>
> FYI, each record holding the D1, D2, D3.. D25 fields can hold any one code
> of more than 14K+ possible codes, and that's how many descriptions are in
> the table holding the codes descriptions, however there are no [Acct #]
> fields in the table holding the descriptions. All I want to relate is the
> Codes in the D1, D2, D3 fields to their respective description in the
> table that holds the codes by themselves and their description only. Can I
> still use your suggested solution under this scenario?
>
> --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom
> <duanehookom@...> wrote:
> >
> > 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@...
> > > 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]
> >
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (11) |
Tidak ada komentar:
Posting Komentar