Combining multiple records that should be treated as one

[AnotherJob]

I am frustrated with a Jet-SQL update query for Microsoft Office 2003. I have some customer delivery records and I need to combine all deliveries to the same customer that occurred on the same day. As a greatly simplified example, suppose my data is like:

CustomerNumber   DeliveryDate   QuantityDelivered
1                1/11/2008       5
1                2/11/2008      17
1                3/16/2008       9
2                1/14/2008      13
2                1/14/2008       3
2                2/10/2008      17
2                2/28/2008       5

The first two records shown for customer number 2 are on the same date. I'd like to copy all date from this table into a second table, summing the Quantity Delivered values that were made to the same customer and date, producting:

1                1/11/2008       5
1                2/11/2008      17
1                3/16/2008       9
2                1/14/2008      16
2                2/10/2008      17
2                2/28/2008       5

In the actual case I have thousands of records with scores of multiple deliveries to various customers.

I can get a list of all multiple deliveries with:

SELECT a.* FROM tblDeliveries AS a INNER JOIN 
(SELECT CustomerNumber, DeliveryDate FROM tblDeliveries AS b GROUP BY CustomerNumber, DeliveryDate) AS b 
ON a.CustomerNumber=b.CustomerNumber AND a.DeliveryDate=b.DeliveryDate;

But I haven't been able to get farther than that. Any suggestions please?

 

[r937]

SELECT CustomerNumber
     , DeliveryDate 
     , SUM(QuantityDelivered) AS QuantityDelivered
  INTO my_new_table
  FROM tblDeliveries
GROUP 
    BY CustomerNumber
     , DeliveryDate

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon