Returning multiple rows for each record.

[emihir0]

I have a table



Orders

ID_Order (autoNum)	ID_Cust (int)	amountOrdered (int)	amountPerPackage (int)
1	1	20	10
2	1	10	5
3	1	5	5

And I want my output query to look like this:

packageNo	ID_Order	ID_Cust
1	1	1
2	1	1
3	2	1
4	2	1
5	3	1

Here is a short pseudo-code (same result can be achieved in a few different ways using code).

counter: Int
remainder: Int
counter = 0
remainder = 0

For (OrderID, CustID) in Orders:
While (remainder != 0):
remainder = amountOrdered - amountPerPackage
amountOrdered = amountOrdered - amountPerPackage
counter = counter + 1
output: counter, OrderID, CustID
Loop
Loop

My question, however, is, how do you achieve this using SQL?

Basically I want the output query to be used in a report to print out the packaging labels.

[CJ_London]

Which sql are you referring to? T-SQL (sql server) or Access SQL?

If the latter you would need to use vba code and build it into the print process or create label records which can then be queried from your report

Not sure about T-SQL from an access perspective

[InsuranceGuy]

I haven't seen this accomplished in an Access query before. I believe it's possible in TSQL using recursive references; however, Access does not support those methods.

If you are using Access, you are probably best suited to develop a VBA routine, using your pseudocode, that takes the source data and builds your output table. Your report will then refer to the output data that your VBA routine created.