query to count multiple records as one?

[LMS]

Do I need code to do this, rather than just writing a
query?

I'm working with a table of medical data, where each row
represents a billable procedure performed on a patient.
A single surgery may include several procedures; e.g. the
doctor might fix two or more different valves in the
heart, and fixing each valve is a separate procedure.

My table has records with the following fields:
MD_NAME, PROCEDURE_CODE, DATE, PATIENT_ID, HOSPITAL, CHARGE
etc.

I want to count the number of surgeries done. That is,
for any case where there are multiple records with the
same MD_NAME, DATE, and PATIENT_ID, I want all those
records to count as 1. There is no other indication that
more than one procedure was part of the same surgery.

Is there any way to do this without writing code?

thanks much.

 

[LMS]

I should have added that my results need to include the
PROCEDURE_CODE for all of the procedures done on a given
day, because I need to use them for a subsequent query.
The ideal results would be something like

MD_NAME, DATE, PATIENT_ID,PROCEDURE_CODE_1,
PROCEDURE_CODE_2, etc.

The number of records would give me my count.

thanks.

 

[Tom Ellison]

Your analysis conveys the idea that, if a patient enters the operating
room twice in the same day under the same surgeon, then that is to be
considered as one surgery. From my own experience, this is often not
true. If a patient hemorages after an operation, they may go back
under the knife to correct that. Sometimes a patient may have
multiple minor surgeries in the same day. Before proceeding, you may
want to consider this and confirm it.

If your DATE column is not just the date, but a time when the surgery
began, then this would not be the case. That would work if everything
done in one "operation" is recorded as the same date/time. Is that
the case?

The organization of the table design here is questionable to me. If
one surgery is defined as everything that happens under one surgeon
with one patient under one date/time in an operating room, then this
should be in two separate tables. One table describes the general
parameters of the surgery: Patient, Operating Room Number, Date/Time
Begun. Notice I do not include the Surgeon here, as there could be
more than one, each in charge of different procedures. The procedures
and which surgeon was the principle for each procedure would be
recorded in a subsidiary table of details for that surgery.

Some of the problems you may be having now could be caused by the lack
of analysis on which your application has been based.

However, it is possible to simply query your table like this:

SELECT DISTINCT MD_NAME, [DATE], PATIENT_ID, HOSPITAL
FROM YourTable

Notice I included HOSPITAL as I assume (if it were possible) that two
surgeries at different hospitals would have to be counted as separate.

You could save the above query, then perform your counting on that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[LMS]

Thanks. It's quite possible that my analysis is faulty,
so here is more information.

A brief bit of background: the data I'm working with is
collected for billing purposes and the purpose to which
I'm putting it is related not to billing but rather to
the use of an electronic documentation tool. It's a bad
situation, but it's the only data available to my team.

That said, I recognize that a person may go into the OR
twice on the same day; however, in the particular data I
have that is extremely unlikely (verified independently)
and the assumption is within the acceptable tolerance for
error. Date is unfortunately just the date, without
time or length of surgery, and in all the data I have for
12 months there is no occurrence of a patient being at
more than one hospital on the same day. The data
includes no indication of the specific OR or assisting
team or anything else I can think of to indicate whether
all of the surgeries occurred in the same OR session.

I did in fact write the query you suggested. However, as
indicated in the additional message I posted right
afterward (see below), I have to have not just a count
but a list of all the procedure codes that are counted as
one.

Subsequent post:
I should have added that my results need to include the
PROCEDURE_CODE for all of the procedures done on a given
day, because I need to use them for a subsequent query.
The ideal results would be something like

MD_NAME, DATE, PATIENT_ID,PROCEDURE_CODE_1,
PROCEDURE_CODE_2, etc.

The number of records would give me my count.

Linda

 

[Tom Ellison]

Dear Linda:

You might try the query I suggested to see if it is a step along the
way. Adding multiple columns for the procedure codes would be
possible in a crosstab, but not simple. First, rank all the
procedures for any surgery, perhaps alphabetically, to get a column
with values 1, 2, 3, etc. for the number of procedures performed.
Then you can crosstab on this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[LMS]

Thanks. The query you suggested does work fine to count
the unique surgeries (defined loosely, as you noted). I
don't know any way to rank the procedures except
manually. I think what I'm trying to do should just be
done in code (which I don't know how to write). Thanks.

Linda

 

[Tom Ellison]

Dear LMS:

The ranking can be done in a query quite easily. I wouldn't recommend
anything manual.

SELECT MD_NAME, [DATE], PATIENT_ID, PROCEDURE_CODE
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.MD_NAME = T.MD_NAME AND T1.[DATE] = T.[DATE]
AND T1.PATIENT_ID = T.PATIENT_ID
AND T1.PROCEDURE_CODE < T.PROCEDURE_CODE) AS Rank
FROM YourTable T

Is is possible you would have the same PROCEDURE_CODE twice in the
same surgery? If so, how would you want this to report? That would
be a small problem for the code above.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Linda]

wonderful! thanks!

-----Original Message-----
Dear LMS:

The ranking can be done in a query quite easily. I wouldn't recommend
anything manual.

SELECT MD_NAME, [DATE], PATIENT_ID, PROCEDURE_CODE
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.MD_NAME = T.MD_NAME AND T1.[DATE] = T. [DATE]
AND T1.PATIENT_ID = T.PATIENT_ID
AND T1.PROCEDURE_CODE < T.PROCEDURE_CODE) AS Rank
FROM YourTable T

Is is possible you would have the same PROCEDURE_CODE twice in the
same surgery? If so, how would you want this to report? That would
be a small problem for the code above.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Thanks. The query you suggested does work fine to count
the unique surgeries (defined loosely, as you noted). I
don't know any way to rank the procedures except
manually. I think what I'm trying to do should just be
done in code (which I don't know how to write). Thanks.

Linda all
the

.