Complicated Query

[andysgirl8800]

I want to build a form based on a query, but I don't seem to be structuring it properly. Please refer to attached picture to help explain what I'm trying to do. I have 4 essential fields in a table, tblInpatientLog. They are POSno, Primary, POS Code, and Length of Stay (calculated as difference between Admit Date and Discharge Date).

What I need to do is have the form display the POSno that are in the table as "21" and "61" ONLY. There are others, but I only need to see these two. I then need to split the form (visually) into two sections. On the right, I need to display the Primary field as ones that are in the table as "Healthy Plan" only and on the left, I need to display Primary as ones that are in the table as "Medicare" only.

Under these headings, I need to show the total number of days, under the Length of Stay field for each of the four hospitals, Hospital 1, Hospital 2, Hospital 3 and Hospital 4, which are identified in the table under the "POS Code" field as "16", "2", "5", and any number OTHER THAN 16, 2, or 5 (or, whatever is left), respectively.

The Length of Stay field is calculated as difference between Admit Date and Discharge Date fields in the form for each individual record. I need access to show me a SUM of all the number of days for all the records that are listed as "Healthy Plan" for the "Primary" field for Hospital 1, then for Hospital 2, then for Hospital 3 and then for Hospital 4.

Then I need Access to calculate the SUM of all the 4 calculate field results for the 4 hospitals and display in the "Total Hospital Days" field on the form. Finally, I need a count of the total number of records that meet the criteria of "POSno" = 21 and 61, "Primary" = Healthy Plan, "POS Code" = all.

I hope that makes some sort of sense for someone. I've tried to create a query, but it doesn't display any results. I've wondered if I need to create more than one query, and have tried various different options without success. Any alternate suggestions would be welcome at this point. Thanks in advance for anyone out there that might be able to help.

 

[KeithG]

Sounds like you need three forms to me. A main form and two subforms, one sub form showing the medicare and the other showing Healthy Care.

 

[Rickster57]

Forms

Create a form with no record source. Create one form using a query based on the criteria for Healthy plan. Create another form based on a query for Medicare. In both those queries use the criteria under each field for your filters.

Using the Subform control in your toolbox drop your Healthy Form on the left and your Medicare form on the right of the form with no record source.

See if this gets you started.

 

[andysgirl8800]

Interesting! I'll give both of these suggestions a try and see what happens. Will be a first in using subforms. Also, when I was working with the query, I couldn't get it to filter "21" AND "61" for the POSno, no matter what syntax I used. I could get it to filter one, or the other, but not both. Was I doing something wrong?

 

[Rickster57]

posno

Are you sure you don't mean 16 or 21?
If you say AND this means the POSNo has to be both which doesn't make sense to me.
In the criteria put 16 OR 21

 

[andysgirl8800]

Thank you for the clarification on that criteria for the query, looks like it's displaying the correct data. My next issue comes with calculating the total length of stay at each hospital. Currently, this is stored as a calculation field for EACH RECORD as the difference between two dates, admit date and discharge date. How can I get a sum of ALL of the days spent at one hospital?

 

[andysgirl8800]

I've pasted the SQL language for the first of my queries. Seems to be working alright, but I still need to figure out how to calculate a calculated field. Any suggestions?

SELECT tblInpatientLog.[Patient's Discharge Date], tblInpatientLog.POSno, tblInpatientLog.[POS Code], tblInpatientLog.Primary, tblInpatientLog.[Date Admitted as Inpatient or SNF], tblInpatientLog.[Patient's Discharge Date], DateDiff("d",[Date Admitted as Inpatient or SNF],[Patient's Discharge Date]) AS Expr1
FROM tblInpatientLog
WHERE (((tblInpatientLog.[Patient's Discharge Date]) Between [1st Date] And [Last Date]) AND ((tblInpatientLog.POSno)="21" Or (tblInpatientLog.POSno)="61") AND ((tblInpatientLog.[POS Code])="16") AND ((tblInpatientLog.Primary)="Healthy Plan"));

 

[KeithG]

what do you need to calculate?

 

[andysgirl8800]

I have two fields in my query, Admit Date and Discharge Date. I've build in a calculated field into the same query to calculate the total days between these two dates. What I need to do to finalize this query is calculate or count the TOTAL of the days that are the results of the date diff calculation. In other words, I have the query set to segregate out records pertaining to one specific health plan. The end goal of this query is to show me the total number of patients serviced by this health plan in a given time period and a count of the total number of days spent by ALL patients in the same time period. Make sense?

 

[KeithG]

Okay so you want to calculate the total of the days? I would do this with a control on the form not in the query.

 

[andysgirl8800]

Thank you for your quick reply. The form itself will also be complicated. It will be complied of a series of subforms all built on different variations of this query I'm building (but will be seperate queries, just with different criteria). How can I calculate this count of all the days?

Ultimately, what I want to do is display a count of the grand total days. So, if I have the following three records:

ID AdmitDate DischargeDate Exp100
1..... 01/01/06.........01/15/06............15
2......07/12/06.........07/19/06............7
3......04/27/06.........04/30/06............3

My query in the form should display:
Healthy Plan Total Patients=3
Healthy Plan Total # Days = 25

 

[KeithG]

Okay. create another query from the query you have been working on. Make it a totals query and Sum the number of days addmitted and count the ID's

 

[andysgirl8800]

Please excuse the ignorance of this question, but I'm not sure what you mean. Build another query from a query?

 

[KeithG]

Instead of basing your new query off of a table base it off of the query you created.

 

[andysgirl8800]

Ok, thanks for the suggestions. I did get this working based on your feedback. Only problem I'm running into now is that each subform is built on it's own query, which is also built on another query. Each query specifies a date range to pull data. Now, here's my new question. Each time the queries are run, they will ALL have the same date range. Right now, I have to enter the date range 8 times to display all the totals for the 8 hospitals. Is there ANY way at all to only enter the date range ONCE for ALL queries?

 

[andysgirl8800]

Been tinkering with it for a while now and still have to enter the date range 8 times. Can I build this in somewhere else? On the parent form, perhaps. Did a searc of the forums and can't seem to find something similar. Thanks.

 

[KeithG]

Create an unbound form to enter the date range on and then change your queries parameters to the control names.

 

[andysgirl8800]

Thank you for the suggestion KiethG. I already have an unbound form, and then have 8 subforms. I'm not sure what you mean by "change your queries parameters to the control names". Which control names are you referring to? And where in the form design should I put the date range? In a header or footer?

 

[siperiea]

I think KeithG is referring to the name of the unbound control as a replacement for the dates in the query.

Place an unbound control (say a text box) in the header, or footer, or anywhere you like.

If your form where you placed the unbound control is called Form1 and the unbound control (say a textbox) is called DateEntry, change all references in your query from the date, to:

Forms![Form1].[DateEntry]

When you open the form, enter the date, then run the query.

HTH