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: pattykf@cox.net
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar