Help with SQL command grouping

Hi,

I have 3 tables: Deliveries, IssuedWarehouse, ReturnedStock.

Deliveries:
ID
OrderNumber
Material
Width
Gauge
DelKG

IssuedWarehouse:
OrderNumber
IssuedKG

ReturnedStock:
OrderNumber
IssuedKG

What I'd like to do is group all the orders by Material, Width and Gauge and then sum the amount delivered, issued to the warehouse and issued back to stock. This is the SQL that is really quite close:

SELECT tblFILM07_DELIVERIES.Material, tblFILM07_DELIVERIES.Width, tblFILM07_DELIVERIES.Gauge, Count(tblFILM07_DELIVERIES.OrderNo) AS [Orders Placed], Sum(tblFILM07_DELIVERIES.DeldQtyKilos) AS [KG Delivered], Sum(WarehouseFilmIissued.[Qty Issued]) AS [Film Issued], Sum([Film Retns].[Qty Issued]) AS [Film Returned], [KG Delivered]-[Film Issued]+[Film Returned] AS [Qty Remaining]

FROM (tblFILM07_DELIVERIES INNER JOIN WarehouseFilmIissued ON tblFILM07_DELIVERIES.OrderNo = WarehouseFilmIissued.[Order No From]) INNER JOIN [Film Retns] ON tblFILM07_DELIVERIES.OrderNo = [Film Retns].[Order No From]

GROUP BY tblFILM07_DELIVERIES.Material, tblFILM07_DELIVERIES.Width, tblFILM07_DELIVERIES.Gauge, tblFILM07_DELIVERIES.ActDelDate
HAVING (((tblFILM07_DELIVERIES.ActDelDate) Between [start date] And [end date]))

ORDER BY tblFILM07_DELIVERIES.Material;

This groups the products almost perfectly. However if you take a look at the results:

Material Width Gauge Orders Placed Delivered Qnty Kilos Film Issued Film Returned Qty Remaining
COEX-GLOSS 590 75 1 534 500 124 158
COEX-MATT 1080 80 1 4226 4226 52 52
CPP 660 38 8 6720 2768 1384 5336
CPP 666 47 1 5677 5716 536 497
CPP 690 65 2 1232 717 202 717
CPP 760 38 3 3444 1318 510 2636
CPP 770 38 4 4316 3318 2592 3590
CPP 786 38 2 672 442 212 442
CPP 800 47 1 1122 1122 116 116
CPP 810 47 1 1127 1134 69 62
CPP 810 47 2 2250 1285 320 1285
CPP 1460 38 12 6540 4704 2442 4278
LD 975 75 1 502 502 182 182
LDPE 450 50 1 252 252 50 50
LDPE 520 70 1 250 250 95 95
LDPE 570 65 2 504 295 86 295
LDPE 570 65 2 508 278 48 278
LDPE 620 50 1 252 252 67 67
LDPE 660 50 1 256 256 62 62
LDPE 670 75 1 248 248 80 80
LDPE 690 47 1 476 476 390 390
LDPE 790 38 2 2104 1122 140 1122
LDPE 790 50 1 286 286 134 134
LDPE 790 50 1 250 250 125 125
LDPE 810 30 1 4062 4062 100 100
LDPE 843 33 1 408 408 835 835
LDPE 850 80 1 412 412 34 34
LDPE 855 30 1 740 740 83 83
LDPE 880 60 1 304 304 130 130
LDPE 900 70 2 1000 650 500 850
LDPE 1017 60 1 1056 1056 174 174
OPP 25 1100 1 381 381 95 95
OPP 1000 30 2 1358 1112 300 546
OPP 1000 30 1 1492 1491 100 101
OPP 1200 20 1 418 417 461 462
PET 760 12 3 1227 1876 132 -517

You'll see that there are some materials that have the same width and gauge yet they are not grouped. I think this is because the delivered qty is different on the orders. For example:

Material Width Gauge Orders Placed Delivered Qnty Kilos Film Issued Film Returned Qty Remaining
LDPE 620 50 1 252 252 67 67
LDPE 660 50 1 256 256 62 62

I would like these two row's to be grouped. They have the same material, width and gauge but the delivered qty is different therefore it hasn't grouped it.

Can anyway help me group these strange rows?

Thanks in advance,

Paul.

when i got to your HAVING clause, i realized this wasn't for microsoft sql server, but instead, for microsoft access

so i've moved the thread to the Databases forum

Thank you very much. That's very helpful.

Paul.