UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Combine Several Rows Into One, Access 2010    
 
   
ehartshorn
post Jan 21 2020, 01:30 PM
Post#1



Posts: 3
Joined: 21-January 20



I am working with medical data, so must be careful how I post this. My data is stored in a hosted SQL database, and I have limited access via an Access front end. I did not design the database, and I cannot change the database, nor write reports. I can, however, write Access queries.

In this particular case, I have a Demographics Table with Patient Identifiers, an Event Table with Event identifiers and personnel, then I have a Medications table. Each medication is stored in a single row of the medications table, so a Patient with one event may have 20 rows in the Medications table.

In particular, I need to find whether each patient received either an ACE or an ARB and a Beta Blocker:

"If "ACE Inhibitor"="Yes - Prescribed" AND "Beta Blocker" ="Yes - Prescribed" OR If "ARB"="Yes - Prescribed" AND "Beta Blocker" ="Yes - Prescribed" THEN "Meds Prescribed".

SELECT DISTINCT Demographics.Patient_ID, Event.EventDate, Event.Physician, Event.ProcedureType, IIf([Medication]="ACE Inhibitor",[MedicationAdministration]) AS [ACE-I], IIf([Medication]="ARB",[MedicationAdministration]) AS Angiotensin, IIf([Medication]="Beta Blocker",[MedicationAdministration]) AS Beta

FROM (Demographics INNER JOIN Event ON Demographics.SS_Patient_ID = Event.SS_Patient_ID) LEFT JOIN Medications ON Event.SS_Event_ID = Medications.SS_Event_ID

WHERE (((Event.EventDate) Between #10/1/2018# And #9/30/2019#));

Results in attached screenshot.
Attached File  Capture.JPG ( 80.34K )Number of downloads: 12


Select DISTINCT changes from getting 15 blank rows of other meds into one row with blank meds.

Problem: How do I get rid of the blank row, and get all the other meds for each individual patient into one row instead of 3 separate rows?

Bonus: If ACE or ARB (Angiotensin) and BB prescribed, get a column that shows "Meds Prescribed"
Go to the top of the page
 
theDBguy
post Jan 21 2020, 01:46 PM
Post#2


UA Moderator
Posts: 77,506
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

One way to do that is to create four separate queries and then combine them using another query.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Jan 21 2020, 02:00 PM
Post#3


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


theDBGuy's approach would work, but I would probably also consider doing this as a crosstab that filters records returned by the three meds that you need to report on.

Because you can't provide sample data, I might take a whack at something based on what you have been able to post.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jan 21 2020, 02:04 PM
Post#4


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


I wonder if the date the meds were administered is a factor in deciding whether "a Meds Prescribed incident" occurred.

In other words, do you want this for each event date, or once for all event dates?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
June7
post Jan 21 2020, 02:29 PM
Post#5



Posts: 1,222
Joined: 25-January 16
From: The Great Land


Build a CROSSTAB or apply GROUP BY with Max() function to each calculated field instead of DISTINCT.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
ehartshorn
post Jan 21 2020, 02:50 PM
Post#6



Posts: 3
Joined: 21-January 20



June7: I will try the GROUP BY Max().

June7 and GroverParkGeorge: I will try Crosstab

GroverParkGeorge: Once for each patient for each event date as illustrated:
Attached File  Capture2.JPG ( 45.52K )Number of downloads: 2


theDBguy: I will try writing a query for each med separately, then a query on those queries.

Thanks, you've given me several things to try, I will post back with results. I have a tendency to get stuck in a rut sometimes, hoping this will break me out.
Go to the top of the page
 
ehartshorn
post Jan 21 2020, 05:06 PM
Post#7



Posts: 3
Joined: 21-January 20



Tried several of the solutions, what worked easiest for me was to create 4 queries: 1 for each med, then a 4th that queried the results of the other 3. Don't have a quick way to get the "If ACE or ARB (Angiotensin) and BB prescribed, get a column that shows "Meds Prescribed"", but I can easily do that in Excel.

Thanks for all your help!
Go to the top of the page
 
theDBguy
post Jan 21 2020, 05:14 PM
Post#8


UA Moderator
Posts: 77,506
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got is sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Jan 21 2020, 06:19 PM
Post#9


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


A bit late to the party, but look at this and let me know what needs to be adjusted to work for you.

Attached File  MedsUA.zip ( 24.74K )Number of downloads: 0

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st February 2020 - 09:30 AM