In my contracting days I saw many table structures that were similar particularly in the healthcare industry. It's prevalent in insurance, mortgage, and other industries. That's why it's important to understand how to create union queries ;-)
In your particular case, I would have most likely created the union query on just the dbo_charges_Medical_dups_3 mos table/query and added the [Diagnosis Table] in a later query.
Duane
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 14 Dec 2015 12:27:21 -0800
Subject: RE: [MS_AccessPros] Diagnosis searching off charge table
Duane
thank you so much for walking me though this one. Unfortunately that table I did not create so I am stuck with horrible design issues (Electronic Health Records are not designed for the data people in mind). But individuals like yourself are a huge asset to those of us that struggle to make since of the volume of data they have.
I can't thank you enough for your help. I will now be able to use this to cull out other diagnosis from all that data.
THANK YOU have a wonderful holiday season
Patty
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
In your particular case, I would have most likely created the union query on just the dbo_charges_Medical_dups_3 mos table/query and added the [Diagnosis Table] in a later query.
Duane
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 14 Dec 2015 12:27:21 -0800
Subject: RE: [MS_AccessPros] Diagnosis searching off charge table
Duane
thank you so much for walking me though this one. Unfortunately that table I did not create so I am stuck with horrible design issues (Electronic Health Records are not designed for the data people in mind). But individuals like yourself are a huge asset to those of us that struggle to make since of the volume of data they have.
I can't thank you enough for your help. I will now be able to use this to cull out other diagnosis from all that data.
THANK YOU have a wonderful holiday season
Patty
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
Patty,
You are getting it.
Duane
You are getting it.
Duane
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 14 Dec 2015 12:19:37 -0800
Subject: RE: [MS_AccessPros] Diagnosis searching off charge table
Oh My gosh that would make since, now I understood why it was having an issue, that makes since to me
now to make sure that I correctly understand the product of this.
what it does is pull the information from all 4 of the columns and gives me a list of the rows that meet the Diabetes criteria from the diagnosis table showing one column that is the code column even though before there were 4 potential columns of data?
Did I get the basics of what the normalizing does?
thank you
Patty
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 14 Dec 2015 12:19:37 -0800
Subject: RE: [MS_AccessPros] Diagnosis searching off charge table
Oh My gosh that would make since, now I understood why it was having an issue, that makes since to me
now to make sure that I correctly understand the product of this.
what it does is pull the information from all 4 of the columns and gives me a list of the rows that meet the Diabetes criteria from the diagnosis table showing one column that is the code column even though before there were 4 potential columns of data?
Did I get the basics of what the normalizing does?
thank you
Patty
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
Looks like my copy and paste left off some "Select":
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
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_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
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_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
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_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;
Duane
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
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_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
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_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
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_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;
Duane
To: ms_access_professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 14 Dec 2015 13:48:24 -0600
Subject: RE: [MS_AccessPros] Diagnosis searching off charge table
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 14 Dec 2015 13:48:24 -0600
Subject: RE: [MS_AccessPros] Diagnosis searching off charge table
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 (9) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar