"non equi join query"

[LeLe]

I produce work tickets for our production people. For example if my order is
for 3 pillows and 2 bedskirts, I produce 5 work tickets. They say 1 or 3
pillows, 2 of 3 pillows, 3 of 3 pillows, 1 of 2 bedskirts, 2 of 2 bedskirts.
This part works great!!(I was able to do with with help I received from this
discussion group by use of a "non equi join query"

Now I am trying to get each of the tickets to sum up the total number of
items on the order and create a count accordingly. I would still like to
produce 5 tickets which would still have the same count 1 of 3 pillows etc,
but also like to have each of the 5 tickets say 1 of 5, 2 or 5 etc items on
the order. I have tried adding a second nonequi join to my query but this
ends up creating too many tickets and still does not give the right count on
each ticket.


My query looks like this:
SELECT OrderLineItems.OrderNumber, OrderLineItems.LineNumber, Num.N,
NumberOfPieces.Pieces, OrderLineItems.Item, OrderLineItems.Qty,
OrderLineItems.UOM, IIf([UOM]="Pair",2,1) AS WES, NumberOfPieces.UOMp,
NumberOfPieces.QtyP
FROM Num INNER JOIN (OrderHeader INNER JOIN (OrderLineItems INNER JOIN
NumberOfPieces ON OrderLineItems.LineNumber = NumberOfPieces.LineNumber) ON
OrderHeader.OrderNumber = OrderLineItems.OrderNumber) ON Num.N <=
NumberOfPieces.Pieces
WHERE (((OrderLineItems.OrderNumber)=[forms]![orderEntry]![OrderNumber]))
ORDER BY OrderLineItems.OrderNumber, OrderLineItems.LineNumber, Num.N,
OrderLineItems.Item;

The one thing I should mention much of our business involves producing
draperies which are generally (but not always) sold in pairs. This is what my
number of Pieces table is about.
Therefore if my order is for 1 pillow & 2 pairs of draperies I am now
creating 5 tickets, as follows:
1 of 1 pillow
1 of 2 pairs drapes Left Panel
1 of 2 pairs drapes Right Panel
2 of 2 pairs Drapes Left Panel
2 of 2 pairs Drapes Right Panel

I am looking to add the following info to the ticket:
1 of 1 pillow 1 of 3 items on order
1 of 2 pairs drapes Left Panel 2 of 3 items on the order
1 of 2 pairs drapes Right Panel 2 of 3 items on the order
2 of 2 pairs Drapes Left Panel 3 of 3 items on the order
2 of 2 pairs Drapes Right Panel 3 of 3 items on the order

Any help is greatly appreciated.

 

[Earl D DeARMAN]

I produce work tickets for our production people. For example if my order
is
for 3 pillows and 2 bedskirts, I produce 5 work tickets. They say 1 or 3
pillows, 2 of 3 pillows, 3 of 3 pillows, 1 of 2 bedskirts, 2 of 2
bedskirts.
This part works great!!(I was able to do with with help I received from
this
discussion group by use of a "non equi join query"

Now I am trying to get each of the tickets to sum up the total number of
items on the order and create a count accordingly. I would still like to
produce 5 tickets which would still have the same count 1 of 3 pillows
etc,
but also like to have each of the 5 tickets say 1 of 5, 2 or 5 etc items
on
the order. I have tried adding a second nonequi join to my query but this
ends up creating too many tickets and still does not give the right count
on
each ticket.


My query looks like this:
SELECT OrderLineItems.OrderNumber, OrderLineItems.LineNumber, Num.N,
NumberOfPieces.Pieces, OrderLineItems.Item, OrderLineItems.Qty,
OrderLineItems.UOM, IIf([UOM]="Pair",2,1) AS WES, NumberOfPieces.UOMp,
NumberOfPieces.QtyP
FROM Num INNER JOIN (OrderHeader INNER JOIN (OrderLineItems INNER JOIN
NumberOfPieces ON OrderLineItems.LineNumber = NumberOfPieces.LineNumber)
ON
OrderHeader.OrderNumber = OrderLineItems.OrderNumber) ON Num.N <=
NumberOfPieces.Pieces
WHERE (((OrderLineItems.OrderNumber)=[forms]![orderEntry]![OrderNumber]))
ORDER BY OrderLineItems.OrderNumber, OrderLineItems.LineNumber, Num.N,
OrderLineItems.Item;

The one thing I should mention much of our business involves producing
draperies which are generally (but not always) sold in pairs. This is what
my
number of Pieces table is about.
Therefore if my order is for 1 pillow & 2 pairs of draperies I am now
creating 5 tickets, as follows:
1 of 1 pillow
1 of 2 pairs drapes Left Panel
1 of 2 pairs drapes Right Panel
2 of 2 pairs Drapes Left Panel
2 of 2 pairs Drapes Right Panel

I am looking to add the following info to the ticket:
1 of 1 pillow 1 of 3 items on order
1 of 2 pairs drapes Left Panel 2 of 3 items on the order
1 of 2 pairs drapes Right Panel 2 of 3 items on the order
2 of 2 pairs Drapes Left Panel 3 of 3 items on the order
2 of 2 pairs Drapes Right Panel 3 of 3 items on the order

Any help is greatly appreciated.