Query criteria - latest record if the status is AA?

[jdraw]

Spend your time defining exactly WHAT the conditions for
a) accepting a record, and
b) rejecting a record.

It still is not clear. Can you describe the set up, in plain simple English - forget queries etc.?

You really need concrete examples because your conditions/criteria are not clear. Show us a clear example for accept and one for reject.

Be very, very explicit in WHAT causes a record to be accepted or rejected.

Remember this: If you can't define it, not you nor anyone else can build/program it.

 

[jdraw]

undefeatedskillz26,

I am attaching a database with some test data that has been manipulated to give a number of records and statuses. In the database in module1 is a routine "a" that has logic to process the test data based on a query that returns exactly 2 records per patient based on the latest 2 reportDates.

It should serve as a starting point for your real data and proper algorithm.

You can review the test data via the query, and run the routine "a" from module1.

Accepted/rejected records are identified in the immediate window.

Good luck and Merry Christmas

 

[undefeatedskillz26]

undefeatedskillz26,

I am attaching a database with some test data that has been manipulated to give a number of records and statuses. In the database in module1 is a routine "a" that has logic to process the test data based on a query that returns exactly 2 records per patient based on the latest 2 reportDates.

It should serve as a starting point for your real data and proper algorithm.

You can review the test data via the query, and run the routine "a" from module1.

Accepted/rejected records are identified in the immediate window.

Good luck and Merry Christmas

Hi jdraw,

You have gone beyond kindness to go this far to help me. Thank you very much from the botton of my heart,.

I eneded up writing a query with a friend this is what we did and it worked. We ran a sub query with an inner join. The report over all checks the last status for each record to see if the status is P01. If so it check it with the current date, if it is over 3 days it flags it.

This helped very much. Maybe if I was more clear from start it would help. Again you have been to kind sir. Thank you.

 

[jdraw]

Glad you have it solved.
Perhaps you could post your solution so that others may benefit from your work.

 

[undefeatedskillz26]

Glad you have it solved.
Perhaps you could post your solution so that others may benefit from your work.

Great idea jdraw, here you guys go:

SELECT a.Data1, a.Data2, a.AgeData, a.FirstData, a.LastData, a.DOBData, b.SetDate, b.ReasonData, Date()-SetDate-2*Int((Date()-SetDate)/7)-IIf((Weekday(Date())-Weekday(SetDate))<0,2,0)+IIf(Weekday(SetDate)=7,1,0)-IIf(Weekday(Date())=7,1,0)-(Select count(*) from tblDays where DayDate between SetDate and Date() and Weekday(DayDate)<>1 and Weekday(DayDate)<>7) AS [Just Days]
FROM tblInfo AS a INNER JOIN tblStatus AS b ON a.Data1 = b.Data1
WHERE (((b.ReasonData)='P1') AND ((b.TimeDate)=(Select Max(TimeDate) from tblStatus x where x.Data1=a.Data1)) AND ((Date()-[SetDate]-2*Int((Date()-[SetDate])/7)-IIf((Weekday(Date())-Weekday([SetDate]))<0,2,0)+IIf(Weekday([SetDate])=7,1,0)-IIf(Weekday(Date())=7,1,0)-(Select Count(*) from tblDays where DayDate between SetDate and Date() and Weekday(DayDate)<>1 and Weekday(DayDate)<>7))>3))
ORDER BY a.Data1;

 

[jdraw]

Interesting SQL, the table names aren't the names you told us about as we asked for more and more info.

tblPatientInfo
TblStateFileAttribute
????