Help with criteria for this query(yes another one) (1 Viewer)

[Hayley Baxter]

Ok this time I want to know how I can list the patient(and only this patient) who has the longest hospital stay.

Any help with the criteria or should it be an expression?

Many thanks in advance

 

[ColinEssex]

Hayley

Are these current inpatients or discharged patients?

Col

 

[Hayley Baxter]

Hi Col

These patients will have been discharged.

 

[ColinEssex]

Hi Hayley

I don't know if I'm causing you too many problems today !!! Perhaps it's me having an off day.

Anyway, assuming you've got a table with the patients and length of stay in it. Just do a query that finds 'max' on the length of stay field.

I hope this helps so that I make up for the 'age' problem I gave you

Col
ps - it's nice that Sky are now involved with the NHS, maybe it'll work ok now with Mr Murdoch in charge !!!

 

[Hayley Baxter]

Well you just never know do you??

With all this talk of hospitals I'll be very experienced in this area soon and where's your good friend Ally gone?? You're having to do all the explaining on my probs alone today!

No you've been a great help today. I don't actually have a field called length of stay. I used an expression similar to the age one in another query to get the results in days of how long patients have been in, this time I just want to list the patient with the longest stay. I have never used the max function but I'll give it a bash!

 

[ColinEssex]

Hi Hayley

Ally is off at a meeting sorting out one of her databases which is why you're having to put up with me!!!

The max query is like doing a count but select max instead.
Just to confuse you even more, the length of stay is based on the midnight count so if a patient is admitted on the 1st and discharged on the 3rd, the length of stay is 2.

Col

 

[Hayley Baxter]

Thanks again Col, I shall let you know If I run into anymore problems(knowing me that's quite possible) Not to worry won't be annoying you for 2 whole weeks as Im on hols!

 

[ColinEssex]

Hayley

Have a nice holiday.

I'm off home now, I'll pick up any more of your posts tomorrow morning

Col

 

[Hayley Baxter]

I can get this to work using max ie it displays the longest duration in days, only problem I have is that I need to display the patients details in the query which Access doesn't like since I am using max.

Any suggestions??

 

[ColinEssex]

Hi Hayley

I've worked out a 'round the houses' solution to your problem, it's vaguely similar to one I had a while ago.
Basically you need to write some code that loops through the table and then appends the highest LOS and ID to a 'holding' table. You can then use that holding table as your link in your query to get the rest of the fields up.
Email me if you want more detail

Col

 

[Rich]

Use two queries or one and DLookup

[This message has been edited by Rich (edited 04-19-2002).]