Complicated Query

[RozS]

Hi There-

I am trying to query results for a "30 day readmissions". My data resembles the table below. I need any hospitalization within 30 days of the prior hospitalization. I've tried many different scenarios and feel like I'm dancing all around yet can't get it to pull exactly right. I'm working in access 2010. Any help would be greatly appreciated.

MRN	PatNam	ACCT No	Hospital Check-in	Discharge date	30 Day Dte
99199199	Doe, John	739014049	11/11/2011	11/18/2011	12/18/2011
99199199	Doe, John	743931323	04/05/2012	04/25/2012	05/25/2012
99199199	Doe, John	741636782	01/31/2012	02/07/2012	03/08/2012
99199199	Doe, John	739752027	12/05/2011	12/08/2011	01/07/2012
99199199	Doe, John	739580408	11/30/2011	12/04/2011	01/03/2012

[June7]

Criteria based on data in another record of same table is always tricky. You need to calculate the difference between sequential records within a group (patient ID). Might require nested subquery or domain aggregate such as DLookup().

Here is one tutorial about subqueries http://allenbrowne.com/subquery-01.html#AnotherRecord

[NTC]

the visual Query Design View can only get you part way there. Add the table twice, and join on the MRN field, make a second join in 1 table's Check In field to the other table's Discharge Field. Then change to sql view.

manually modify that second join syntax to be: > [Discharge Date] AND <= [Discharge Date] + 30

just air code - will take some trial & error but this is the approach...

[RozS]

Thank you, I'll give it a try!

[RozS]

Hello,

Well, when I saw your response I thought "how hard could that be". Well, I don't ever write in sql. I always use the query builder so I am quite lost. When I do the joins like you suggested and open sql this is what I see SELECT [tbl Table A].MRN, [tbl Table B].[Discharge date B]
FROM [tbl Table A] INNER JOIN [tbl Table B] ON ([tbl Table A].MRN = [tbl Table B].MRN) AND ([tbl Table A].[Hospital Check-in] = [tbl Table B].[Discharge date B]);
So I have no idea where the "second" join begins. This might as well be Chinese. If you could just give me a hint as to where to begin I would GREATLY appreciate it. Thanks very much. I guess I'll be putting a sql class on my agenda!

[June7]

I think NTC is suggesting

Instead of:

AND ([tbl Table A].[Hospital Check-in] = [tbl Table B].[Discharge date B]);

Try:

AND ([tbl Table A].[Hospital Check-in] > [tbl Table B].[Discharge date B] AND <= [tbl Table B].[Discharge date B] + 30);


Why do you have tbl Table A and tbl Table B. Bringing the same table into a query twice is usually like:

TableName and TableName_1

However, I cannot get that join to work.

Here is what I did:

Create Table1 with an autonumber primary key field called ID. Create a calculated field IDminus1 with expression [ID]-1. Also calculate Minus30DayDte as DischargeDate - 30. Changed record 5 DischargeDate to get criteria match.

ID	MRN	HospitalCheckIn	DischargeDate	Minus30DayDte	IDminus1
1	99199199	11/11/2011	11/18/2011	10/19/2011	0
2	99199199	4/5/2012	4/25/2012	3/26/2012	1
3	99199199	1/31/2012	2/7/2012	1/8/2012	2
4	99199199	12/5/2011	12/8/2011	11/8/2011	3
5	99199199	11/30/2011	12/12/2011	11/12/2011	4

Build query:
SELECT Table1.ID, Table1_1.IDminus1, Table1.MRN, Table1.HospitalCheckIn, Table1.DischargeDate, Table1.Minus30DayDte, Table1_1.HospitalCheckIn AS PrevCheckIn, Table1_1.DischargeDate AS PrevDis
FROM Table1 AS Table1_1 RIGHT JOIN Table1 ON (Table1_1.ID = Table1.IDminus1);

Using criteria of previous discharge between discharge and -30 days:
WHERE (((Table1_1.DischargeDate) Between [Table1].[Minus30DayDte] And [table1].[DischargeDate]));

NOTE: advise no spaces or special characters/punctuation (underscore is exception) in naming convention.