Manipulating distinct records based on certain conditions

[beacon]

Hi everybody,

[Access 2003]

I'm working with data to determine if records were completed in a given timeframe. I'm trying to figure out how I can assess records with the same ID, but have different data in other fields to, to come up with compliance for that ID.

Here's the gist...the database tracks info for patients at a hospital. Upon discharge, the charts for the patients get assessed to make sure that each of the forms in the chart are compliant. All patients are entered into the database, but only those that have non-compliant forms are entered in a forms table. The patients that have non-compliant forms may have one form or 20 forms that are non-compliant.

My ultimate goal is to create a crosstab query that will show me the number of charts that are compliant (which would be null in this query because no forms = compliant) and the number of forms that are out of compliance for the program that the patient was housed on.

The part I'm having trouble with is taking the data in the query for one patient that has multiple forms, checking each for compliance, and then returning the number of forms that aren't in compliance. If one form isn't compliant, then the entire chart for that patient is out of compliance. I have a couple of expression used to determine compliance and they display ITF or NITF (in time frame/not in timeframe).

Just for good measure, here's my SQL, but I wouldn't be surprised if some of it needs to be changed. I'm mostly including it so everyone can see the fields and expressions used thus far:

SELECT DISTINCT qryPatientDetail.PatientDetailID,
                qryPatientDetail.Episode,
                qryPatientDetail.DischargeDate,
                NZ([FormName]) AS Form,
                qryForms.FormName,
                qryPrograms.ProgramInitials,
                IIf(([ComplianceCalculationConsideration]=True) Or
                    (IsNull([FormName])),True,False) AS CCC,
                qryEpisodeDetail.CorrectedDate,
                IIf(([correcteddate]>([dischargedate]+30)) Or
                    (IsNull([correcteddate]) And
                    (Date()>[dischargedate]+30)),"Not ITF","ITF") AS ITF
 
FROM            (qryForms RIGHT JOIN ((qryPatientDetail
                LEFT JOIN qryEpisodeDetail
  ON            qryPatientDetail.PatientDetailID = qryEpisodeDetail.PatientDetailIDFK)
                LEFT JOIN qryDeficiencyType
  ON            qryEpisodeDetail.DeficiencyIDFK = qryDeficiencyType.DeficiencyTypeID)
  ON            qryForms.FormID = qryEpisodeDetail.FormIDFK)
                LEFT JOIN qryPrograms
  ON            qryPatientDetail.ProgramIDFK = qryPrograms.ProgramID
 
