Patty,
Do you understand why most of us would consider your table structure with repeating ICD9 fields is wrong? What happens when you get more than 4 codes? As you have experienced, it's difficult to query against multiple columns/fields. A properly normalized structure would create one record per code which I have attempted to accomplish with a union query.
My original reply was missing several "Selects".
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 14 Dec 2015 12:12:26 -0800
Subject: RE: [MS_AccessPros] Diagnosis searching off charge table
Duane
thank you for your help I am sorry but this one is new to me and I think I am not understanding something.
I copied what you had below, pasting in the information from Select down to the end of the sql query. But when I try to run the query I get an error that it is missing the operator (SELECT is there). I am not sure if I am doing something wrong. Never wrote a union query before so all new to me.
Appreciate the support
Patty
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
Do you understand why most of us would consider your table structure with repeating ICD9 fields is wrong? What happens when you get more than 4 codes? As you have experienced, it's difficult to query against multiple columns/fields. A properly normalized structure would create one record per code which I have attempted to accomplish with a union query.
My original reply was missing several "Selects".
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 14 Dec 2015 12:12:26 -0800
Subject: RE: [MS_AccessPros] Diagnosis searching off charge table
Duane
thank you for your help I am sorry but this one is new to me and I think I am not understanding something.
I copied what you had below, pasting in the information from Select down to the end of the sql query. But when I try to run the query I get an error that it is missing the operator (SELECT is there). I am not sure if I am doing something wrong. Never wrote a union query before so all new to me.
Appreciate the support
Patty
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
Patty,
I would first create a normalizing union query with SQL like:
=== quniICD9 =======
SELECT [dbo_charges_Medical_dups_3 mos].person_id, [dbo_charges_Medical_dups_3 mos].person_nbr,
[dbo_charges_Medical_dups_3 mos].last_name, [dbo_charges_Medical_dups_3 mos].first_name,
[dbo_charges_Medical_dups_3 mos].date_of_birth, [dbo_charges_Medical_dups_3 mos].create_timestamp,
[dbo_charges_Medical_dups_3 mos].location_name, [Diagnosis Table].Diabetes, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id
FROM [Diagnosis Table] INNER JOIN [dbo_charges_Medical_dups_3 mos] ON [Diagnosis Table].Code = [dbo_charges_Medical_dups_3 mos].icd9cm_code_id
WHERE [Diagnosis Table].Diabetes Is Not Null
UNION ALL
[dbo_charges_Medical_dups_3 mos].person_id, [dbo_charges_Medical_dups_3 mos].person_nbr,
[dbo_charges_Medical_dups_3 mos].last_name, [dbo_charges_Medical_dups_3 mos].first_name,
[dbo_charges_Medical_dups_3 mos].date_of_birth, [dbo_charges_Medical_dups_3 mos].create_timestamp,
[dbo_charges_Medical_dups_3 mos].location_name, [Diagnosis Table].Diabetes, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id_2
FROM [Diagnosis Table] INNER JOIN [dbo_charges_Medical_dups_3 mos] ON [Diagnosis Table].Code = [dbo_charges_Medical_dups_3 mos].icd9cm_code_id
WHERE [Diagnosis Table].Diabetes Is Not Null AND icd9cm_code_id_2 Is Not Null
UNION ALL
[dbo_charges_Medical_dups_3 mos].person_id, [dbo_charges_Medical_dups_3 mos].person_nbr,
[dbo_charges_Medical_dups_3 mos].last_name, [dbo_charges_Medical_dups_3 mos].first_name,
[dbo_charges_Medical_dups_3 mos].date_of_birth, [dbo_charges_Medical_dups_3 mos].create_timestamp,
[dbo_charges_Medical_dups_3 mos].location_name, [Diagnosis Table].Diabetes, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id_3
FROM [Diagnosis Table] INNER JOIN [dbo_charges_Medical_dups_3 mos] ON [Diagnosis Table].Code = [dbo_charges_Medical_dups_3 mos].icd9cm_code_id
WHERE [Diagnosis Table].Diabetes Is Not Null AND icd9cm_code_id_3 Is Not Null
UNION ALL
[dbo_charges_Medical_dups_3 mos].person_id, [dbo_charges_Medical_dups_3 mos].person_nbr,
[dbo_charges_Medical_dups_3 mos].last_name, [dbo_charges_Medical_dups_3 mos].first_name,
[dbo_charges_Medical_dups_3 mos].date_of_birth, [dbo_charges_Medical_dups_3 mos].create_timestamp,
[dbo_charges_Medical_dups_3 mos].location_name, [Diagnosis Table].Diabetes, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id_4
FROM [Diagnosis Table] INNER JOIN [dbo_charges_Medical_dups_3 mos] ON [Diagnosis Table].Code = [dbo_charges_Medical_dups_3 mos].icd9cm_code_id
WHERE [Diagnosis Table].Diabetes Is Not Null AND icd9cm_code_id_4 Is Not Null;
This puts your data into a more normalized format so you can query against just one column.
Duane Hookom, MVP
MS Access
I would first create a normalizing union query with SQL like:
=== quniICD9 =======
SELECT [dbo_charges_Medical_dups_3 mos].person_id, [dbo_charges_Medical_dups_3 mos].person_nbr,
[dbo_charges_Medical_dups_3 mos].last_name, [dbo_charges_Medical_dups_3 mos].first_name,
[dbo_charges_Medical_dups_3 mos].date_of_birth, [dbo_charges_Medical_dups_3 mos].create_timestamp,
[dbo_charges_Medical_dups_3 mos].location_name, [Diagnosis Table].Diabetes, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id
FROM [Diagnosis Table] INNER JOIN [dbo_charges_Medical_dups_3 mos] ON [Diagnosis Table].Code = [dbo_charges_Medical_dups_3 mos].icd9cm_code_id
WHERE [Diagnosis Table].Diabetes Is Not Null
UNION ALL
[dbo_charges_Medical_dups_3 mos].person_id, [dbo_charges_Medical_dups_3 mos].person_nbr,
[dbo_charges_Medical_dups_3 mos].last_name, [dbo_charges_Medical_dups_3 mos].first_name,
[dbo_charges_Medical_dups_3 mos].date_of_birth, [dbo_charges_Medical_dups_3 mos].create_timestamp,
[dbo_charges_Medical_dups_3 mos].location_name, [Diagnosis Table].Diabetes, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id_2
FROM [Diagnosis Table] INNER JOIN [dbo_charges_Medical_dups_3 mos] ON [Diagnosis Table].Code = [dbo_charges_Medical_dups_3 mos].icd9cm_code_id
WHERE [Diagnosis Table].Diabetes Is Not Null AND icd9cm_code_id_2 Is Not Null
UNION ALL
[dbo_charges_Medical_dups_3 mos].person_id, [dbo_charges_Medical_dups_3 mos].person_nbr,
[dbo_charges_Medical_dups_3 mos].last_name, [dbo_charges_Medical_dups_3 mos].first_name,
[dbo_charges_Medical_dups_3 mos].date_of_birth, [dbo_charges_Medical_dups_3 mos].create_timestamp,
[dbo_charges_Medical_dups_3 mos].location_name, [Diagnosis Table].Diabetes, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id_3
FROM [Diagnosis Table] INNER JOIN [dbo_charges_Medical_dups_3 mos] ON [Diagnosis Table].Code = [dbo_charges_Medical_dups_3 mos].icd9cm_code_id
WHERE [Diagnosis Table].Diabetes Is Not Null AND icd9cm_code_id_3 Is Not Null
UNION ALL
[dbo_charges_Medical_dups_3 mos].person_id, [dbo_charges_Medical_dups_3 mos].person_nbr,
[dbo_charges_Medical_dups_3 mos].last_name, [dbo_charges_Medical_dups_3 mos].first_name,
[dbo_charges_Medical_dups_3 mos].date_of_birth, [dbo_charges_Medical_dups_3 mos].create_timestamp,
[dbo_charges_Medical_dups_3 mos].location_name, [Diagnosis Table].Diabetes, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id_4
FROM [Diagnosis Table] INNER JOIN [dbo_charges_Medical_dups_3 mos] ON [Diagnosis Table].Code = [dbo_charges_Medical_dups_3 mos].icd9cm_code_id
WHERE [Diagnosis Table].Diabetes Is Not Null AND icd9cm_code_id_4 Is Not Null;
This puts your data into a more normalized format so you can query against just one column.
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 14 Dec 2015 11:24:00 -0800
Subject: [MS_AccessPros] Diagnosis searching off charge table
I have a table that I created that identifies ICD codes by the diagnosis (ie code E11 had a1 in the Diabetes column,
then a table of charges (not created by me created by EMR company) on that table are 4 columns that could have the codes that would be a diabetic code
I created a query to sort out the charges so only the medical charges would show up (we have other programs)
that table I called dbo_charges_medical_dups_3mos (for whatever reason a person for a particular visit could have two lines of codes for the same visit date -so dups is there for my mind)
So I have linked the Diagnosis table I created to the dbo_charges_medical_dups_3mos, but I can't seem to figure out how I can pull all the patients that could have a diabestes code in one of 4 columns, I have written it to get those with the first column but I can't seem to figure out how to get the other 3 columns. I also would like to see the patient show up only once no matter the number of times that they may have had a visit that included a diabetic code if that is at all possible.
here is my code so far:
SELECT [dbo_charges_Medical_dups_3 mos].person_id, [dbo_charges_Medical_dups_3 mos].person_nbr, [dbo_charges_Medical_dups_3 mos].last_name, [dbo_charges_Medical_dups_3 mos].first_name, [dbo_charges_Medical_dups_3 mos].date_of_birth, [dbo_charges_Medical_dups_3 mos].create_timestamp, [dbo_charges_Medical_dups_3 mos].location_name, [Diagnosis Table].Diabetes, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id_2, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id_3, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id_4
FROM [Diagnosis Table] INNER JOIN [dbo_charges_Medical_dups_3 mos] ON [Diagnosis Table].Code = [dbo_charges_Medical_dups_3 mos].icd9cm_code_id
WHERE ((([Diagnosis Table].Diabetes) Is Not Null));
I know someone is able to give me a hand on this, I am hoping I have described the situation clearly enough for you if not please reply and I will answer any questions.
thank you so much
Patty
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 14 Dec 2015 11:24:00 -0800
Subject: [MS_AccessPros] Diagnosis searching off charge table
I have a table that I created that identifies ICD codes by the diagnosis (ie code E11 had a1 in the Diabetes column,
then a table of charges (not created by me created by EMR company) on that table are 4 columns that could have the codes that would be a diabetic code
I created a query to sort out the charges so only the medical charges would show up (we have other programs)
that table I called dbo_charges_medical_dups_3mos (for whatever reason a person for a particular visit could have two lines of codes for the same visit date -so dups is there for my mind)
So I have linked the Diagnosis table I created to the dbo_charges_medical_dups_3mos, but I can't seem to figure out how I can pull all the patients that could have a diabestes code in one of 4 columns, I have written it to get those with the first column but I can't seem to figure out how to get the other 3 columns. I also would like to see the patient show up only once no matter the number of times that they may have had a visit that included a diabetic code if that is at all possible.
here is my code so far:
SELECT [dbo_charges_Medical_dups_3 mos].person_id, [dbo_charges_Medical_dups_3 mos].person_nbr, [dbo_charges_Medical_dups_3 mos].last_name, [dbo_charges_Medical_dups_3 mos].first_name, [dbo_charges_Medical_dups_3 mos].date_of_birth, [dbo_charges_Medical_dups_3 mos].create_timestamp, [dbo_charges_Medical_dups_3 mos].location_name, [Diagnosis Table].Diabetes, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id_2, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id_3, [dbo_charges_Medical_dups_3 mos].icd9cm_code_id_4
FROM [Diagnosis Table] INNER JOIN [dbo_charges_Medical_dups_3 mos] ON [Diagnosis Table].Code = [dbo_charges_Medical_dups_3 mos].icd9cm_code_id
WHERE ((([Diagnosis Table].Diabetes) Is Not Null));
I know someone is able to give me a hand on this, I am hoping I have described the situation clearly enough for you if not please reply and I will answer any questions.
thank you so much
Patty
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar