trouble with layout/report

[swf]

I have been so far very successful in creating my database but I am having trouble in displaying a summary of my data in a report and would appreciate help.

I have a database of patients each of whom have many visits by a physician. Patient A is seen on 1/1/05 by Dr Y, 1/2/05 by Dr Z, 1/3/05 by Dr X. The same is true of Patient B although the dates and doctors will vary.

At the end of the month I want to make a spreadsheet report that has the patients in rows and day of the month in columns. If patient A was seen on 1/1 it will display Dr. Y in that cell. under 1/2 it will display Dr. Z in the cell.

Again each patient is linked to many visits which record the date and the doctor for each one. Finally I want to be able to punch in the date range each month.

1st 1/1-1/30 then 2/1 to 2/28 etc. and create monthly summary reports.

Any help is appreciated.

SWF

[RalphL]

I think this can be done. Each cell needs a relationship. The Patient ID, The Day and the Year & Month.

That is 31 relationships. In the visit table start with year and month, these can be defined by the calculation 100 * Year (VisitDate) + Month (VisitDate). Day is Day (VisitDate). And Patient ID should exist. In the patient table you will need global fields Month and Year and the calculation YearMonth as in the visit table. Next you will need 31 number fields Day1 = 1, Day2 = 2 up to Day31 = 31. Next you need to make 31 TO's from Visit. The first 1 will be related Patient ID = Patient ID and Day1 = Day and YearMonth = YearMonth. The rest will follow the same pattern.

Let me know if this isn't clear.

[swf]

I sort of follow. I definitely understand how to calculate the year, month, day from the date and place them with each visit. Is the global field Month and year what I will enter when running the report.(This will limit the visits retrieved to the month I am working on?) I don't understand which table to put the day fields in and how to set up the relationship. Should I be making a new table. I can post my tables and relationships if that would help

Thanks for the help.

[RalphL]

I assumed 2 tables, Patient and Visit.

Patient would have PatientID, FirstName, LastName, Address, etc. The global fields gMonth & gYear. The calculated field YearMonth = 100 * gYear + gMonth. And the 31 fields Day1 to Day31 each egual to number in the field name. When your are ready to make your report you enter Month and Year of the report into gMonth & gYear.

Visit would have PatientID, VisitDate, Visit Time, Doctor, etc. It would have the calculated fields YearMonth = 100 * Year(VisitDate) + Month(VisitDate) and Day = Day(VisitDate).

Relationship for 1st day would be

Patient::PatientID = Visit1::PatientID

and Patient::YearMonth = Visit1::YearMonth

and Patient::Day1 = Visit1: Day

Relationship for 2nd day would be

Patient::PatientID = Visit2::PatientID

and Patient::YearMonth = Visit2::YearMonth

and Patient::Day2 = Visit2: Day

I will look at your tables if you want to post them.

[swf]

Thanks, but I think this type of setup is over my head. Maybe I am approaching this wrong. If I could make a report that listed each patient followed by there visits for a date range (1/01 to 1/30) that would probably accomplish the same goal.

Patient A

Visit 1 Doctor x

Visit 2 Doctor Y

Patient B

visit 1 Doctor Z

visit 2 Doctor x

Thanks

[RalphL]

It sounds more difficult than it is. You can still use the YearMonth calculation to find all visits in a month.

[Vaughan]

This is a straight-forward Summary report. It should be done in the *related* table (visits) not the master table (patients).

Use the layout wizard to make your first summary report, it's pretty good. The only thing you need to do yourself is make an interface to perform a find over the desired date range. Once the records are found, change to the summary layout and sort by the "break field" which in this case would be Patient ID.

I'd also sort by visit date, so the visits are listed in chronological order on the report.

[swf]

Thank you

I think the summary report will accomplish what I need.