query multiple records for a report

[Pete P.]

I am looking for assistance on running the "Purchase Orders" report in the MS
Access database template Inventory control. In this database each product has
a "Category" assignment. I need to have the categories for each of the items
on the P.O. show with a dollar total for each category. I have tried creating
a sub-report based on a category query. The appropriate information shows up
in the report previesw if only on item is shown. If multiple items are shown
in the "details" section of the report, the "category" sub-report (located in
the section below "details") registers only the last item from the details
section. Is there a way to get the sub-report to register data from each
record in the "details" section. Sorry for the length of this question. Any
help is appreciated. Pete P.

 

[Marshall Barton]

I am looking for assistance on running the "Purchase Orders" report in the MS
Access database template Inventory control. In this database each product has
a "Category" assignment. I need to have the categories for each of the items
on the P.O. show with a dollar total for each category. I have tried creating
a sub-report based on a category query. The appropriate information shows up
in the report previesw if only on item is shown. If multiple items are shown
in the "details" section of the report, the "category" sub-report (located in
the section below "details") registers only the last item from the details
section. Is there a way to get the sub-report to register data from each
record in the "details" section. Sorry for the length of this question. Any
help is appreciated. Pete P.

That's kind of vague so I'm not sure what you really need
here. However, there are several points that might be
relevant.

It sounds like you want the subreport to be in the detail
section. Be sure to set the subreport control's Link
Child/Master properties appropriately.

The group footer section is normally used to display totals
of the details in the group. This is easy to do with text
boxes that have expressions that use one or more aggregate
functions (Count, Sum, Avg, etc).

From what I can follow in your question, I don't see the
need for a subreport. Setting the report's record source to
a query that Joins the two table is both simpler and more
efficient.

 

[Pete P.]

Marshall, Thank you for your reply. You are grasping my dilemma. I discovered
that putting the sub-report into the "details" section accomplishes the task
numerically, but visually I need to group the purchase items and categories
separately. Your suggestion about not using a sub-report is intriguing, but
as I'm using a pre-designed template from Microsoft that has a fairly
complicated SQL already written I am kind of scared to mess with it for fear
I might create a bad situation I can't fix.

Would there be a way to visually group the purchase items and categories
separately in the details section, or will they always alternate: Item,
category, item category, etc.? In addition I need separate sub-totals for
each category. The category grand total should equal the purchased items
total. I apologize for being vague, but without writing a treatise on what
I'm trying to do its difficult to condense.

 

[Marshall Barton]

Marshall, Thank you for your reply. You are grasping my dilemma. I discovered
that putting the sub-report into the "details" section accomplishes the task
numerically, but visually I need to group the purchase items and categories
separately. Your suggestion about not using a sub-report is intriguing, but
as I'm using a pre-designed template from Microsoft that has a fairly
complicated SQL already written I am kind of scared to mess with it for fear
I might create a bad situation I can't fix.

Would there be a way to visually group the purchase items and categories
separately in the details section, or will they always alternate: Item,
category, item category, etc.? In addition I need separate sub-totals for
each category. The category grand total should equal the purchased items
total. I apologize for being vague, but without writing a treatise on what
I'm trying to do its difficult to condense.

You can group on the item types if you have a field in the
main report's record source table/query that can be used to
distinguish the type of the record. That field can then be
used to create an additional level in the report's Sorting
and Grouping.

If the item type field is not in the main report's record
source, then you can not do this. In this case, you really
need to combine at least that field with the main report's
data. If this is necessary, it may make more sense to get
rid of the subreports.