Access 2010 SQL aggregate Help!

[dinodeserter]

This is my first posting on here! I have a form that lets nursing leaders see who has seen patients in a month by selecting the month with a combo and then viewing it in a subform. I have realized that I want to show the number of times each person has 'rounded' on patients in that month and then the percentage of the total seen for the month. Here is a copy of my SQL so far. The names of the forms are strange because I use a GUI menu that renames them when it's open.

SELECT tblPatient.DateofRounding, [tblNurse Rounding Leader].NRLLastName, [tblNurse Rounding Leader].NRLFirstName
FROM [tblNurse Rounding Leader] INNER JOIN tblPatient ON [tblNurse Rounding Leader].[Nurse Rounding LeaderID] = tblPatient.fk_LeaderInitials
WHERE (((Month([DateofRounding]))=[Forms]![frmMain_expandlg]![Subform1]![cboMonthRPT]));

Thanks
Chris

[Dal Jeanis]

1) In real life terms, what is "rounding"?
2) I assume that tblPatient includes a record each for multiple rounds per day
3) is it valid to assume that a patient will never be in the database in June 2012 and also June 2013, so that Month is sufficient?

Assuming that your code above is getting you the records that you want, I would add two more fields -
A) The patientID,
B) 1 As RoundCount

I might also bring back the month() and the NRLID explicitly as fields.

So, each record that comes back from the query has
patient ID, Month(DateofRounding), dateofRounding, NurseRoundingLeaderID, NRLLastName, NRLFirstName, RoundCount (which is 1)

Given that query, you can do SUMs and AVGs and your Percent. You can also use PivotTable functionality by patient, NRLID,date, and so on.

[dinodeserter]

OK, so for my table I would add a field called RoundCount and use that for my percentages and calculations? Can you give me an example of how the SQL would look?
Thanks!
Oh, so rounding is when the administrators in a hospital go and see patients to make sure they are happy so they won't get a bad survey.
Here is a sample picture of my table

[dinodeserter]

Basicaly there are only two tables which includes this one for the patients and a separate table to the actual people doing the rounding. There only needs to be one record for each time a patient is rounded on and it doesn't matter if there are duplicate last names because they will be on a different day. This is really separated by the dates since one day a patient may be happy and the next they may be upset about something. The administration also wants to track who is 'rounding' and when they are doing it hence the combo box for quick metrics.

[Dal Jeanis]

You don't add the field RoundCount to the table, you just "magick" it into the primary query, with a value of 1 and a fieldname of RoundCount. That way you can easily do analysis on the field using aggregate functions. It's not absolutely necessary, since you can usually build the same information into the aggregate query, but it's sometimes useful to have it returned in a simple query, then do the complicated calculations on the query results, rather than the underlying table.

While trying to code you a crosstab/Pivot query for this, I realized that I don't actually understand your table here. With patientID as an autonumber key field, that means you can only have one record per patient. A patient can only be rounded once. Is that right?

I would expect that you need a tblPatient that describes the patient himself/herself, and a tblRounds table that describes what actually happens on each Round that occurs. Or am I minsunderstanding your intention?

[dinodeserter]

You don't add the field RoundCount to the table, you just "magick" it into the primary query, with a value of 1 and a fieldname of RoundCount. That way you can easily do analysis on the field using aggregate functions. It's not absolutely necessary, since you can usually build the same information into the aggregate query, but it's sometimes useful to have it returned in a simple query, then do the complicated calculations on the query results, rather than the underlying table.

While trying to code you a crosstab/Pivot query for this, I realized that I don't actually understand your table here. With patientID as an autonumber key field, that means you can only have one record per patient. A patient can only be rounded once. Is that right?

I would expect that you need a tblPatient that describes the patient himself/herself, and a tblRounds table that describes what actually happens on each Round that occurs. Or am I minsunderstanding your intention?

Each round is one instance or visit for a patient. So a nursing leader may see one patient in the morning and then something else happens and another person from administration sees the patient for something else. This way they can see that this patient was seen by Joe Schmoe for a complaint about the food and food service was contacted. Since he may leave tommorow and never return I don't really need to save his name. I could have a separate table for the patients and one for the rounding but I don't think the tables need to be that complicated. My other table tlblNurse Rounding Leader just has all the names of the administrators who round and whether they are a core or alternative rounder.

[Dal Jeanis]

THE ROUND TABLE

If each round is one visit for a patient, then the table you are showing me is the "tblRound" table, not the patient table, and the information about that patient that can not change on every round should be moved to a separate table. I would strongly suggest that the table be renamed to properly reflect the *entity* that the record represents, which is a single visit by a nurse or administrator to a patient. The autonumber key that you are calling "PatientID" is really a unique ID for that Round on that Patient, and should be renamed to RoundID.

There's nothing wrong with the table structure, really - only three fields seem like they could be moved out (patient last name, VIP flag, and room number), so I don't see that your tables are too far out of normal form. Since a patient may be moved, it's proper to have room number on the Round.

BUSINESS NEEDS CONFIRMATION

