Is it possible to create this report?

[Rene Lazaro]

Good afternoon,

I am trying to develop a report but get a message stating that I have reached the limit to the amount of tables that can be open at the same time when I run a test report. As a result, I cannot conclude its development. I've attached two pages of the report for your review.

The data is stored in a relational fashion. I have one table for facilities, another for injuries, and another for property damage.

So far, the only way I have been able to make the report work is by having one query for each column, labeled on the report as:

Mth (column 1) Ytd (column 2) Mth PY (column 3) Ytd PY (column 4)

Mth = Month; Ytd = Year-to-Date; Mth PY = Month of Previous Year; Ytd PY = Year-to-Date of Previous Year.

The four columns above should summarize data for each category - ie. Reportable Injuries, Lost Time Injuries, Lost Days, etc. as a line item to each facility (location) listed on the left-hand side of the report.

Any help anyone can provide is greatly appreciated.

Thanks in advance,

Rene Lazaro

 

[gemma-the-husky]

you ought to be aiming for a single query including all the information

its hard to see how you can have too many tables, as you say you only have three. The trick is to get the right data into your single query, which probably needs subqueries.

---------
the other way to do it might be to have a temporary table to store the data, and populate each row of the table with the relevant data.

 

[DCrake]

Lets say you have got over the issue you currently have and Access creates the report. How are you going to print it. Ok you can use landscape mode but I'm sure it will not fit on one sheet. My suggestion would be to send it directly to Excel into a predefined template layout. Its a bit like having 50 characters for a persons first line of the address and using window envelopes. However if the user uses all the 50 characters the trailing part of the addess is hidden because the window will only accommodate 40 characters.

Just my point of view.

David