WHERE           (((qryPatientDetail.DischargeDate) Between #3/1/2009# And #3/31/2009#))
 
ORDER BY        qryPatientDetail.PatientDetailID,
                qryPatientDetail.DischargeDate;

Thanks for the help...

[ChipR]

It would take some time to decipher that SQL. Do you have a list of your tables and fields, identifying primary and foreign keys?

[NeoPa]

Beacon,

I've reorganised your SQL so that it's easier to read.

I suggest, any SQL as involved as that is worth formatting to be readable before posting. It's up to you of course, but people are more likely to look at it if it's easier to see what it's saying ;)

[NeoPa]

Right. Now I've read the whole question through a few times to try to get what you're actually asking for, I find that you will almost certainly need to provide some extra information.

To be able to help, we would need two things mainly :

1. An indication of the data that we're expected to work from. The layout of the tables is of main importance.
2. Some clarity about which bit you need help with. What are you looking for in an answer?

If you can provide these bits of information we can have another look to see what we can do for you.

[beacon]

@NeoPa
How did you reorganize it? I saw that it looked funky after I posted, but didn't know if I put =SQL in the code block if it would format correctly.

I'm sorry for not posting sooner, but I had something urgent come across my desk that's going to take precedence over this question until I get it finished. I will post back with more detail as soon as I get the opportunity...

Thanks

[NeoPa]

That's fine, and thanks for letting us know :)

Funky SQL
There are various concepts to use :

1. Wide lines are not easy to read, so I tend to split each field and show below the others in a column.
2. Horizontal layout should always be managed using spaces and never tabs. You cannot rely on the same tab stops being used on the page as the ones when you are laying it out.
3. If it gets very complicated, adding empty lines between the different clauses helps to keep it clear.

I hope this helps.

[beacon]

As I read back my first post, I see that it was both ambitious and ambiguous. Let me start smaller and work my way out to the bigger question.

On my query, I have a field that shows the ID for patients that are entered into the database. There's another field that shows forms that are filled out for the patient during their stay that have corrections that need to be made. If the form field is null, the patient's chart is perfect. If there is a form name, then I have to test to see whether it was corrected in time.

Because the forms may have different things that may need to be corrected, the form may appear multiple times for one patient. What I would like to do first is see if there's a way that I can look at each record for a patient and if it isn't compliant, then flag the entire chart.

Here's an example:

ID          DischargeDate           Form               Timeframe
001         03/01/2009              Evaluation         True
002         03/04/2009              (Null)             True
003         03/07/2009              Evaluation         True
003         03/07/2009              Order              False
003         03/07/2009              Assessment         False
 

As you can see, patient #001 and #002 were completed in the timeframe so their charts are compliant. #001 had something wrong, but it was corrected and #002 was compliant when it arrived (no corrections).

Patient #003 is where I'm having trouble. For the report that I will eventually create when I get the query working, I need to convey that #003's chart is out of compliance because there are two items that haven't been corrected. I need to see that each is correct/incorrect and whether the chart on the whole is correct or not.

I'm sure I will probaby have to create another query, but I've been staring at this thing for so long that I'm just not seeing it.

Thanks...

[ChipR]

@beacon
This sounds to me like you want a report showing the status of every form (correct and incorrect) for every chart (correct and incorrect), but I dont think that is what you intended.

[beacon]

For right now, I would be happy just seeing if the chart is altogether compliant or not.

If #003 has one item that is in the timeframe and two that aren't, then #003's chart is not compliant until those two items are corrected. I want to show that #003's chart (when you add up the status of the forms...like a truth table) is compliant or not.

Does that make sense? Can I look at multiple records that have the same idea and calculate a result based on all the records for that one patient? And can I do it without having to drill down to one specific patient?

Please disregard the title of the thread or any posts before this most recent one...it will only serve to confuse now.

Thanks again...

[ChipR]

Doesn't Timeframe = False mean that a form is not complete? In that case, something helpful might be like:

SELECT ID, Form, Timeframe from myQuery WHERE ID IN (SELECT ID FROM myQuery WHERE Timeframe = 'False')

This just shows the status of all forms for patients with an incomplete form.

[beacon]

You're exactly right Chip...that does tell me if a form is not in the timeframe, but I need to compare all of the forms for one patient to determine if all the forms are in the timeframe. Only if all the forms are in the timeframe is the chart complete.

In my example, #001 has 1 form that was completed in the timeframe, so that chart is complete. For #002, there were no forms, so that chart is automatically complete.

For #003, there were 3 forms, one was completed in the timeframe and two were not. I need to look at all three forms and return true or false for the entire chart. I will still need it for the forms (which I've got)...I just need to take it one step further and determine the status of the chart based on all the forms together.

I hope this makes sense. I may need to call it a day and come back to this later just in case we aren't on the same wavelength because of me. I need a vacation...

[ChipR]

How about this?

qryCountOfFalse

SELECT ID, Count(TimeFrame) AS [Count]
FROM someTable
WHERE TimeFrame = 'False' GROUP BY ID;

qryCompliant

SELECT ID, IIf([Count] > 0, 'Non-compliant', 'Compliant') AS Result
FROM qryCountOfFalse;

Then you get the list of patient IDs, and if they had 1 or more False forms, they are Non-compliant. I hope this is what you are after.

[beacon]

I think we're close. When I create these queries, the last one shows only 'Non-compliant' for all the records. Just for peace of mind, is there a way that I can remove the 'Timeframe=False' so that it will show all charts and 'Non-compliant' and 'Compliant' in the same field?

[ChipR]

You're right, that was silly of me. We will need to keep the 'Timeframe=False' to determine which charts are not compliant, but we can combine it with another query to list them all together. Let me just do a quick test to make sure I have the syntax right.

[ChipR]

Ok, you just need something like tblPatients that has all patient IDs in it, then you can join that with the query that counts the false forms to get the whole list.

SELECT tblPatients.ID, 
       IIf([count]>0,'Not Compliant','Compliant') AS Result
FROM       qryCountOfFalse 
RIGHT JOIN tblPatients 
ON   qryCountOfFalse.ID = tblPatients.ID;

[beacon]

Thanks for your help Chip. That helps me with the first part to a certain extent, but I'm now having a hard time incorporating it with the next part.

To help explain, I've attached a report in snapshot format.

The report is a dynamic cross tab report based on the article at this link, http://support.microsoft.com/kb/328320. The row column is the name of the form. The column heading is the name of the unit/program. The value needs have a number in it once for each patient and only if the form was non-compliant.

The other thing, I have a yes/no field on the forms table that indicates which forms should show up in the forms column. I need these forms to show up in the rows column every time even if there are no values associated with that form. As the report stands right now, the forms only show up if there is a value for one of the programs.

I hope this makes sense...I'm working on putting together a shell database to help explain further.

[ChipR]

Unfortunately, I haven't done anything with cross tab queries, so I won't be much help. It's probably a good idea to create a new topic for this.

[NeoPa]

Suggestion followed - Crosstab queries, multiple joins (?), show all rows.