Question Display "headers" over grouped items in table

[redbe4rd]

Hello all,

I have a table of items for our companies quotes. When we go to print out our quotes to send to the customers, the salespeople would like most of the items to be grouped under certain "headers" for the systems they are part of.

Ex:

Autopilot System
Part #1 Qty 2
Part #2 Qty 1

PA/GA System
Part #4 Qty 4
Part #5 Qty 1

My 'Items' table currently has these fields:

ID (PK)
System_ID (FK for 'Systems' table)
Part_No
Qty

etc...

'Systems' table has these:

ID (PK)
Sys_Description

I have a query using a RIGHT JOIN and a GROUP BY to tie everything together, I'm just not certain how to go about displaying the information the way I did above.

 

[NeutronFlux]

I haven't tried this out yet, it's just an idea floating in my head:
Create a query that lists the groups, such as a Distinct query on the group field. Tie that to your main report. Create a sub-report that is tied to the main report through the group field.

 

[plog]

You're not going to kill to many orphans and nuns by playing around with it. Don't be afraid to get in there and try your ideas out.

With that said, you shouldn't need a sub-report. Just one query to bring it all together, you can then use a grouping in your report to get all the parts under the appropriate System Heading.

 

[redbe4rd]

I got my report looking proper using the grouping like plog suggested.

Is there any way to accomplish this same feat on a form?

Personally, it doesn't bother me having the system repeated on every line when viewing it on a form, but for aesthetic purposes, they'd prefer it not. If it's too much of a hassle to accomplish, I'll just tell them it's not possible.

 

[plog]

I think you could accomplish what you want in a Form by using sub-forms. Your main form would hold feed off the Systems table and then the sub-form would show all the data in parts that match the System.

 

[redbe4rd]

I've tried using sub-forms, but it won't let me display the main form in Continuous Form mode, so it limits me to viewing one system at a time.