One to many to many report

[gmurrie99]

I have a dataset that is a group of projects completed by a construction firm. Each is a separate project. Within each project, a number of items are installed. Within those items, there are technical hours to be billed, components to be fabricated and installed, and logistical elements such as shipping.

MY PROBLEM IS THIS: I can write the report that breaks out each item, but when I try to write a report that takes each project, groups all items within that project, and provides all the detail for each item, the report is enormous (21k+ pages) and does not actually show the proper relationship within the project.

What I thought would happen is that for each item within a project, the details (~6 pages) would be generated, and that report would repeat until all items within that project had been reported. From there it should continue on the the next project and so on. Doesn't seem to work that way. I tried building a report that lists each project and item and then inserted subreports in the detail section which is the listing of items.

As you may be able to tell, I am now 40 hours straight on this problem and I don't seem to be getting anywhere.

Please help.

 

[dhookom]

Can you provide your table structures, relationships, and how you want your report to display?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gmurrie99]

Yes I can. Thanks in advance...


WorkPackMain - Main Table
ID - Autonumber field generated by ACCESS
wpno - Work Pack # (Project)
itemno - item # (multiple items per project)

RatesandHours - Table of technical workers and their payrate
ID
wpno
itemno
discipline - type of worker
hours - # of hours worked
rate - hourly rate per discipline
cost - hours * rate

LOGISTICS1231 - Table of equipment used for items
ID
wpno
itemno
transporttohou - cost to transport item to Houston port
cranehours - hours a crane was used
crane - hourly rate for use of crane
LOTS AND LOTS OF OTHER EQUIPMENT/HOURS PAIRS

GENMAT - Table of General Materials used on an item
ID
wpno
itemno
material - description of the material used (Piping, elbows, etc.)
units - How each unit of material is defined (Ea., LOT,etc.)
qty - how many of each unit
rate - per unit cost
cost - total cost

FABRICATION - Table of Fabrication necessary to build item
ID
wpno
itemno
description - item fabricated
qty - How many were fabricated
fabrate - hourly rate for fabrication
fabhours - hours of fabrication
fabcost - rate * hours

Relationships - All tables are connected by itemno

I want the report to be grouped by wpno, then for each itemno attached to that wpno, I want to print details from the other tables (Ratesandhours, LOGISTICS1231, GENMAT, Fabrication)(I actually have 6 reports already written grouped by itemno from these tables.)

If there is a better way to send you this information, let me know. I can send you the database if you like...

 

[dhookom]

I would expect you could create a subreport for each related table and place them on the main report which has a record source of your main table. Use the link master/child properties to filter the subreports to the related records.

I kinda question why you state the tables are related on "itemno" when each table also contains the "wpno". If itemno values are unique, you shouldn't need the wpno in each related table.

I think I would have used the ID from the main table as the link in the related tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]