Query help

[error123]

Hi,

I have a table that contains a field cath_procedures.
In this field I found all the procedures that was done on a given event ( I get the date from the event_cath table).
When the even starts in the lab, the recorder selects the procedures from the drop down combo box.
The event can start with a diagnostic procedure, so the recorder picks a diag.procedure.
Then, the next minute the doctor can decide on the interventional procedure. So, the recorder picks the procedure in the combo box called PCI or PCI:*
Than the doctor can decide to have a stent done and that is another Intervention. So, the recorder picks the procedure Stent - coronary in the 3rd field of the combo box.
The problem is that when I query the doctor for all the interventions Like "*PCI*" AND Like "Stent - Coronary" it gives me duplicate dates and patient names, because - again the procedures in one table.
I would like to know how to create this query, that it would show the doctors name, patient_id, pt_name, and only once the event if it was an Intervention (even if they recorded 2 different kind of Interventional procedures that time).
I am calculating with only "Scheduled & Performed" and "Performed" from the cath_extension table.

Thank you!!!!

 

[dhookom]

Can you provide your table structure and some sample records? Are you storing multiple values in a single field?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[error123]

SELECT Demographics.Patient_ID, Demographics.Last_Name, Event_Cath.Date_of_Cath, Event_Cath.Cath_Fellow, Cath_Procedures.Procedure_Name, Report_List.Status
FROM ((Demographics INNER JOIN Event_Cath ON Demographics.SS_Patient_ID = Event_Cath.SS_Patient_ID) INNER JOIN Cath_Procedures ON Event_Cath.SS_Event_Cath_ID = Cath_Procedures.SS_Event_Cath_ID) INNER JOIN Report_List ON Demographics.SS_Patient_ID = Report_List.SS_Patient_ID
WHERE (((Event_Cath.Cath_Fellow) Like "*" & [LastName] & "*"))
GROUP BY Demographics.Patient_ID, Demographics.Last_Name, Event_Cath.Date_of_Cath, Event_Cath.Cath_Fellow, Cath_Procedures.Procedure_Name, Report_List.Status
HAVING (((Event_Cath.Date_of_Cath) Between [Start_Date] And [End_Date]) AND ((Cath_Procedures.Procedure_Name) Like "*PCI*" Or (Cath_Procedures.Procedure_Name) Like "Ballon*" Or (Cath_Procedures.Procedure_Name)="Like Stent-Coronary") AND ((Report_List.Status)="Final"));


The problem is how it is recorded during the procedure. Th epatient is on the table and they start with a PCI, then they put another procedure in called Stent-Coronary.
But, it really count as only 1 Interventional procedure, because Interventional procedures are : "*PCI*", "Stent - Coronary", "Ballon Angioplasty".

When I queried this I got the dupliate records, because I patient had multiple Interventional procedures recorded during the same cath event.
I would like to see how many Interventional procs a doctor had. I would like to count only the events like 1/1/2006, PT_Name, Intervention(can not show up twice if doctor did a Stent - Coronary as well as a PCI).
Maybe it is not possible to count and list the Interventions at the same time...

Thank you!!!

 

[lespaul]

ok, that's the query, how about some sample data from Demographics, Cath_Procedures and Report_List and your expected results....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[error123]

Ok!

Demographics tbl:

SS_Patient_ID PK Autonumber
SS_Parent_ID Number
Last_Name text
First_Name text
Patient_ID text <---MR#

Event_Cath tbl:

SS_Patient_ID Number
SS_Event_Cath_ID AutoNumber PK
Account_Number Text
Date_of_Cath Date/Time
Cath_Attending Text
Cath_Attending2 Text
Cath_Fellow Text
Referring_MD Text
RefeeringMD2Second Text
Case_Start Date/Time
Case_End Date/Time
SS_ACC_CATH Text <----These ACC tables always pop up and I am just tryoing to figure out their purpose...
338442
15
SS1-00086
27048057
9/25/2001
Test, Attending <--always does Diagnostic procs
Test, Attending2 <--once proced.turns into Intervention the Interventional doctor comes here, I think.
Many times, procedure starts as a scheduled Diagnostinc procedure, then it turnes into an Intervention (Stent - Coronary and a PCI as well). Doctor at that time wants to count this event only one intervention.

---I have the same "logic" with the reports run off a report table. The fellow runs one report but had t different procedures on a given date on a given patient.
I am getting duplicate patient names, because I get the 2 different procedure names for that 1 "Standard Cath Report" that the fellow run.

Report_List Table:

PK AutoNumber SS_Report_ID, 378 or 1878 or 21323 ...
SS_Patient_ID 44309, 19413...
SS_Event_ID 41, 41, 42, 42, 43, 43, 43...
SS_Patient_Reports_ID, 52, 27, 52, 27 (27 is forSatndard Cath Report and 36 is for Transesophageal Echo).

Title, Standard Cath Report,Resting Echo,Holter Report
Event, Even_Cath, Event_Echo
DateofEvent,
DateofFinal,
Status, Preliminary, Final
Created_By



Cath_Procedures tbl:
SS_Event_Cath_ID Number
SS_Cath_Procedure_ID AutoNumber PK
Procedure_Name text
SchedulingType Text

15
9
Right and left Heart Cath with coronary angiography
Performed

17
11
Balloon Angioplasty
Scheduled

18
12
Stent
Scheduled & performed

Thank you!!!!!!!