Using Access 2010. I've got a request from a user to develop an Access report that shows medical residents assigned to teaching doctors by month. Currently the user uses an Excel spreadsheet, one column per month, 12 doctors across for each month, one or more residents below their assigned doctor.

Spreadsheet looks like so:
JANUARY FEBRUARY MARCH...
Dr. J Dr. M Dr. D
Jones Smith Wilson

User wants to use an Access database to record all this data and not use Excel anymore if possible.

My first thought is just to call Excel thru Access and automate a spreadsheet.

My second thought is to create a report with a series of subreports, one for each month for each doctor, and one for each month for each resident. There would be 24 subreports on the report. The user is thinking about adding more data to the report which would mean 24 more subreports.

I tried the second thought (with 48 subreports total) and it works pretty slick except sometimes I get the dreaded "system resource exceeded" or "not enough memory..." error messages. Sometimes the report will preview OK butwon't print without getting the error messages. So the subreport idea is a bit flaky.

The user ALSO wants a blank box printed if there is no data for the month. So if there's no data, just print an outline where the data should be. I couldn't figure out how to do that so I put a transparent box with a solid border underneath each subreport. That way, if there's no data for the subreport, the box will show on the report. If there is data for the subreport, then the subreport prints and hides the box.

This is a very busy and complicated Access report. Obviously it's too flaky to give to the user. Am I doing it the best way or can you think of a better way? I've not automated Excel thru Access. Would that be the better way?

Thanks for any help or advice.

My first thought is what is your table structure?

I really doubt that you would need 48 subreports if your table design was correct.


John Spencer
Access MVP 2002-2005, 2007-2011

On 7/7/2011 10:16 AM, emanning wrote:

Per John Spencer:
+1
--
PeteCresswell

Thanks for taking time to reply. I think I see where I've made this way too complicated.

Currently there is a table for doctors with their name and month they're teaching. So there are 12 records. There are 12 subreports, one for each month. The subreports are linked to the main report by month.

Same for the residents. One table for residents with their name and month they're being taught. Subreports are linked to the main report by month.

Instead of using subreports, the only alternative I see is to order the doctor table by month, then print all twelve across the page. Same for the residents.

Modification to my post above....The doctor table should be restructured to 12 fields instead of 12 records. One field for each doctor. 12 fields since there are 12 months.

Same for the residents.

Then just place each field in the appropriate place on the report.

Per emanning:
My kneejerk is tblPerson with a PersonType to distinguish doctors
from residents....

Less technically-rigorous but more intuitive:

tblDoctor
DoctorID (AutoNumber)
DoctorName
(whatever other info is needed)

tblResident
ResidentID (AutoNumber
ResidentName
(whatever other info is needed)

tblDoctorResidentTeachingMonth
DoctorResidentTeachingMonthID (AutoNumber)
DoctorID
ResidentID
MonthNumber

--
PeteCresswell

I understand the new table structure you're describing but not how to printthe report. Your structure still returns 12 records. Using your suggestion, I think I need one record with 24 fields...one field for each doctor and one field for each resident. Then on the report I can format the data like so:

doctor1 doctor2 doctor3....
resident1 resident2 resident3...

The way I described would be two tables, one for doctor and one for resident. Both have one record with 12 fields. The report could be formatted as above.

I apologize if I'm not understanding what you're suggesting.

I was thinking about a structure that would look something like:

Doctors table
DrID
DoctorName

Residents Table
ResId
ResidentName

Training
DrId
ResID
TheMonth
(one record for each Dr plus Resident plus TheMonth)

Then if they will accept a slightly revised version of the report you could
easily show something like the following using a crosstab query to generate
the information
______________ Jan Feb Mar ...
Resident Allen DrA DrB blank
Resident Baker DrC blank DrC ...

If not, you should be able to come up with 12 sub reports for the months.

John Spencer
Access MVP 2002-2005, 2007-2011

On 7/8/2011 10:56 AM, emanning wrote:

Thanks, John. But the user wants the data stacked on top of each other.

I think I'm going to leave it to two tables with 12 records each. I can then create a crosstab query to report the data across the page. Both tableshave basically the same structure: Name and Month. I had to add an extracolumn for an xtab row heading so that it wouldn't try to group by Name.

Instead of 24 subreports, I just have 2 subreports now. One for doctors and one for residents. Each subreport is based on the xtab query that shows the doctors in month order and the residents in month order. So far, so good.

If the user decides to add more data, then I'll just do the same thing to the new data as I did to doctors and residents.

Thanks for your help.