Combine multiple rows into one record

[Joe]

Hello everyone -

I have a query that queries our Customer Orders & Shipping tables. This
query returns data for all shipped customer orders. I have a slight
problem however. When our shipping department ships an order, sometimes
they ship it in multiple packages at different times of the day. When
they enter this into our system, it appears as different shipments.

For example, if a customer orders 100 pieces our shipping department
might ship 50 pieces in the morning, 25 pieces in the afternoon, and 25
pieces before closing. This then appears in 3 rows in my query.

Example:

Order ID: Order Qty: Ship Qty:
139756 100 25
139756 100 25
139756 100 50

My question is - how can I have my query combine these into one record
by checking to see if the sum of the Ship Qty rows is equal the first
rows Order Qty.

Basically, how can I have my query say If the sum of the Ship Qty
(25+25+50) = Order Qty (100) combine into one record return as:

Order ID: Order Qty: Ship Qty:
139756 100 100

If the sum of the ship quantities does not = the order quantity it is
fine if it breaks up into multiple rows because we want to identify
missed shipments.

Thanks for all your help.

 

[John Spencer]

Probably by using a UNION query that would similar to below. Sheer
guesswork on the names of your tables and fields and the actual structure.

SELECT O.[Order ID], [Order Qty], Sum([Ship qty]) as Shipped
FROM ORDERS INNER JOIN SHIPPING AS S
ON O.[Order ID] = S.[Order ID]
GROUP BY O.[Order ID], O.[Order Qty]
HAVING O.[Order Qty] = Sum([Ship qty])

UNION ALL

SELECT O.[Order ID], [Order Qty], [Ship qty]
FROM ORDERS INNER JOIN SHIPPING AS S
ON O.[Order ID] = S.[Order ID]
WHERE O.[Order ID] In (
SELECT O.[Order ID]
FROM ORDERS INNER JOIN SHIPPING AS S
ON O.[Order ID] = S.[Order ID]
GROUP BY O.[Order ID], O.[Order Qty]
HAVING O.[Order Qty] = Sum([Ship qty]) )

 

[Allen Browne]

In query design view, depress the Total button on the toolbar (upper sigma
icon.) Access adds a Total row to the grid.

In the total row under the [Order ID] field, accept Group By.
Under the [Order Qty] field, choose First in the Total row.
Under [Ship Qty], choose Sum.

The query groups by [Order ID], so you get one row for each order.

It shows the value from the first row for [Order Qty], since this is
presumably the same on each row for this order.

And it sums the [Ship Qty] field, so show the total number shipped for the
order.

 

[Joe]

Allen,

This did not seem to make any difference to the results of my query.

 

[Allen Browne]

Did you get the Total row showing in the query design grid?

Did you set the 3 fields as Group By, First, and Sum?

Did you omit any other fields you might have had?

If you are still stuck, switch the query to SQL View (View menu), copy the
SQL statement, and paste it here in a followup.

 

[Joe]

Allen,

I'm sorry - this does seem to work but only when I have no other fields
selected for my query. Is there any way that I can include other fields
and still have this work?

I would like to show 3 more fields which are Customer
Order.DesiredShipDate, Customer Order.PartID and Shipping.ShipDate.

 

[Joe]

Allen,

I'm sorry - this does seem to work but only when I have no other fields
selected for my query. Is there any way that I can include other fields
and still have this work?

I would like to show 3 more fields which are Customer
Order.DesiredShipDate, Customer Order.PartID and Shipping.ShipDate.

I would then like to be able to use the DesiredShipDate as a critiera
for my query. Showing only orders desired to ship on "such and such" a
date.

 

[Joe]

Allen,

This is my current SQL statement before making the changes you
suggested.

SELECT dbo_CUSTOMER_ORDER.ID, dbo_CUST_ORDER_LINE.LINE_NO,
dbo_CUST_ORDER_LINE.PROMISE_DATE, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_SHIPPER_LINE.SHIPPED_QTY, dbo_PART.ID, dbo_SHIPPER.SHIPPED_DATE
FROM dbo_PART INNER JOIN ((((dbo_CUSTOMER INNER JOIN dbo_CUSTOMER_ORDER
ON dbo_CUSTOMER.ID = dbo_CUSTOMER_ORDER.CUSTOMER_ID) INNER JOIN
dbo_CUST_ORDER_LINE ON dbo_CUSTOMER_ORDER.ID =
dbo_CUST_ORDER_LINE.CUST_ORDER_ID) INNER JOIN dbo_SHIPPER_LINE ON
(dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_SHIPPER_LINE.CUST_ORDER_ID)
AND (dbo_CUST_ORDER_LINE.LINE_NO =
dbo_SHIPPER_LINE.CUST_ORDER_LINE_NO)) INNER JOIN dbo_SHIPPER ON
dbo_SHIPPER_LINE.PACKLIST_ID = dbo_SHIPPER.PACKLIST_ID) ON dbo_PART.ID
= dbo_CUST_ORDER_LINE.PART_ID
GROUP BY dbo_CUSTOMER_ORDER.ID, dbo_CUST_ORDER_LINE.LINE_NO,
dbo_CUST_ORDER_LINE.PROMISE_DATE, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_SHIPPER_LINE.SHIPPED_QTY, dbo_PART.ID, dbo_SHIPPER.SHIPPED_DATE
HAVING (((dbo_CUST_ORDER_LINE.PROMISE_DATE)=[Forms]![frm Promise Date
Attainment]![PromiseDate]) AND
((dbo_SHIPPER.SHIPPED_DATE)<=[Forms]![frm Promise Date
Attainment]![PromiseDate]));

 

[Allen Browne]

So what do you want to show on each row here?

Perhaps you use First in the Total row udner DesiredShipDate.

But if there are 3 shipping dates, which one do you want to show in the
query results? The earliest one - use Min. That lastest one - use Max.

If there are multiple parts in the order, I'm not sure what you want do show
in the summary query.

 

[Joe]

The orders that are being split into multiple rows that I want to
combine should all have a ship date on or before the promise date of
the order.

I am trying to pull a listing of all orders that were promised to ship
to the customer (dbo_CUST_ORDER_LINE.PROMISE_DATE) for a certain date
but I only want to show orders that were shipped on or before that
promise date (dbo_SHIPPER.SHIPPED_DATE).

When I pull this information, an order will show up multiple times
because our shipping department sometimes splits up orders into
multiple shipments like I said.

So instead of one record looking like this:

dbo_CUSTOMER_ORDER_ID PROMISE_DATE ORDER_QTY SHIPPED_QTY dbo_PART_ID SHIPPED_DATE

139756 1/23/2006 100 100 E39AFG-3 10/20/2005

I get:
139756 9 1/23/2006 100 20 E39AFG-3 10/20/2005
139756 9 1/23/2006 100 30 E39AFG-3 10/21/2005
139756 9 1/23/2006 100 50 E39AFG-3 1/4/2006

 

[Allen Browne]

Joe, you will need to decide what to use under the Total row for each field.

I think your example suggests you want Max under the Shipped_Date field, but
ultimately these are your call.

You cannot use Group By, unless you want to create multiple rows (as you do
with the order number.)