Subreport under a different grouping; where do I put them?

[crobaseball]

I've got a report which has the following grouping structure





But I also need to embed 2 subreports. One directly in front of the groupings which will be under the FlatTrimOther, and the other after it. It needs to look something like this:



So as you can see the FlatTrimOther grouping will have multiple tables. This shows 2, but it can have up to 4. I need to have my color subreport show up above it, with my shipping subreport shown below.

Basically, I'm unsure of where to place the subreports in design view. Thoughts here?

Thanks!!

[June7]

Try:

Set up a group for Colors and group for Packaging. Use Expression option to create group: ="Colors"

Put subreport in group header section

[crobaseball]

Out of curiosity . . . do you lean to the camp which focuses on using queries with reports?  I'm trying to do so in order to keep things clean, but man is it tough to write the query I want to the exclusion of using a table for another field.  THoughts here?

[June7]

Not sure what you mean by 'focuses on using queries'. The RecordSource for a report (as well as form) can be table, query, or SQL statement. I have used all.

[crobaseball]

That wasn't clear: I'm putting together a report which has a lot of different elements from several different tables. I'm struggling to figure out how best to approach this. I have some info which wouldn't group well on a query, but I'm realizing the report itself could then do the grouping for me, eliminating some of the redundancy the query would create.

This isn't easy to explain in a short post. I need to think more on it and come back here tomorrow.

[June7]

Report/Subreport, domain aggregate functions, aggregate queries, VBA and temp table - I have used them all to get desired output

[crobaseball]

Just out of curiosity, does the project tend to drive the structure you use, or over time has it become clear to you that there's a particular structure which tends to be more robust over another?

[June7]

Guess I would have to say the project drives the structure. I find it is a balancing act between normalization and ease of data entry/output. I have dbs that defy normalization rules at some point because it suits my needs.

[crobaseball]

That's what i would have guessed, but I'm new enough that i didn't want to assume

[June7]

Start with identifying data entities and relationships. Normalize until it hurts more than it helps.

[crobaseball]

That sounds a lot like what I've done. I've had to organize the data in a similar fashion. It seems like a logical data structure itself should then neatly fit into a report with minimal maneuvering. That's certainly the case with this. Tho I was trying too hard with some of the grouping on the report instead of just using subreports which make much more sense.

[crobaseball]

Last bit of maneuvaring. The items which we have are broken up into sections. So I basically need the report to look like the following:


The key is that all of the pricing information is stored in one table. It is then connected by series, in this case, Amber Valley which titles at the top. But the categories of the pricing, as you can see, are Flat and Trim. I created a sub report to list the size, descript, price fields etc., and embed it in another subreport which broke down everything into the categores of flat and trim. These categories have one to many relationship with the items, not with the seires. So here's my issue: When I then go to create the master report, and go to embed the 2nd level sub into the main, the connection is from series (Amber Valley) to the items directly. What do I need to do here when linking the master/child fields? Do I need to group this somehow? Unsure of where to go (obviously)