Combining items in report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pilgrim - 29 Jan 2007 13:29 GMT

I have a table with fields [quantity] [Part#] Description]
In this table records repeat themselves with same part number and
description but the qty changes. I would like a report that combines the
parts numbers that are same and totals the qty. Is this possible?

Reply to this Message

Jeff Boyce - 29 Jan 2007 13:50 GMT

If you want the "sum" of like part#s, you can use a query to do this (the
"Totals" queries).

If you want to add a total to your detail report, you can do this as well.
In fact, the New Report Wizard will even help you do this the first time.

Signature

Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

> I have a table with fields [quantity] [Part#] Description]
> In this table records repeat themselves with same part number and
> description but the qty changes. I would like a report that combines the
> parts numbers that are same and totals the qty. Is this possible?

Reply to this Message

Marshall Barton - 29 Jan 2007 14:18 GMT

>I have a table with fields [quantity] [Part#] Description]
>In this table records repeat themselves with same part number and
>description but the qty changes. I would like a report that combines the
>parts numbers that are same and totals the qty. Is this possible?

Sure it's possible. Generally, you should create a Totals
type (GROUP BY) query that does the calculations. Lacking
any other information, the qury's SQL would be along these
lines:
SELECT T.[Part#], T.[Description],
Sum(T.Quantity) As TotalQty
FROM table As T
GROUP BY T.[Part#], T.[Description]

Then use the query as the report's record source and the
rest should be straightforward.

A different (not as flexible and less efficient) way is to
base the report on the table and use Sorting and Grouping to
create a group on the [Part#] field. The group header or
footer section would have text boxes bound to the [Part#]
and [Description] fields and a text box with the expression
=Sum(Quantity). Make the detail section invisible.

Signature

Marsh
MVP [MS Access]

Reply to this Message

pilgrim - 29 Jan 2007 15:59 GMT

Thanks, I used the grouping on the report. It has the added benifit if the
description changes but part # stays same it does not combine. This helps if
I change desciption on the fly or add comments in that field. Thanks Again, Ed

> >I have a table with fields [quantity] [Part#] Description]
> >In this table records repeat themselves with same part number and
[quoted text clipped - 19 lines]
> and [Description] fields and a text box with the expression
> =Sum(Quantity). Make the detail section invisible.

Reply to this Message