469,943 Members | 2,583 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,943 developers. It's quick & easy.

Manipulating distinct records based on certain conditions

579 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT qryPatientDetail.PatientDetailID,
  2.                 qryPatientDetail.Episode,
  3.                 qryPatientDetail.DischargeDate,
  4.                 NZ([FormName]) AS Form,
  5.                 qryForms.FormName,
  6.                 qryPrograms.ProgramInitials,
  7.                 IIf(([ComplianceCalculationConsideration]=True) Or
  8.                     (IsNull([FormName])),True,False) AS CCC,
  9.                 qryEpisodeDetail.CorrectedDate,
  10.                 IIf(([correcteddate]>([dischargedate]+30)) Or
  11.                     (IsNull([correcteddate]) And
  12.                     (Date()>[dischargedate]+30)),"Not ITF","ITF") AS ITF
  14. FROM            (qryForms RIGHT JOIN ((qryPatientDetail
  15.                 LEFT JOIN qryEpisodeDetail
  16.   ON            qryPatientDetail.PatientDetailID = qryEpisodeDetail.PatientDetailIDFK)
  17.                 LEFT JOIN qryDeficiencyType
  18.   ON            qryEpisodeDetail.DeficiencyIDFK = qryDeficiencyType.DeficiencyTypeID)
  19.   ON            qryForms.FormID = qryEpisodeDetail.FormIDFK)
  20.                 LEFT JOIN qryPrograms
  21.   ON            qryPatientDetail.ProgramIDFK = qryPrograms.ProgramID
  23. WHERE           (((qryPatientDetail.DischargeDate) Between #3/1/2009# And #3/31/2009#))
  25. ORDER BY        qryPatientDetail.PatientDetailID,
  26.                 qryPatientDetail.DischargeDate;
Thanks for the help...
Apr 27 '09 #1
17 2232
1,287 Expert 1GB
It would take some time to decipher that SQL. Do you have a list of your tables and fields, identifying primary and foreign keys?
Apr 27 '09 #2
32,233 Expert Mod 16PB

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 ;)
Apr 27 '09 #3
32,233 Expert Mod 16PB
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.
Apr 27 '09 #4
579 512MB
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...

Apr 28 '09 #5
32,233 Expert Mod 16PB
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.
Apr 28 '09 #6
579 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. ID          DischargeDate           Form               Timeframe
  2. 001         03/01/2009              Evaluation         True
  3. 002         03/04/2009              (Null)             True
  4. 003         03/07/2009              Evaluation         True
  5. 003         03/07/2009              Order              False
  6. 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.

May 1 '09 #7
1,287 Expert 1GB
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.
May 1 '09 #8
579 512MB
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...
May 1 '09 #9
1,287 Expert 1GB
Doesn't Timeframe = False mean that a form is not complete? In that case, something helpful might be like:
Expand|Select|Wrap|Line Numbers
  1. 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.
May 1 '09 #10
579 512MB
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...
May 1 '09 #11
1,287 Expert 1GB
How about this?

Expand|Select|Wrap|Line Numbers
  1. SELECT ID, Count(TimeFrame) AS [Count]
  2. FROM someTable
  3. WHERE TimeFrame = 'False' GROUP BY ID;
Expand|Select|Wrap|Line Numbers
  1. SELECT ID, IIf([Count] > 0, 'Non-compliant', 'Compliant') AS Result
  2. 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.
May 1 '09 #12
579 512MB
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?
May 1 '09 #13
1,287 Expert 1GB
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.
May 4 '09 #14
1,287 Expert 1GB
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblPatients.ID, 
  2.        IIf([count]>0,'Not Compliant','Compliant') AS Result
  3. FROM       qryCountOfFalse 
  4. RIGHT JOIN tblPatients 
  5. ON   qryCountOfFalse.ID = tblPatients.ID;
May 4 '09 #15
579 512MB
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.
May 7 '09 #16
1,287 Expert 1GB
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.
May 7 '09 #17
32,233 Expert Mod 16PB
Suggestion followed - Crosstab queries, multiple joins (?), show all rows.
May 8 '09 #18

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Steve Heath | last post: by
5 posts views Thread by Fred Zuckerman | last post: by
2 posts views Thread by Dixie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.