Report w/ Subreport locks up Access 2007

[guyc]

Hi,

I have a report and subreport built around two tables which share an id. The first table is a list of road lengths, and the second contains three maintenance options for each road length in the first table. Quite literally a 1 to 3 relationship.

I have built a report which shows the road lengths and the info that goes with them, and I want to add a subreport next to this info with the three maintenance options alongside. The report and subreport have been linked correctly, and when I run the report it initially shows the correct info in the way that I would like it.



However, it then locks up and the status bar reports that it is calculating and then formatting, and it stays like this for a long time. I left it running over night and it was still in that holding pattern this morning.

I've obviously missed something (hopefully simple!), but I've been away from Access for a while. Does anyone have any advice or pointers?

The database is attached in a zip to this post. The main master report is Field_Validation_Report. Unimportant tables and links etc have been removed.

Many thanks in advance
g

(Also, please forgive the terrible formatting, this was intended to be a proof of concept! I'll get the report working first, and then make it pretty!)

[ajetrumpet]

ummm...your report and sub report are NOT linked correctly. They can't be. I think it's impossible. your sub is sourced by a UNION query for one thing. and the links in the prop sheets of reports and forms are best used, and possible only used, with linked/related sources. none of your tables are related. there are no relationships set up at all in the sample you gave.

so more than likely, I would guess that what you're trying to ask Access to do is just too complex. As in, something similar to when you get the "query too complex" message when you write an extremely long and involved query statement in the query builder.

[guyc]

^ Hi, I knew that one was going to get caught, but I will say that I did try this with a straight table made from the union and that took ages too.

Are you saying this should just be a simple case of enforcing a one to many relationship between two tables or is there something else as well as that in how I've set up the report?

[guyc]

Ok, tried making a table and then enforcing the relationship between the two. That worked much faster.

Thanks for pointing that out. Like I said, I've been away from Access and querying etc for a while.

Thanks heaps.
g

[ajetrumpet]

i'm going to assume this is solved. Marked as such.