Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS-Access - Determining if the next record meets a criteria

Posted on 2015-02-19
5
Medium Priority
?
194 Views
Last Modified: 2015-02-19
I have attached a list of sample data I have to evaluate for repeat admissions within 30 days of the last admission date.  The data is organized by Unit Number and Admission Dates in a descending order.  If the subsequent admission date for the same Unit Number is within 30 days of the current  record's admission date, then it should be marked as "Y" for readmission.

Can this be reflected in a query?

Thanks

Glen
lACEdATA.xlsx
0
Comment
Question by:GPSPOW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 40620227
You use UnitNumber, not AccountNumber to identify readmission?

You can do something like:

SELECT T.UnitNumber, T.AccountNumber, T.AdmitDateTime
, Max(T2.AdmitDateTime) as PrevAdmitDate
FROM yourTable as T
LEFT JOIN yourTable as T2
ON T.UnitNumber = T2.UnitNumber
AND T.AdmitDateTime > T2.AdmitDateTime
GROUP by T.UnitNumber, T.AccountNumber, T.AdmitDateTime

This would give you each admittance and the previous admittance for that particular UnitNumber.  You could then wrap that up as a subquery to get the indication of whether it is a readmittance.

Select subQ.UnitNumber, SubQ.AccountNumber
subQ.AdmitDateTime, subQ.PrevAdmitDate
, iif(IsNull([PrevAdmitDate]), "No", iif(DateDiff("d", subQ.PrevAdmitDate, subQ.AdmitDateTime) <= 30, "Yes", "No")) as Readmit
FROM (
SELECT T.UnitNumber, T.AccountNumber, T.AdmitDateTime
, Max(T2.AdmitDateTime) as PrevAdmitDate
FROM yourTable as T
LEFT JOIN yourTable as T2
ON T.UnitNumber = T2.UnitNumber
AND T.AdmitDateTime > T2.AdmitDateTime
GROUP by T.UnitNumber, T.AccountNumber, T.AdmitDateTime
) as SubQ
0
 

Author Closing Comment

by:GPSPOW
ID: 40620271
I had to reverse the two dates in the datediff function and it worked perfectly.

The UnitNumber is the patient Medical Record number which stays with the patient no matter how many times he or she is admitted.  The account number changes with every admit.  So I am looking when the UnitNumber matches for subsequent admissions.

Thanks

Glen
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40620288
glad to be of assistance.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40620290
actually, you might want to use:

IIF(DateDiff("d", NZ([PrevAdmitDate], [AdmitDate] - 31), [AdmitDateTime]) <=30, "Yes", "No") as Readmit

This should work better as it handles the NULL [PrevAdmitDate] inside an NZ function instead of another IIF( ) statement.
0
 

Author Comment

by:GPSPOW
ID: 40620295
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question