Problem with a Report and accompanying Query in MS Access 2007

[Rene]

Good afternoon,

I am having trouble developing a report. The format is as follows:


Activity ReportableInjuries LostTimeInjuries
Etc.
Name Mth Ytd Mth PY Ytd PY Mth Ytd Mth PY Ytd PY
Etc.

Basically, there are about 127 locations I list (using Labels) under the
"Activity Name" column in the report. The queries pull the accompanying data
for each location.

The issue is:

So far the only way I've been able to piece the massive report together and
get it to work is by having one query for each column ("Mth RI", "Ytd RI",
"Mth PY RI", Ytd PY RI", "Mth LT", "Ytd LT", etc...) and accompanying
subreports that group each location, due to limits on the amount of objects a
single report can have. It all works, but when I incorporate the subreports
into one master report to glue all of it together, I receive a message
stating that I have reached the limit of the amount of tables that can be
open at the same time and am not able to run the report.

Please help or provide an alternative way of getting this done.

Thanks in advance for all your help,

Rene

 

[KARL DEWEY]

You need to post your table structure. All you mentioned was locations in
Activity.
How is the data for Mth RI", "Ytd RI", "Mth PY RI", Ytd PY RI", "Mth LT",
"Ytd LT", etc... stored?

If your data is stored as a relational database is supposed to look then
probably one query ahead of a crosstab query should do it.
Otherwise you might need a union query to reassemble the data first.

 

[M Skabialka]

I also think a crosstab query would work. I started with this site to learn
how to do this.
http://www.blueclaw-db.com/report_dynamic_crosstab_field.htm
I ended up doing quite a lot of other research but finally got a really
great looking report.

 

[Rene]

Across the top: Reportable Injuries (Space) Lost Time Injuries (Space)
Lost Days (Space) etc.

Each category above has four columns under it as follows:

Mth (space) Ytd (Space) Mth PY (Space) Ytd PY (Space)

Mth = Month, Ytd = Year-to-Date, Mth PY = Month for Previous Year, Ytd PY =
Year-to-Date for Previous Year

Then, along the left handside all the locations are listed. The information
above would be listed as a line item for each one.

Your help is greatly appreciated!

Rene Lazaro

 

[Rene]

Good afternoon,

The data is stored as a relational database.

There is a table for facilities, another for injuries, another for property
damage. The injury and property damage tables are related to the facility
table.

Thanks!

Rene

 

[Rene]

Good afternoon,

Is this possible given my replies to Steve and Karl?

Thanks a bunch!

Rene Lazaro

 

[KARL DEWEY]

You posted table names but not field names with data types.
Post sample data - you can make it phony data.

I assume the following -
Reportable Injuries - count of injuries
Lost Time Injuries - count of hours (Only 8 hours per work day)
Lost Days - count of days

 

[Rene]

Facilities Table

(Field 1) Facility ID (Field 2) Facility Name (Field 3) Address (Field
4) Phone (Field 5) Etc...

Injuries Table

(Field 1) Social Security No. (Field 2) Facility ID (Field 3) Injury
Date (Field 4) Injury Classification, i.e., lost time, light duty, first
aid, etc. (Field 5) Etc...

Property Damage Table

(Field 1) Social Security No. (Field 2) Facility ID (Field 3) Property
Damage Date (Field 4) Property Damage Classification, i.e., at-fault,
not-at-fault, etc. (Field 5) Etc...


Reportable Injuries = Sum of Count of Injury Classifications (i.e., lost
time injuries + light duty injuries + first aid injuries)

Lost Time Injuries = Count of lost time injuries

Lost Days = Count of days


Is there some way for me to attach a jpg of the report?

Thanks again!

 

[Rene]

The queries do not include other queries.

Right now, I have four queries. One for the month (mth) data, one for the
year-to-date (ytd) data, one for the month of previous (mth py) data, and one
for the year-to-date for previous year (ytd py) data.

Each query has the same fields as follows:

Field 1- Facility Name
Field 2 - Year: Year([Injury Date])
Field 3 - Facility ID
Field 4 - Reportable Injuries: ([Lost Time Injuries])+([Light Duty
Injuries])+([Other Reportable Injuries])
Field 5 - Lost Time Injuries: Sum([tbl_Injuries]![Injury
Classification]="Lost Time")*-1
Field 6 - Light Duty Injuries: Sum([tbl_Injuries]![Injury
Classification]="Light Duty")*-1
Field 7 - Other Reportable Injuries: Sum([tbl_Injuries]![Injury
Classification]="Other Reportable")*-1
Field 8 - Osha Lost Days: Sum([tbl_OSHA 300]![Total Lost Days])
Field 9 - Osha Restricted Days: Sum([tbl_OSHA 300]![Total Restricted Days])
Field 10 - Injury Date (Where Between [Start Date] And [End Date]

They are Totals Queries.

Fields 1 to 3 are Grouped.
Fields 4 to 9 are Expressions.
Field 10 is a Where.

You mention that I don't need subreports. Can I just include all of them in
one report without the subreports? If so, how can I do it?

(Space) refers to each category being standalone. I meant:

The following categories are listed across the top from left to right
Reportable Injuries
Lost Time Injuries
Lost Days

Thanks Steve!

 

[KARL DEWEY]

Lost Days = Count of days
How do you get a count of days with a single date - (Field 3) Injury Date ?

Your response to Steve indicates mores tables - Field 8 - Osha Lost Days:
Sum([tbl_OSHA 300]![Total Lost Days]) and Field 9 - Osha Restricted
Days: Sum([tbl_OSHA 300]![Total Restricted Days])

You need to post the SQL of your query so it can be analyzed. Open query
in design view, click on VIEW - SQL View, highlight all, copy, and paste in a
post.

 

[Rene]

Good morning Steve,

I followed the steps you outlined in creating the combined query and it
worked!

Thanks a million!!!!!!

I will proceed with developing the report, If I have any additional
questions I'll continue this thread.

Thanks very much!!!!!!

Rene Lazaro