Run a subreport for all records in a main report???

[westchamp24]

I am trying to run a subreport for every record in a main report. How can I pass a parameter from the main report query to the subreport query. The way I have it set up now, the subreport prints the desired fields for ALL records in the main report for each record in the main report.

I don't know if that made any sense, so I'll try to give an example of what I am looking for. My main report prints a record for every state, and related info such as capital, population, size...etc. For each record in the main report(state), I want to run a subreport that prints cities of that state. Right now, my subreport prints all U.S. cities for each state, not the state specific cities.

 

[Kempes]

If I understand this correctly, a subreport would not be needed.

I would recommend you create a new query showing this information (linking the parent to the child), then base the report upon this query.

Let me know if you need any more guidance on this.

regards
Kempes

 

[Rich]

You can link a subReport in the same way you link a subForm

 

[westchamp24]

Thanks for the suggestions so far, but I still need a little help. I can create a single query that will pull in all fields of interest, but it returns duplicate parent records for each child record. I don't know if that is the correct terminology, so I'll go back to my states and cities example.

I can create a query that returns the state and other top level info and also returns the cities for ach state. However, the state and top level info is repeated for every city. In my report, I want to show the state with its cities listed underneath.

I think GROUP will take care of this issue. Obviously, I am not to familiar with SQL, so it may take a while.

Again, thanks for the help.

 

[boblarson]

I think GROUP will take care of this issue. Obviously, I am not to familiar with SQL, so it may take a while.

Yes, you can do this in the REPORT'S GROUPING and SORTING (don't even worry about the underlying SQL as reports don't like to use that sorting anyway). Create a group for the State and then in the details have the cities.

 

[westchamp24]

Thanks Bob,

That did the trick. Im embarrased about how long this has hung me up, but I'm still learning.

 

[boblarson]

Glad to hear you got it sorted. And don't worry about the time it took to get there as there is a LOT to learn about Access and it doesn't all just come right away. Believe me, I've been doing this for 10+ years and I still learn new things I didn't know how to do before. This forum is great for that.