Stays in psychiatric hospitals

[Len Bowers]

I'm not an access programmer, but a researcher. However I've worked with Access for some time (self taught with a book and the help files). I have the limits of my capability with my current project, so I am posting here asking for some help.

I am conducting a study of admissions to psychiatric wards in three hospitals. I have two tables and two problems.

Table one: each row represents a discharge, with an admission and discharge date, ward name, age gender, diagnosis, and a unique patient identifier (repeated because patients are often admitted more than once).

Table two: each row represents a ward stay, with a start and end date, ward, and patient identifier. Because patients are frequently transferred between wards, they might have several different ward stays during an admission.

What I need to get: Numbers of occupied bed days by week and ward. And I need this in several ways, e.g. number of male occupied bed days by ward and week. The period of time covered by my data is just under three years.

My problems are twofold.

1. I need to match the discharges data to the ward stays. However whenever I join the tables using the patient identifier, it multiplies the rows in the result, rather than giving me one row per ward stay.

2. Once I have got that far, I need to produce tables by week. I could handle this if I could find a way to multiply the rows of the ward stays table so that there was one row for every day within the ward stay – but I can’t figure out how to do that, and suspect there may be a much more elegant solution.

I'm hoping this all seems simple to you. It doesn't to me!

Len

 

[Len Bowers]

Len, you must have been having a bad day yesterday. have you forgotten how to use WHERE statements. Your second question difficulty was a little more complex, but solved in the same way and utilising a table of dates.

Dur....

 

[IfButOnly]

Len, hate to be the one to point it out, but you, as a psychiatric researcher, should be a little worried when you start talking to yourself.... and more so when you answer.

 

[Len Bowers]

Well, no one else was talking to me.....

Anyway, I thought it was a way to let everyone know I had solved the problem myself and save them from possibly wasting their time answering me.

Nothing wrong with talking to yourself, by the way, that's not pathological. It's talking to people that aren't there that should have you worried, especially if you hear them answer back.