Query criteria - latest record if the status is AA?

[undefeatedskillz26]

I have a query with a record id, report date and status.

How do I pull the latest record if the status is AA?

For latest record in report date I used Max in Totals. With just this max it is pulling the latest date for each set of records with the same record id.

This is a start now how do i pull the latest record that has a AA status?

Any ideas?

 

[jdraw]

Latest = most current Report Date?

Try

Select * from yourTableName
WHERE status = "AA" And
ReportDate =(Select Max(x.ReportDate) from yourTableName as X)

 

[undefeatedskillz26]

Latest = most current Report Date?

Try

Select * from yourTableName
WHERE status = "AA" And
ReportDate =(Select Max(x.ReportDate) from yourTableName as X)

Hi,

Thank you for the fast reply. Yes I am looking for the latest most recent record to pull up only if it is a P1 status. With your code.

It pulls up a record but only 1. Not sure yet why its pulling this one. If there are 3 records with the same record id of 001 and the first record has status of BB, 2nd status is AA, and the 3rd and latest record is P1 than I want it to show that last record. I hav several like this but only 1 record is being pulled for now with your code.

Let me know if I am not clear. Appreciate anything you can help me with.

 

[jdraw]

There must be something you aren't telling readers.

How do I pull the latest record if the status is AA?

To me --"the latest" -- indicates 1 record.

Please tell us more about the other records you are expecting.

 

[undefeatedskillz26]

There must be something you aren't telling readers.



To me --"the latest" -- indicates 1 record.

Please tell us more about the other records you are expecting.

Thank you for responding.

Yes maybe I am not as clear as I would like to be. So I will tell you my whole story.

I have 2 tables.

Patient
Attribute

The tables have a one to many relationship with record ID

Each Patient has one record in Table Patient , but can have many records in Table attribute.

What I am trying to do is:

Create a Query that gets data from Table Patient:

First
Last
DOB

along with data from table Attribute that shows the

Date
Status
only if the Status is P1

than if the Status has not changed 3 business days later. If the Patient Status is still P1 I would like to have it in the query. If within the 3 busines days the status has changed than that is okay, I do not need it in the query.

So for each patient I am trying to find the p1 status and see if the latest status is still P1 3 business days later. If so bring in query, if than okay.

I hope that I am clear. I will be online if anyone responds to this, has questions etc.

 

[jdraw]

Just curious, where did the "AA" fit in all of this?

Looking at your latest post, it seems that finding 2 records for Patient X with status "P1" where the dates are less than 4 days apart, would be a good start.

Is that realistic, or do you have a better algorithm? I chose 4 but you could use a different number. All this to get a subset of records for further analysis.

Do you have some sample data -- enough to illustrate the data -- remove any real names if required.
We're used to working with John Doe, Donald Duck, General Purpose, or even Patient 100

 

[undefeatedskillz26]

Just curious, where did the "AA" fit in all of this?

Looking at your latest post, it seems that finding 2 records for Patient X with status "P1" where the dates are less than 4 days apart, would be a good start.

Is that realistic, or do you have a better algorithm? I chose 4 but you could use a different number. All this to get a subset of records for further analysis.

Do you have some sample data -- enough to illustrate the data -- remove any real names if required.
We're used to working with John Doe, Donald Duck, General Purpose, or even Patient 100

I only posted AA because I was afraid work may see it and I get in trouble. Right now I can go back and edit it.

I have a question on the algo.

What is there is a Status of:

P1 on 12-01-14
A1 on 12-08-14
P1 on 12-14-14

Then the record should not be pulled into the query because the latest status is P1 and the one before that by date is status A1.

The query should be seeing if there are dates less than 4 days apart as long as the status has not changed prior correct? I really hope I am making sense. Sorry English is not my first language so please forgive me.

You have no idea how thankful I am and how much this is helping me learn.

Okay here is some data, please see attachment. I tried to attach it in here but didnt work so I added as a pic, let me know if you want another format like excel, etc.

For this algorithm to work it should pull record id 1, 4, 6 and 9 correct?

 

[jdraw]

It's like pulling teeth... I realize your first language isn't English, but why give only some of the info.

