Use of Non equi join Queries

[LeLe]

I am producing 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.

Any help is greatly appreciated.

 

[John W. Vinson]

I am producing 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.

Any help is greatly appreciated.

Please post the SQL view of your current query. You shouldn't need another
join; my guess is that you'll need a DCount() of some sort, but can't tell
without knowing your structure.

 

[LeLe]

Ok here it is:
SELECT OrderHeader.CustomerName, OrderLineItems.OrderNumber,
OrderHeader.SalesOrdNum, OrderHeader.DatePromised, OrderLineItems.LineNumber,
Num.N, NumberOfPieces.Pieces, OrderHeader.CustomerPO, OrderHeader.Sidemark,
OrderHeader.Room, OrderLineItems.Item, OrderHeader.Date, OrderLineItems.Qty,
OrderLineItems.UOM, OrderLineItems.RodWidth, OrderLineItems.Overlap,
OrderLineItems.ReturnL, OrderLineItems.ReturnR, OrderLineItems.Fullness,
OrderLineItems.TopSize, OrderLineItems.BuckrumType,
IIf([BuckrumType]="Translucent" Or [BuckrumType]="no",[TopdescText] & " - " &
[BuckrumType] & " Buckrum",[TopdescText]) AS TopDesc, OrderLineItems.FLength,
OrderLineItems.Hem, OrderLineItems.fabric,
(-Int(-([FLength]+[TopSize]+[TopSize]+[hem]+[hem]+2)/nz([repeat])))*nz([repeat])
AS CutLength,
Round([qty]*((([RodWidth]+[Overlap]+[ReturnL]+[ReturnR])*[FULLNESS])/[FabricWidth]))
AS NumbOfCuts, Round(([CutLength]*[NumbOfCuts])/36,1) AS YdsRequired,
IIf([UOM]="Pair",2,1) AS WES,
IIf([Item]="PPDrapery",Round([NumbOfCuts]/[WES]),IIf([Item]="Shade",Round([RodWidth]*[FLength]/144),1))
AS PriceUnits, IIf([Room] Is Not Null," - " & [Room]) AS WkTkRM,
IIf([salesordnum] Is Not Null," - SO# " & [SalesOrdNum]) AS Wkso,
NumberOfPieces.UOMp, NumberOfPieces.QtyP, OrderLineItems.WidthPerPnl,
OrderLineItems.Lining, OrderLineItems.Interlned, OrderLineItems.CThread,
OrderLineItems.[BndTR/LedEdg], OrderLineItems.[BndTR/TtrlEdg],
OrderLineItems.[BndTR/Bottom], OrderLineItems.[BndTR/top],
OrderLineItems.Comments, OrderLineItems.rdpktTop, OrderLineItems.rdpktBot,
OrderLineItems.headtop, OrderLineItems.headBot, IIf([fullness]>0.1,[fullness]
& " X 1 fullness","") AS Fulllness, Patterns.PatternName,
Patterns.FabricWidth, Patterns.Repeat, IIf(IsNull([color]),""," " & [color])
AS Collor, IIf(IsNull([fabricWidth]),""," @ " & [fabricWidth] & "'' wide") AS
FabWiddth, IIf(IsNull([Repeat]),""," @ " & [Repeat] & " inches repeat") AS
FabReppeat, [PatternName] & [Collor] & [FabWiddth] & [FabReppeat] AS
FabbDesc, OrderLineItems.Mount, OrderLineItems.StringNumber,
OrderLineItems.CordPostion, OrderLineItems.CordLength,
OrderLineItems.CordDraw, OrderLineItems.CordColor
FROM TopDesc INNER JOIN ((Patterns INNER JOIN Fabrics ON
Patterns.PatternID=Fabrics.PatternID) INNER JOIN (OrderHeader INNER JOIN
(OrderLineItems INNER JOIN (Num INNER JOIN NumberOfPieces ON
Num.N<=NumberOfPieces.Pieces) ON
OrderLineItems.LineNumber=NumberOfPieces.LineNumber) ON
OrderHeader.OrderNumber=OrderLineItems.OrderNumber) ON
Fabrics.FabricID=OrderLineItems.fabric) ON
TopDesc.TopDescCode=OrderLineItems.TopDescCode
WHERE (((OrderLineItems.OrderNumber)=forms!orderEntry!OrderNumber))
ORDER BY OrderLineItems.OrderNumber, OrderLineItems.LineNumber, Num.N,
OrderLineItems.Item;


Again, thanks for your help. It was 3 years you showed me the Nonequi join
query and it has been a big help in our business.

 

[LeLe]

I have simplified my query so it will be easier to work with:
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
Thanks so much for any suggestions.