We help IT Professionals succeed at work.

How do I populate my subform with values from multiple columns - only populating value from one column

donvike1
donvike1 asked
on
I have a subform (frm_ProblemList_subform) within a form (frm_UltraPage)
The subform is populating data from tbl_OfficeVisit and tbl_ICD9
The problem is that I'm only populating data from one column - I would like to populate data from 3 columns instead of one
The 3 columns are ICD9,  ICD92,  ICD93,  from tbl_OfficeVisit and Short_Description from tbl_ICD9
How do I get my subform to populate the other two columns value?

Here is my syntax  also see the attachment with the screenshot of the form and the table - the data is fictisous)

SELECT [tbl_OfficeVisits].[ICD9_Code], [tbl_ICD9].[Description], [tbl_OfficeVisits].[Patient_id] FROM tbl_ICD9 INNER JOIN tbl_OfficeVisits ON tbl_ICD9.ICD9_Code=tbl_OfficeVisits.ICD9_Code;
OfficeVisits.docx
Comment
Watch Question

donvike:
Here you see why the non-normalized structure of tbl_OfficeVisits gets you in trouble: data that should live in one field of three records is stored in three different fields in the same record.

The way to list the results long ways is to create a Union query, which means that the results are not editable.

Query named UnionCodes:
SELECT [tbl_OfficeVisits].[ICD9_Code], [tbl_OfficeVisits].[Patient_id] FROM tbl_OfficeVisits
UNION ALL
SELECT [tbl_OfficeVisits].[ICD92_Code], [tbl_OfficeVisits].[Patient_id] FROM tbl_OfficeVisits
UNION ALL
SELECT [tbl_OfficeVisits].[ICD93_Code], [tbl_OfficeVisits].[Patient_id] FROM tbl_OfficeVisits

Query named Final:
SELECT [UnionCodes].[ICD9_Code], [tbl_ICD9].[Description], [UnionCodes].[Patient_id] FROM [UnionCodes] INNER JOIN [tbl_ICD9] ON [UnionCodes].[ICD9_code] = [tbl_ICD9].[ICD9_code]

Open in new window


Hope this helps,
pT72
Commented:
The union worked, however it brought back the same results as my initial subform - see attachment.

I am going to take your advise and undo my relationships and correct the table.

Thanks

Author

Commented:
Opps here is the attachment
OfficeVisits-Unioncode.docx

Author

Commented:
The end results were the same as my initial results.