P1 is NOT P01

RecordID is a poor name for records in multiple tables.

Plain text, csv would be great - as long as it is consistent.

 

[undefeatedskillz26]

It's like pulling teeth... I realize your first language isn't English, but why give only some of the info.

P1 is NOT P01

RecordID is a poor name for records in multiple tables.

Plain text, csv would be great - as long as it is consistent.

Hi jdraw,

I am so sorry I do not mean it to be hard for you. I am trying to make it as simple as possible but looks like my efforts are failing. I cannot or do not know how to save it as csv every time I do it is not saving in right format. So for now here it is in txt file. I will be online so let me know if there is anything I can do. I am extremely humbled by your generosity.

 

[jdraw]

How exactly should I relate Patient to ReportDates?

Sorry, but most date formats did not match mine and were rejected as errors.
There is nothing to bring the 2 tables together.

 

[undefeatedskillz26]

How exactly should I relate Patient to ReportDates?

Sorry, but most date formats did not match mine and were rejected as errors.
There is nothing to bring the 2 tables together.

Doesn't record ID (i know it's poorly named )bring them together?

Record ID 1 which is John Doe shows a report date of 5/16/2014 and 5/19/2014?

I'm so confused, and now am extremley worried cause I cant get this to work. Anything would help at this point.

 

[jdraw]

What I see is you have patients with recordID from 1 to 9 with No 8?
In the TblStateFileAttribute the recordIds go much higher, so they don't relate really.
I'm looking at the data and adjusting to give recordids in table 2 to fit 1-9 no 8.
I've also adjusted the status to get multiple records per recordId.

I don't have any insight to you table structures or real requirements. I'm trying to see if I can get something to accept if P01 and 3 or less days apart on ReportDate and no intervening status, but it's more playing and guessing than strategy.

Maybe someone else will respond.

 

[undefeatedskillz26]

What I see is you have patients with recordID from 1 to 9 with No 8?
In the TblStateFileAttribute the recordIds go much higher, so they don't relate really.
I'm looking at the data and adjusting to give recordids in table 2 to fit 1-9 no 8.
I've also adjusted the status to get multiple records per recordId.

I don't have any insight to you table structures or real requirements. I'm trying to see if I can get something to accept if P01 and 3 or less days apart on ReportDate and no intervening status, but it's more playing and guessing than strategy.

Maybe someone else will respond.

I understand. I wonder if I have to code this. I can sho you my database if you could ghost on my machine or skype or something on instant messanger. Maybe clearer since pricture or video is worth 1000 words. I am thinking of asking someone on freelancer if they can help. I know this can be done but dont know how. Someone said maybe TOP N parameter nested query for most recent but with additional criteria it is hard for me.

I want to say thank you for taking time and trying to solve this. If there is anything I can do to make it easier like ghosting etc, please let me know. Thank you kind sir!!!

 

[jdraw]

top n is important to get Latest records
It's really getting the requirements clearly stated.

My current thinking is
-Get a List of PatientIDs as A
-Get a list of PatientID, ReportDate and P01 status in sequence by
Patient and ReportDate ascending where PatientId= A.patientiD as B
-Process each pair of ReportDates in B for this Patient to see if they are > 3 days apart. If so then doesn't meet your requirement.
- If 3 or less days apart, then check to see if there is a record for this Patient with a non "P01" status and ReportDate between the pair of ReportDates. If such a record exists, then reject since there was an intervening record.
-When your finished with patient go to next Patient record.
- if Finished with Patient List--you're done.

Don't know if that helps, or if it has got all your requirements.

Any way, good luck. Merry Christmas.

 

[undefeatedskillz26]

Thank you and Merry Christmas to you as well!!!

Okay I am trying to make this simpler since I am getting deperate here to come up with an answer.

I need the query to have 2 status for each patient. If both status are the same for each Patient record ID, and is more than 3 business days apart, than I should see it in the query. Is this easier to do?

top n is important to get Latest records
It's really getting the requirements clearly stated.

