Report Help

[Tom Patten]

Help - I'm "over my head"!!

Cann't tell you how many hours I've spent on this.

I need to create a report to find the Days-of-care by month.

For each Client I have a SOC (Start of Care date field) and DC (Discharge date field).

From a form I ask for the User to enter the Beginning Date vBegin=UI...
And the Ending Date, vEnd = ui...

Then I create a TempTable to capture all the dates from vBegin to vEnd +1.

Then I developed a report and created a calculated field that compares TempDate to SOC and DC dates (between(T_date,Soc,DC),1,0).

Anyway - I have all the above working.

My problem is developing a link between the TempDate table and Client Table. I tried a "Linkfield=1" in both tables, Client and Temptable, but then I get all multiples of records. All Client relate to ALL Dates because they all have linkfield=1.

On my report, I thought I could control this, but I have not been successful. My detail section keeps showing individual clients multiple times.

Any and all suggestions would be very much appreciated.

Thanks

Tom

[Jay Talbott]

RE: Report Help

Tom,

I would try and write the info to a flat file and, depending on the date range for the report, you may need 13 or 25 files per record. I would do something like this:

field 1, client name
field 2, N,2.0 (with name of first month and year in report period as field name)
field 3, N,2.0 (with name of second month and year in report period as field name)
.
.
.
field 13, N,2.0(with 12th name of month and year in report period as field name)
.
.
field 25, N,2.0 (with 25th name of month and year in report period if you need two years data)

Then create a script that goes through each client, Write the name of the client out to the extract record, and the nubmer of days in the appropriate field.

The report would then look something like this
Name Jan-2002 Feb-2002 Mar-2002 ..etc.
Smith 2 28 3
Jones 25 10

(I suspect the above may not line up well, but I hope you get the idea)

Hope this helps. If not, maybe it will trigger other ideas.

Let us know what you decide.

Regards,

Jay Talbott

[Tom Patten]

RE: Report Help

Jay,

I get the idea. Seems complicated given my level of expertise.

With over 120 clients and maybe going back one year, it could take considerable time.

Most times a person would only want a report for 3 to 6 months back.

Can you suggest a way for me to link to just a dates table and eliminate the duplicates within the report?

Thanks

Tom

[Tom Patten]

RE: Report Help

I've attached a file to demonstration the above. And I added another grouping just to complicate matters.

Again, your help is very much appreciated.

Tom

[Tom Patten]

RE: Report Help

I got it to work. Just needed a nights sleep.

I don't have the details done, but I'm getting the right results using trial data.

Thanks anyway.

Tom