Calculating a value for a report - complex

[rosenn]

Basic dbase structure - each record has two subforms that could contain either a variable number of hospital admissions (each with an admission date) or a variable number of medical procedures (each with an operation date). In printing an active list of patient records the user will choose (with a field in the parent record) if they want the list to show number of days in the hospital (Hosp.day #10 for example) or number of days since the last procedure (postop day #10 for example). The report, based on a query with only the active patients on it, will then list the users choice of the two dates.

It gets tricky because there could be multiple admissions and operations, but the subforms are sorted so the most recent one is showing. Becuase the query that makes the report is based on admissions (all patients have an admission, but not all were operated on), if I tack on the operations table to my query I will lose those patients without operations - not acceptable. How can I make this work. SQL is not my forté.

Any help is appreciated,

Nelson

 

[neileg]

Do you mean that the report will either show days since admission, or post op days, but not a mix of the two? If so, surely you want two reports based on different queries.

One query will bring in the admissions date, subtract today's date and then show the minimum of this value for each patient.

The second query will bring in the operations date, subtract today's date and then show the minimum of this value.

If that's not what you want, please explain.

 

[rosenn]

One or the other

Yes, the report is to show hospital day or postop day, but not a mix of the two. I have written a function to do this - it takes in operation date, admit date, and the user's choice for what they want on the list, and it returns a text value with either HD__ or POD___. This is what is printed in the report. Problem is when I went from having all admission and operation data on each record (meaning it must be changed everytime a new one is added, and old data is lost) to the parent and daughter record format allowing infinite operations or admissions, it all became more complicated. In the form that has the two subforms, the subforms are sorted in reverse date order so the Me!AdmitDate and Me!OperationDate are the correct ones to go into the function.

Problem is that the final active patient list is based on a query that has all active admissions - if I tack on the operations query I loose all patients who have not had operations, and that won't work.

 

[WayneRyan]

rosenn,

You can create "unbound" textboxes in your detail section.

Make your pertinent query fields invisible in your detail section.

In the detail-OnFormat event move the contents of the desired
invisible fields into your visible unbound fields based on which
type of report.

hth,
Wayne

 

[rosenn]

its all going into the same report

The problem is the final report is a listing of all active patients and for each patient, based on what you have chosen in the parent record, the pertinent date is listed - either Hospital Day# for those not operated or Postop day# for those who have been operated on. So they all end up in the same report. But if I base the report on a query, I don't know how to construct a query that has all patients with active admissions, and then if they have had an operation, that is added on as well. The only place it works is in the open form with the 2 subforms because all the pertinent data is there (and the form won't exclude patients just because they have no records in the operations subform). Can I calculate the value (Hosp day or Postop day) in the open subform and then use the value to populate a field in the parent record that would then be available in the final querry? No worries on calculation errors because the function that I wrote to do the calculation already deals with that and returns a string in any event.

 

[neileg]

You are asking for help in solving your solution. This is always more difficult than solving the original problem.

I think you are coming unstuck because you feel you have to gather everything into one query. While this may be required to do a straight report, I can't help but feel that you will need to use subreports that will be based on different queries.

I have not spent hours considering your task, but I suspect you need three queries, one main report (for patient details) one subreport for admissions and one subreport for operations. This will allow for the situations of admissions with no ops.

Hope this gives a clue.

 

[rosenn]

I cheated and got it working

What I have done is in violation of what the senior members keep writing - not inserting calculated values into a table, when the data could remain in queries and be recalculated as needed. I calculated the value in the open main form that has the two subforms open and then I insert that into a field (invisible on the form) in the main data table. It works.

At the same time I am going to try to do it with multiple queries and another solution with sub-reports. I guess when I find the cleanest code I will stick with that.

For those of you against inserting calculated values into a table, what is your logic (other than protecting against calculation errors that arise when components of the calculation change, or a reflexive logic design that is unnappealing to elegance programmers)?

-Nelson

 

[neileg]

For those of you against inserting calculated values into a table, what is your logic (other than protecting against calculation errors that arise when components of the calculation change, or a reflexive logic design that is unnappealing to elegance programmers)?

Well, quite. The former, ie protecting against calculation errors that arise when components of the calculation change is sufficient justification, surely.

There are other reasons, like reducing the size of the stored data.

On ocassion, I will store a calculated value. I just don't tell anybody!

 

[rosenn]

Thanks

Thanks Neil, Wayne for your help,

and to all on the forum whose replies I have benefited from immensely I say thanks.

-Nelson