My current thinking is
-Get a List of PatientIDs as A
-Get a list of PatientID, ReportDate and P01 status in sequence by
Patient and ReportDate ascending where PatientId= A.patientiD as B
-Process each pair of ReportDates in B for this Patient to see if they are > 3 days apart. If so then doesn't meet your requirement.
- If 3 or less days apart, then check to see if there is a record for this Patient with a non "P01" status and ReportDate between the pair of ReportDates. If such a record exists, then reject since there was an intervening record.
-When your finished with patient go to next Patient record.
- if Finished with Patient List--you're done.

Don't know if that helps, or if it has got all your requirements.

Any way, good luck. Merry Christmas.

 

[undefeatedskillz26]

Thank you and Merry Christmas to you as well!!!

Okay I am trying to make this simpler since I am getting deperate here to come up with an answer.

I need the query to have 2 status for each patient. If both status are the same for each Patient record ID, and is more than 3 business days apart, than I should see it in the query. Is this easier to do?

Okay i created a duplicate Query to find duplicated within the same recordid and Status. It seems to work for now. May have a question on the business days beging greater than 3 days, will do some research. Thank you very much for your time and efforts.

 

[jdraw]

Saw your post on AccessForums as well. I think the biggest issue people are having is understanding WHAT you really want.
Perhaps you could try to be more explicit with criteria (all details regarding status) for record acceptance.
Does a Patient have to have 2 status P01 within 3 business days?
If the last status given is P01, and the previous report date is a month earlier??

 

[jdraw]

Looked at your request again. Here is sql to get the latest 2 records by ReportDate for each Patient(recordId) regardless of status. There could be more than 2 records if the ReportDates are equal. I used my own name for the PatientStatus field. This may serve as a starting point for you.

I hope it is useful to you. Still not absolutely clear of requirements.

SELECT TblStateFileAttribute.RecordID
,  TblStateFileAttribute.ReportDate
, TblStateFileAttribute.jPatientStatus
FROM TblStateFileAttribute
Where ReportDate IN
(
 select top 2 reportDate from TblStateFileAttribute as XX
 where TblStateFileAttribute.recordId = xx.recordId 
 Group By xx.RecordId, xx.ReportDate
 Order by xx.RecordId, xx.ReportDate desc
)
GROUP BY TblStateFileAttribute.RecordID
, TblStateFileAttribute.ReportDate
, TblStateFileAttribute.jPatientStatus;

 

[undefeatedskillz26]

Looked at your request again. Here is sql to get the latest 2 records by ReportDate for each Patient(recordId) regardless of status. There could be more than 2 records if the ReportDates are equal. I used my own name for the PatientStatus field. This may serve as a starting point for you.

I hope it is useful to you. Still not absolutely clear of requirements.

SELECT TblStateFileAttribute.RecordID
,  TblStateFileAttribute.ReportDate
, TblStateFileAttribute.jPatientStatus
FROM TblStateFileAttribute
Where ReportDate IN
(
 select top 2 reportDate from TblStateFileAttribute as XX
 where TblStateFileAttribute.recordId = xx.recordId 
 Group By xx.RecordId, xx.ReportDate
 Order by xx.RecordId, xx.ReportDate desc
)
GROUP BY TblStateFileAttribute.RecordID
, TblStateFileAttribute.ReportDate
, TblStateFileAttribute.jPatientStatus;

This will be very helpful. I am going to have an outside person help me with this as I have a deadline to get this out. I can learn from someone helping me with the code. I will show them this as this maybe a solution. Thank you very very much.

 

[undefeatedskillz26]

Saw your post on AccessForums as well. I think the biggest issue people are having is understanding WHAT you really want.
Perhaps you could try to be more explicit with criteria (all details regarding status) for record acceptance.
Does a Patient have to have 2 status P01 within 3 business days?
If the last status given is P01, and the previous report date is a month earlier??

Yes the purpose of this is to show someone that has a P01 status. If his status is idle for to long (more than 3 business days) than a query would be run each day to show that his status is idle and something needs to happen.)

So if I get the last status or more recent status for each patient and the status has not changed from P01 to A01 or anything else but still is P01 than it would need to go to the query so we can do some sort of follow up.

Is that clearer? If you have any questions please let me know. As you ask it is easier for me to determine what is needed. This is my first big project and just need to get it done right.