1) Is it truly a characteristic of the place you work that you never have two patients with the last name on the same day? I can't imagine that being the case, even allowing for the fact that dups will usually be in different rooms. What happens when a family is in a car accident?

2) Is this data going to be destroyed for HIPPAA reasons after a few days, or will your legal department someday be asking you what interactions the staff had with "Juan Castro" back in late February of 2013?

The whole point of business analysis (what I do) is to make sure that you give the business all the "free" capability that you can, at the design phase. By artificially reducing the storage of info on which precise patient is being rounded, you are making your later retrieval of that information difficult.

"Juan Castro" sues the hospital, and you have to retrieve the records for the legal department. Are you going to give them all records for "Castro" in the months in question?

Figure out ASAP whether and for how long you are required to keep the information. If you are, you'll need to create at least a minimal Patient table, with full name and some way to reference the real person for later research purposes. If you are not, then you will probably need to create a periodic archive/delete of the information. Either design decision should be vetted by an administrator who regularly deals with legal issues.

AND NOW, YOUR TECHNICAL QUESTIONS

I'm going to grab some lunch, then I'll have some demo queries for you in a short while. What you are looking for is Crosstab Queries and Pivot Queries. Here's some good example references:

http://social.msdn.microsoft.com/For...2-56af47e0aa3f
http://www.access-programmers.co.uk/...d.php?t=222233
http://www.access-programmers.co.uk/...d.php?t=214504

Here's the general form of a Crosstab/Pivot Query

PARAMETERS  (any parameters being passed in to the where clause)  - Apparently Not Supported in Access, use temp variables instead in Access 2010+ 
TRANSFORM   (the aggregate calculation for what you want)
SELECT      (the fields to group by, and 
             the fields that will be used to calculate what you want, up in the transform)
FROM        (query)  
WHERE       (any limits on the query)   
GROUP BY    (the fields to group by)  
PIVOT       (what the columns will be - does not have to be in the SELECT itself)

As near as I can tell, the FROM has to be a table or query, not a JOIN.

[dinodeserter]

Wow!! Thanks for all the great advice!! I really appreciate that. I am a PICC nurse so most of the time I am inserting lines, but I have picked this up as an extra project. I know that for this the legal department likes for us to keep the records for 10 years. The table has another identifier which is unique to each admission. This is the PatientFINNumber which is a unique number assigned to each patient for every admission. So if Juan was here on 6/10/13, when home and returned again and was rounded on his FIN number would be different. This makes it easy to look up in the EMR. I agree with you on changing the name to RoundingID and tblRounding so I don't get things mixed up.

[dinodeserter]

OK, So I looked some things up on crosstab queries (which seem super complicated compared to the select ones!!) and I was able to ask some people and come up with this SQL from a select query. The only thing is that I can't get it to A: show percents for the number of times rounded in the selected month, B: get it to accept the parameters from the combo box for the month number. Mine doesn' the PARAMETERS at the beginning so maybe I'm missing something!

TRANSFORM Count(tblPatient.PatientID) AS theCount
SELECT [tblNurse Rounding Leader].NRLLastName, [tblNurse Rounding Leader].NRLFirstName
FROM [tblNurse Rounding Leader] INNER JOIN tblPatient ON [tblNurse Rounding Leader].[Nurse Rounding LeaderID] = tblPatient.fk_LeaderInitials
GROUP BY [tblNurse Rounding Leader].NRLLastName, [tblNurse Rounding Leader].NRLFirstName, tblPatient.PatientID
PIVOT Month(tblPatient.DateofRounding);

[Dal Jeanis]

Here are the tables I used, in their most trivial form:

Table tblNurse:
NurseID     : Key
NurseName   : Text Name

Table tblPatient:
PatientID   : Key
PatientName : Text Name

Table tblRound:
ID          : Key
NurseID     : Foreign Key to tblNurse
PatientID   : Foreign Key to tblPatient
RoundDate   : Date/Time

Here are two queries. The first, qryNDayRound, will give the total number of rounds that a nurse performed that day. The second, qryPDayRound, will give the total number of rounds that a patient received that day.

Query qryNDayRound: 
   SELECT tblRound.NurseID, tblRound.RoundDate, Count(*) AS NDayRdSum
   FROM tblRound
   GROUP BY tblRound.NurseID, tblRound.RoundDate;

Query qryPDayRound:
   SELECT tblRound.PatientID, tblRound.RoundDate, Count(*) AS PDayRdSum
   FROM tblRound
   GROUP BY tblRound.PatientID, tblRound.RoundDate;

The next query (qryRound1) gives the information for each Round, along with three extra anallysis fields at the end -
* RdCount is 1, for the Round itself.
* RdCountP is the number of rounds that the same patient received that day, from qryPDayRound,
* RdCountN is the number of rounds that nurse did that day, from qryNDayRound.

qryRound1:
SELECT 
   tblNurse.Nursename AS RdNurse, 
   tblNurse.NurseID AS RdNurseID, 
   tblPatient.PatientName AS RdPatient, 
   tblPatient.PatientID AS RdPatientID, 
   tblRound.RoundDate AS RdDate, 
   1 AS RdCount, 
   QP.PDayRdSum AS RdCountP, 
   QN.NDayRdSum AS RdCountN
