Determine PREVIOUS offense (A2K)

Topic

New Reply

Hello All,

I will do my best to NOT be lengthy in my description, as most of time I am.

Here’s my situation:

I have a main recordset–tIPDEMO

This table holds ALL admissions and discharges data for all members.

I have a query, using tIPDEMO, that HANS helped me with some many months ago post 597,838 , to determine the members who discharged and then readmitted w/in a 30 day time frame. All works very well.

Now the powers that be want to include the PREVIOUS discharge data that created the READMIT as part of the report.

The problem is, is that the 30DayReadmit query ONLY extracts the data that CREATED the offense. How can I now include the DISCHARGED offense in the query?

Hopefully that makes sense and someone can provide some insight.

Reply | Quote

Replies

The query from the post you refer to contains two copies of the table – you should be able to retrieve information from both copies.

If that doesn’t help, please provide sufficient information for us to help you.

Reply | Quote

I’m so struggling here…and I just absolutly don’t know what to do next.

I made an error in my original post, I was sure it was Hans’ solution I was using and it isn’t. I therefore, sent everyone to a post that doesn’t apply here. To everyone, I apologize.

To set things straight. I’m using a subquery to retrieve the 30 day readmits:

DaysSinceLastVisit: [sfd]-nz((SELECT TOP 1 ID.STD FROM tIP_DEMOS_DX AS ID WHERE ID.STD< tIP_DEMOS_DX.SFD AND ID.SSN=tIP_DEMOS_DX.SSN ORDER BY ID.STD DESC;),[sfd]+1)

What this does is counts the days between discharge and admit. Then I have “between 0 and 30 in the criteria to only pull those records.

However, because of this, I don’t have anything with the PRIOR DISCHARGE information … which of course is now what I need.

Hans, I’ve looked at the original solution you provided for days, trying to come up with a way of retrieving JUST the DISCHARGE that creats the NEXT record — the 30 day readmit OFFENSE and I can’t find a way of getting it.

I’ve attached examples of what I need but can’t get. Hopefully that will make more sense.

Reply | Quote

Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

Hope you or someone can provide some insight…because I’m totally lost on this one….I mean more lost than I normally am.

Reply | Quote

See if the query qryPrevSFD in the attached version does what you want. It is based on qryPrevSTD.

Note: I removed the code table since none of the discharge codes in tttt occurred in the code table.

Reply | Quote

Yes Hans, I believe it will do what I’m looking for. Now the only thing I need to resolve is the criteria issue. I use a DateSerial function for SFD which, each month pulls data from the previous month. It works fine. I’m only concerned that the criteria will omit the previous records. But I can find a resolution to that.

Thanks for your assistance…it’s greatly appreciated…..sincerely it is.

Reply | Quote

OK Hans….I’m so very close…I can barely breath….I think the syntax you used in the subquery is giving me a new problem.

PrevSTD: CDate(DMax("STD","tttt","STD<#" & Format([SFD],"mm/dd/yyyy") & "# AND SSN=" & Chr(34) & [SSN] & Chr(34)))

In the case(s) WHERE there ISN’T a PREVIOUS discharge (STD) “invalid use of NULL” is returned. If I remove the criteria >=[SFD]-30
then, #ERROR# is returned in the records where there isn’t a previous std and a valid Date where there is a valid std.

Any suggestions?

Reply | Quote

Please attach a sample database where the problem occurs.

Reply | Quote

Here it is…

Reply | Quote

You can use the Nz function (more or less like your original expression) to avoid problems with Null values.. See attached version, I changed the definition of PrevSTD.

Reply | Quote

I think I’m gonna cry,,,,,ok…I am crying…..thank you so much….

Reply | Quote