Multiple labels from order line quantities

[Tusk]

I have a label report that prints out address labels for customer orders. This all works just fine, however, the client wants to have a separate label where the quantity is more than 1 i.e. product = Blue Widget, Quantity = 2; This should generate 2 labels for this customer.

Any ideas of how to achieve this would be welcome before I lose the will to live

Tusk

 

[Remou]

You could use a numbers table containing integer from one to the maximum quantity, the set up a Cartesian join:

SELECT t.Id, t.Quantity
FROM Numbers n, Widgets t 
WHERE n.Number<=t.Quantity[code]


[URL unfurl="true"]http://lessthandot.com[/URL]

 

[Tusk]

Hi Remou, not sure I understand what you are getting at with this? Where does this go in relation to the report?

I have a customers, orders, and orders detail tables. The orders detail table holds the order ID, product ID, and quantity ordered.

What the client wants is to be able to run a report that produces mailing labels but rather than the traditional one label for one order, they want one label for the selected product and if the quantity is, say 4, print 4 address labels.

I can get the report to run the product select and print one label for each selected line item, but can't work out how to do the next bit, which is the multiple labels for a line item.

 

[Remou]

The idea is that you base your report on a query that joins a numbers table to your tables or query, I am not sure of the structure of your tables so I can only give an example:

SELECT c.customername, o.whatever, od.orderID, 
       od.productID, od.quantity
FROM numbers n, (
     orders o 
INNER JOIN orderdetail od 
ON o.orderid = od.orderid) 
INNER JOIN customers c
ON o.customerid = c.customerid
WHERE n.number<=od.[quantity]

If it is much more complicated, you may need to join numbers to a query or use a subquery. It creates a label up to the quantity ordered because of the Cartesian join (

http://en.wikipedia.org/wiki/Join_(SQL))

http://lessthandot.com

 

[Tusk]

Ok I think I can see where you are going with this. Question, where does the numbers table come from and what should it hold as fields or data?

 

[Remou]

As I said in the first post "a numbers table containing integer from one to the maximum quantity". You should create the tabl, such a table can be very useful.

http://lessthandot.com

 

[Tusk]

Hi Remou,

Had a go with the numbers table. By the way this seems to work, the numbers table will need to be reset for each order line, not really what I had in mind!

I tried the code you posted, editing the query to fit my tables. The query runs and asks for the productID, which I give (one with an order quantity greater than 1), it then asks for the number i.e. 2!, then goes on to product a list of 20 lines of the same data.

It is possible that order quantities may go as high as 20, so I created the numbers table with 20 lines 1 - 20, which explains the 20 output records!

What i am looking for is for the query to output a mailing label for each line item in the order details table for a specific product (in this case books)but where the order quantity is more than 1 to output the number of labels in the order quantity i.e.

OrderID Title Order Quantity Number of labels
1 T1 1 1
2 T1 2 2
3 T1 1 1
4 T1 5 5

I can see what you were getting at with this query and it does seem to be going in the right direction, but isn't there yet

 

[dhookom]

Tusk,
You should have provided the SQL for your query so we could more easily correct your mistake where I assume you didn't include the where clause part suggested by Remou.

WHERE n.number<=od.[quantity]

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

I rarely post without testing, and what I posted does exactly what you want, however, it does need to be adjusted for your set-up, which I had to guess at. Please post the SQL you tried and your table schemas, if you want a final answer.

http://lessthandot.com

 

[Tusk]

Yes, I did include the WHERE statement as supplied, and I did adjust this for my setup.

I have to admit to deleting the original query as it didn't seem to work for me . I will rebuild it again using Remou's solution and provide the necessary information to this post.