Grouping

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gary Nelson - 25 Jul 2003 19:58 GMT

In Access2000, I have a query that is showing orders received on a given
day. I have the query is linked to a report.

The purpose of this report is to show our production department orders
received today. The report will give them the information they need in
order to produce our product. There are several instances when the same
item is ordered, and shows up on the report as a seperate entry, but has the
same part number, but different quantity.

I would like to group the orders that have the same part number, so that
only one order for the part number will appear on the report, but also sum
the quantity.

For example:

Part Number: Quantity:

123456 2
123456 1
123456 2

I would like it to appear as follows:

123456 5

Can you please offer some assitance as to how my query or report should be
adjusted?

Thanks,
Gary

Reply to this Message

Douglas J. Steele - 25 Jul 2003 21:00 GMT

You want a Totals query.

To do this through the Query Builder, create a query that select Part Number
and Quantity, then click on the Totals button (the little Sigma on the tool
bar), or select View | Totals. You'll notice a new "Total" row in the grid
under the table names. Select Group By under Part Number, and Sum under
Quantity.

The SQL would be something like:

SELECT PartNumber, Sum(Quantity)
FROM MyTable
GROUP BY PartNumber

BTW, this question has nothing to do with the Developer Toolkit, the topic
of this group. You would probably have been better off asking it in
microsoft.public.access.query

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

> In Access2000, I have a query that is showing orders received on a given
> day. I have the query is linked to a report.
[quoted text clipped - 26 lines]
> Thanks,
> Gary

Reply to this Message