Split recordset of Query

G

Guest

Hi all,
I am trying to run a query to create shipping labels. What I am currently
doing is grouping on the order_ID and creating one shipping label per order.
However, I would like to have another shipping label print out upon every
break at 25 of item_qty, so if I had 30 items, I would have two labels...
Here is my current SQL for the query:
SELECT orders.Order_ID, orders.ShipTo_FName, orders.ShipTo_LName,
orders.ShipTo_Organization, orders.ShipTo_Addr1, orders.ShipTo_Addr2,
orders.ShipTo_City, orders.ShipTo_State, orders.ShipTo_ZIP5,
orders.ShipTo_Country, orders.ShipTo_Phone, orders.ShipTo_Ext,
orders.ShipTo_Email, First(production_shipments.Item_ID) AS FirstOfItem_ID,
Sum(production_shipments.Ship_Qty) AS SumOfShip_Qty
FROM orders INNER JOIN production_shipments ON orders.Order_ID =
production_shipments.Order_ID
WHERE (((production_shipments.Ship_Order_ID)<>0) AND
((production_shipments.Ship_Date) Is Null))
GROUP BY orders.Order_ID, orders.ShipTo_FName, orders.ShipTo_LName,
orders.ShipTo_Organization, orders.ShipTo_Addr1, orders.ShipTo_Addr2,
orders.ShipTo_City, orders.ShipTo_State, orders.ShipTo_ZIP5,
orders.ShipTo_Country, orders.ShipTo_Phone, orders.ShipTo_Ext,
orders.ShipTo_Email;
Any help would be appreciated and if any more information is needed, please
let me know, thanks!
-gary
 
A

Amy Blankenship

Gary Dolliver said:
Hi all,
I am trying to run a query to create shipping labels. What I am currently
doing is grouping on the order_ID and creating one shipping label per
order.
However, I would like to have another shipping label print out upon every
break at 25 of item_qty, so if I had 30 items, I would have two labels...
Here is my current SQL for the query:
SELECT orders.Order_ID, orders.ShipTo_FName, orders.ShipTo_LName,
orders.ShipTo_Organization, orders.ShipTo_Addr1, orders.ShipTo_Addr2,
orders.ShipTo_City, orders.ShipTo_State, orders.ShipTo_ZIP5,
orders.ShipTo_Country, orders.ShipTo_Phone, orders.ShipTo_Ext,
orders.ShipTo_Email, First(production_shipments.Item_ID) AS
FirstOfItem_ID,
Sum(production_shipments.Ship_Qty) AS SumOfShip_Qty
FROM orders INNER JOIN production_shipments ON orders.Order_ID =
production_shipments.Order_ID
WHERE (((production_shipments.Ship_Order_ID)<>0) AND
((production_shipments.Ship_Date) Is Null))
GROUP BY orders.Order_ID, orders.ShipTo_FName, orders.ShipTo_LName,
orders.ShipTo_Organization, orders.ShipTo_Addr1, orders.ShipTo_Addr2,
orders.ShipTo_City, orders.ShipTo_State, orders.ShipTo_ZIP5,
orders.ShipTo_Country, orders.ShipTo_Phone, orders.ShipTo_Ext,
orders.ShipTo_Email;
Any help would be appreciated and if any more information is needed,
please
let me know, thanks!
Click to expand...

Add a Having clause with Count < 25, then union with an identical query with
a having clause > 25.

HTH;

Amy
 
G

Guest

thank you for the reply, I think this makes sense, but what would I do if the
Item Sum is 80? I would need this to be broken into 4 labels (25, 25, 25, 5
- all to the same address)? Sorry, any thoughts on this? Thanks!
-gary
 
G

Guest

Gary,

I have a table (tbl_Numbers) that only has a single field (Numbers), and
only has 10 records (0-9). I use this table for a variety of things, and
this would be a great way to do it. I've created an Orders and Order_Details
table in my database to test this, and it worked OK. I'll give you the SQL
that I used and you can modify it for your purposes:

SELECT tbl_Orders.Order_ID, tbl_Orders.Order_LastName,
tbl_Order_Details.Part, Sum(tbl_Order_Details.Qty) AS SumOfQty,
tbl_Numbers.Numbers AS Lbl_No
FROM tbl_Numbers, tbl_Orders INNER JOIN tbl_Order_Details
ON tbl_Orders.Order_ID = tbl_Order_Details.Order_ID
GROUP BY tbl_Orders.Order_ID, tbl_Orders.Order_LastName,
tbl_Order_Details.Part, tbl_Numbers.Numbers
HAVING tbl_Numbers.Numbers>0
AND tbl_Numbers.Numbers<=Int(Sum([QTY])/25)+1;

The key here is using a Cartesian join to the numbers table (see the From
line) and then limiting which records in the Numbers table to use(see the
Having clause). I wanted my Lbl_No values to start at one and run upwards
from there.

HTH
Dale
 
A

Amy Blankenship

Dale Fye said:
Gary,

I have a table (tbl_Numbers) that only has a single field (Numbers), and
only has 10 records (0-9). I use this table for a variety of things, and
this would be a great way to do it. I've created an Orders and
Order_Details
table in my database to test this, and it worked OK. I'll give you the
SQL
that I used and you can modify it for your purposes:

SELECT tbl_Orders.Order_ID, tbl_Orders.Order_LastName,
tbl_Order_Details.Part, Sum(tbl_Order_Details.Qty) AS SumOfQty,
tbl_Numbers.Numbers AS Lbl_No
FROM tbl_Numbers, tbl_Orders INNER JOIN tbl_Order_Details
ON tbl_Orders.Order_ID = tbl_Order_Details.Order_ID
GROUP BY tbl_Orders.Order_ID, tbl_Orders.Order_LastName,
tbl_Order_Details.Part, tbl_Numbers.Numbers
HAVING tbl_Numbers.Numbers>0
AND tbl_Numbers.Numbers<=Int(Sum([QTY])/25)+1;

The key here is using a Cartesian join to the numbers table (see the From
line) and then limiting which records in the Numbers table to use(see the
Having clause). I wanted my Lbl_No values to start at one and run upwards
from there.
Click to expand...

That's pretty cool ;-)
 
Top