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

I would expect a patient table with a primary key of PatientID. There would be absolutely no Dx or Px information stored in this table. There would be a junction table that would contain all of the Dx's for patients with one record for each patient for each Dx.

tblPatientDx
===================
PatDxID autonumber primary key (my standard)
PatientID related to tblPatient.PatientID
DxCode related to [Maint_Dx Codes V30].[Diagnosis Code]
DateOfDx when was this diagnosed (optional)
DxBy who made the diagnosis (optional)
DxLastUpdate
DxStatus

There would be no repeating columns like D1, D2, etc.

You could create a similar table for Px or the Dx and Px codes might be combined into a single Maint table and a single junction table.

I have worked with these types of databases in hospitals in the past and I realize they are not normalized but I think you have the option of normalizing or not. I would normalize.

Duane Hookom MVP
MS Access

> To: MS_Access_Professionals@yahoogroups.com
> From: karlytos_rivas@yahoo.com
> Date: Thu, 29 Nov 2012 21:11:12 +0000
> Subject: [MS_AccessPros] Re: Create a report that relates a value to an item description
>
> Duane,
> I understand my tables are normalized, all I have is 5 tables, One table holds the demographic information (Patient Master File)15 fields, then I created a table that holds the patient's "Dx codes" and "Px codes" for each record in Patient Master File, there are 25 Dx and 25 Px fields. I then have another table related to Patient master file that holds some status information about this same records. Then I have 2 tables (Maintenance Tables), one that Holds the Dx Codes and its descriptions and another table that holds the Px codes and their description.
> The report I want to create collects the demographic data from the Patient Master File and any codes I entered in the Patient Coded Data Table. I then want to bring to the report the descriptions for this Dx and Px codes from the from the Maintenance tables. I have the Patient Coded Data Table related (One To Many) to the Maint Tables (Dx and Px). If my tables are still not normalized or have a bad design, can you please suggest me how can I normalized them, should I put the Dx in a different table than the Px codes to make the tables smaller?
> I tried your suggestion "text", version, however I get an error stating that the SQL statement could not be executed because it contains ambiguous outer joints.
> regards,
>
> --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
> >
> > 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@...
> > > 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@...>
> > > 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]
> >
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

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