FROM 
   tblPatient INNER JOIN 
      (tblNurse INNER JOIN 
         (qryNDayRound AS QN INNER JOIN 
            (qryPDayRound AS QP INNER JOIN tblRound 
             ON ((QP.RoundDate = tblRound.RoundDate) 
             AND (QP.PatientID = tblRound.PatientID))) 
          ON ((QN.RoundDate = tblRound.RoundDate) 
          AND (QN.NurseID = tblRound.NurseID)) 
      ON tblNurse.NurseID = tblRound.NurseID) 
   ON tblPatient.PatientID = tblRound.PatientID;

Here is the final query that gives you your analysis. This particular one is coded to give you the percent of the visits that day for that one patient which were performed by a given Nurse.

qryRound1_Crosstab:
   TRANSFORM Sum(QR.RdCount/QR.RdCountP) AS PctOfPDayRdCount
   SELECT QR.RdNurse, QR.RdPatient, Sum(QR.RdCount) AS RdCount
   FROM qryRound1 AS QR
   GROUP BY QR.RdNurse, QR.RdPatient
   PIVOT Format([QR.RdDate],"mm/dd");

Given this example, you should be able to work out literally any kind of reporting that you want.
Some big things to note -

(1) Crosstabs are extremely picky. The reason that qryNDayRound and qryPDayRound had to be written was because the crosstab repeatedly objected to every formulation that I tried with the subselects being in qryRound1, even though qryRound1 didn't have any problems with them.

(2) The TRANSFORM statement is overriding the detail that appears in the query matrix, so you can do anything from minor formatting in the SQl itself, for instance

 
   TRANSFORM Format(Sum(QR.RdCount/QR.RdCountP),"00%") AS PctOfPDayRdCount

to changing the output completely to read "1 OF 3 = 33%"

TRANSFORM IIF(Sum(QR.RdCount)>0,(Sum(QR.RdCount) & " of " & Max(QR.RdCountP) & "  =  " &Format(Sum(QR.RdCount/QR.RdCountP),"00%")),"") AS PctOfPDayRdCount

(3) Every term in the TRANSFORM has to be an aggregate function of some sort, which explains why I'm choosing the Max of QR.RdCountP even though they will all be the same on every record for the same patient in the same day.

[Dal Jeanis]

The basic thing to remember for a Pivot Query is that the output from the Transform you see will be a single aggregate calculation made across the subset of the query results that end up in a particular position in the matrix. And ONLY those records.
The underlying query record has to have the information to determine where in the matrix that record will show up, and the information to support the transform. On each record.
So, If the number we want to see is the percent of Rounds

TRANSFORM Sum(QR.RdCount/QR.RdCountP) AS PctOfPDayRdCount

This means that, for each record, we are going to take that record's RdCount, and divide by that record's RdCountP. Then we are going to sum across all those records.

TRANSFORM IIF(Sum(QR.RdCount)>0,(Sum(QR.RdCount) & " of " & Max(QR.RdCountP) & "  =  " &Format(Sum(QR.RdCount/QR.RdCountP),"00%")),"")

This means that, if the sum of all RDCount is greater than zero... meaning there are some records in that subset... then we are going to concatenate together:
1) the sum all the RdCount in the subset,
2) " of ",
3) the max of all the RdCountP subset,
4) the sum of adding up the values of RdCount/RdCountP for each record, then formatted as a percentage.


I'll do some testing on the PARAMETER parm.

[Dal Jeanis]

Looks like PARAMETER is not supported. Use a temp variable.

[Dal Jeanis]

Couldn't quickly find the right syntax for using a temp variable. I suspect TempVars, which are really just another table, may violate the limitation that PIVOTs are limited to one table or query. As such, I just coded a global function to return the value of the temp variable, and the following code worked, when it ran after TempVars have been created for "FirstDate" and "LastDate".

TRANSFORM Sum(QR.RdCount/QR.RdCountP) AS PctOfPDayRdCount
SELECT QR.RdNurse, QR.RdPatient, Sum(QR.RdCount) AS RdCount
FROM qryRound2 AS QR
WHERE ((QR.[RdDate]>= getTemp("FirstDate"))
AND (QR.[RdDate]<= getTemp("LastDate")))
GROUP BY QR.RdNurse, QR.RdPatient
PIVOT Format([QR.RdDate],"mm/dd");

Function getTemp(strVar As String) As String
     getTemp = TempVars.Item(strVar)
End Function

  TempVars.Add "FirstDate", #1/1/2013#
  TempVars.Add "LastDate", #6/14/2013#

[dinodeserter]

That's a lot to take in!! I'm going to work on this over the weekend and see what I can come up with. I have to go to ICU and put a line in. This has been a great learning experience and I think it really benefits nursing when nurses tackle this kind of project. There is so much we can track if we get away from paper and are able to manipulate our data better!I will post something next week when I get this working since I don't work again till Wednesday. I really appreciate you taking the time to walk me through this.

[Dal Jeanis]

No problem. I've eased right up next to this subject a few times, but never fully been dragged though a PIVOT query in Access.