Use of Dsum in a non equi join query

[LeLe]

I produce work tickets for our production people. For example if my order is
for 2 pillows and 1 bedskirts, I produce 3 work tickets. They say 1 or 2
pillows, 2 of 2 pillows, 1 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 3 tickets which would still have the same count 1 of 2 pillows etc,
but also like to have each of the 3 tickets say 1 of 3, 2 or 3 etc total
items on
the order. As was suggested in this discussion group I added a calcuated
control to my query as
follows:TotalOrderQtySUM("[Qty]","[OrderLineItems]","[OrderNumber]=
[Forms]![OrderEntry]![OrderNumber])" It is working fine. It correctly
returns the total number of items on the order. Now how do I get the running
total value into the query? Do I still need to create a join and then modify
it in the SQL view. Since the Dsum Control is not in any of the tables I
don't think I can create the join.

Any Help is greatly appreciated.

 

[LeLe]

Thanks so much for your help. When I copied your query I got the following
1 of 2 pillows
2 of 2 pillows
1 of 1 bedkskirts.... (this part works great)

but instead of
1 of 3 total items on the order
2 of 3 total items of the order
3 of 3 total items on the order....I got

1 of 2 (types of) items on the order
1 of 2 (types of) items on the order
2 of 2 (types of) items on the order

Perhaps I should be using Sum instead of Count for this second part of the
query?

Thanks for any further help you can provide.

 

[LeLe]

Thanks so much for your help. When I copied your query I got the following
1 of 2 pillows
2 of 2 pillows
1 of 1 bedkskirts.... (this part works great)

but instead of
1 of 3 total items on the order
2 of 3 total items of the order
3 of 3 total items on the order....I got

1 of 2 (types of) items on the order
1 of 2 (types of) items on the order
2 of 2 (types of) items on the order

Perhaps I should be using Sum instead of Count for this second part of the
query?

Thanks for any further help you can provide.

I think the best way I can illustrate a way of doing this is with a query
based on the tables in the sample Northwind Database. Before creating the
query, however its necessary to create a table named Counter with one column
of long integer number data type named CountNumber. This should be filled
with numbers from 1 to whatever is the maximum quantity of each product you'd
expect to be ordered. This can easily be done with a little code in a
procedure like so:

Dim dbs As DAO.Database
Dim strSQL As String
Dim n As Integer

Set dbs = CurrentDb

For n = 1 To 200
strSQL = "INSERT INTO Counter(CountNumber) " & _
"VALUES(" & n & ")"
dbs.Execute strSQL
Next n

which inserts numbers from 1 to 200.

The query is then:

SELECT Orders.OrderID, Products.ProductName,
Counter.CountNumber & " of " & [Order Details].Quantity
AS ProductCount,
(SELECT COUNT(*)
FROM [Order Details] AS [Order Details_1]
WHERE [Order Details_1].OrderID = [Order Details].OrderID
AND [Order Details_1].ProductID <= [Order Details].ProductID)
& " of " &
(SELECT COUNT(*)
FROM [Order Details] AS [Order Details_1]
WHERE [Order Details_1].OrderID = [Order Details].OrderID)
AS ItemCount
FROM Counter, Orders , [Order Details], Products
WHERE Products.ProductID = [Order Details].ProductID
AND Orders.OrderID = [Order Details].OrderID
AND Counter.CountNumber <= [Order Details].Quantity
ORDER BY Orders.OrderID, [Order Details].ProductID,
Counter.CountNumber;

The way it works is that the Order Details table is joined to the Counter
table on Counter.CountNumber <= [Order Details].Quantity, so a row is
returned for every number from 1 to the quantity of the current product
ordered. This gives the sequence of numbers from 1 upwards for each of the
quantity of that item.

The first subquery counts the rows in the OrderDetails table by using the
alias Order Details_1 to correlate this with the outer query where the
OrderID is the same and the ProductID is less than or equal to the current
ProductID. This gives a sequence of numbers form 1 upwards for each product
ordered.

The second subquery is the same but this time is correlated with the outer
query only on the PrderID, so in this case it dives the number of products in
each order.

The serial numbers are in each case correlated with the quantity or the
number of products per order, inserting " of " between the two values.

The query is sorted by Orders.OrderID, [Order Details].ProductID, Counter.
CountNumber, but if being used as the RecordSource for a report the ORDER BY
clause would be omitted and the report's internal sorting and grouping
mechanism used to order the report.

To restrict the results to a specific order you can add an extra line to its
WHERE clause:

AND Orders.OrderID = [Forms]![OrderEntry]![OrderNumber]

changing the table and column names to your own of course. Any other columns
from the tables in the outer query (Counter, Orders , Order Details and
Products in the above example) can of course be included in its SELECT clause.


Ken Sheridan
Stafford, England

I produce work tickets for our production people. For example if my order is
for 2 pillows and 1 bedskirts, I produce 3 work tickets. They say 1 or 2
pillows, 2 of 2 pillows, 1 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 3 tickets which would still have the same count 1 of 2 pillows etc,
but also like to have each of the 3 tickets say 1 of 3, 2 or 3 etc total
items on
the order. As was suggested in this discussion group I added a calcuated
control to my query as
follows:TotalOrderQtySUM("[Qty]","[OrderLineItems]","[OrderNumber]=
[Forms]![OrderEntry]![OrderNumber])" It is working fine. It correctly
returns the total number of items on the order. Now how do I get the running
total value into the query? Do I still need to create a join and then modify
it in the SQL view. Since the Dsum Control is not in any of the tables I
don't think I can create the join.

Any Help is greatly appreciated.

 

[LeLe]

Thanks so much. It works great and now does just what I need. Super!!

I misunderstood your original post; I'd thought it was the types of items you
wanted as the second 'n of n'. The following should do what you want, but it
will be slower. If you are using it to generate the tickets for a single
order it should not be unacceptably slow, however, particularly if you omit
the ORDER BY clause and use the report's internal sorting and grouping
mechanism to order the results:

SELECT Orders.OrderID, Products.ProductName,
Counter.CountNumber & " of " & [Order Details].Quantity
AS ProductCount, CountNumber +
(SELECT COUNT(*)
FROM [Order Details] AS [Order Details_1],Counter
WHERE [Order Details_1].OrderID = [Order Details].OrderID
AND [Order Details_1].ProductID < [Order Details].ProductID
AND Counter.CountNumber <= [Order Details_1].Quantity)
& " of " &
(SELECT COUNT(*)
FROM [Order Details] AS [Order Details_1],Counter
WHERE [Order Details_1].OrderID = [Order Details].OrderID
AND Counter.CountNumber <= [Order Details_1].Quantity)
AS ItemCount
FROM [Counter], Orders, [Order Details], Products
WHERE Products.ProductID = [Order Details].ProductID
AND Orders.OrderID = [Order Details].OrderID
AND Counter.CountNumber <= [Order Details].Quantity
ORDER BY Orders.OrderID, [Order Details].ProductID,
Counter.CountNumber;

The first subquery gets the running total of all items in each order by
adding the ContNumber value to the count of rows for items with lower product
ID values. The second subquery counts all rows for the each order where the
CountNumber value is less than or equal to the Quantity value to give the
total of all items.

Ken Sheridan
Stafford, England

Thanks so much for your help. When I copied your query I got the following
1 of 2 pillows
2 of 2 pillows
1 of 1 bedkskirts.... (this part works great)

but instead of
1 of 3 total items on the order
2 of 3 total items of the order
3 of 3 total items on the order....I got

1 of 2 (types of) items on the order
1 of 2 (types of) items on the order
2 of 2 (types of) items on the order

Perhaps I should be using Sum instead of Count for this second part of the
query?

Thanks for any further help you can provide.

I produce work tickets for our production people. For example if my order is
for 2 pillows and 1 bedskirts, I produce 3 work tickets. They say 1 or 2

[quoted text clipped - 17 lines]

Any Help is greatly appreciated.

 

[LeLe]

Well it looks like I spoke a little too soon . When I copied the SQL into
the Northwind database it worked perfectly; however when I tried to adapt it
to my production database, I could not get it to totally work right.

Here is the query adapted with my field names.

SELECT Orderheader.OrderNumber, Num.N & " of " & [NumberofPieces].QtyP AS
ProductCount, [N]+(SELECT COUNT(*) FROM [NumberofPieces] AS
[NumberofPieces_1],Num WHERE [NumberofPieces_1].OrderNumber =
[NumberofPieces].OrderNumber AND [NumberofPieces_1].Itemm <
[NumberofPieces].Itemm AND Num.N <= [NumberofPieces_1].QtyP) & " of " &
(SELECT SUM(QtyP) FROM [NumberofPieces] AS [NumberofPieces_1] WHERE
[NumberofPieces_1].OrderNumber = [NumberofPieces].OrderNumber) AS ItemCount,
Items.Item
FROM ((Num INNER JOIN (Orderheader INNER JOIN NumberofPieces ON
Orderheader.OrderNumber = NumberofPieces.OrderNumber) ON Num.N <=
NumberofPieces.QtyP) INNER JOIN NumberOfPieces AS NumberOfPieces_1 ON
Orderheader.OrderNumber = NumberOfPieces_1.OrderNumber) INNER JOIN Items ON
NumberOfPieces_1.Itemm = Items.Item
WHERE (((Orderheader.OrderNumber)=[forms]![orderEntry]![OrderNumber]) AND
((Num.N)<=[NumberofPieces].[QtyP]))
ORDER BY Orderheader.OrderNumber, NumberofPieces.Itemm, Num.N;

Here is what I am getting:

1 of 2 pillows
2 of 2 pillows
1 of 1 bedskirts...again, this part is great; however then the Item count
field produces...

1 of 3 items on the order
2 of 3 items on the order
1 of 3 items on the order.

Of course I am looking for:
1 of 3 items on the order
2 of 3 items on the order
3 of 3 items on the order.

Can you see where my mistake is? I just can't seem to figure it out.

Thanks again.

Glad to hear that it fits the bill. Thinking further, though, you were
probably right in your last post to suggest summing rather than counting to
get the total quantity. This should give the same results and might be
quicker:

SELECT Orders.OrderID, Products.ProductName,
Counter.CountNumber & " of " & [Order Details].Quantity
AS ProductCount, CountNumber +
(SELECT COUNT(*)
FROM [Order Details] AS [Order Details_1],Counter
WHERE [Order Details_1].OrderID = [Order Details].OrderID
AND [Order Details_1].ProductID < [Order Details].ProductID
AND Counter.CountNumber <= [Order Details_1].Quantity)
& " of " &
(SELECT SUM(Quantity)
FROM [Order Details] AS [Order Details_1]
WHERE [Order Details_1].OrderID = [Order Details].OrderID)
AS ItemCount
FROM [Counter], Orders, [Order Details], Products
WHERE Products.ProductID = [Order Details].ProductID
AND Orders.OrderID = [Order Details].OrderID
AND Counter.CountNumber <= [Order Details].Quantity
ORDER BY Orders.OrderID, [Order Details].ProductID,
Counter.CountNumber;

Ken Sheridan
Stafford, England

Thanks so much. It works great and now does just what I need. Super!!

I misunderstood your original post; I'd thought it was the types of items you
wanted as the second 'n of n'. The following should do what you want, but it

[quoted text clipped - 57 lines]

Any Help is greatly appreciated.

 

[LeLe]

Unfortunately, I am still getting the same (ALMOST perfect) result

1 of 2 pillows
2 of 2 pillows
1 of 1 bedskirts...again, this part is great; however then the Item count
field produces...

1 of 3 items on the order
2 of 3 items on the order
1 of 3 items on the order.

Of course I am looking for:
1 of 3 items on the order
2 of 3 items on the order
3 of 3 items on the order.

I even tried using a DSUM column which produced the correct number of items
but when I created a new item count colum by putting the Num.N of infront of
it, the exact same result as above occurred.

Can you explain in just a few words what the second instance of the
NumberofPieces table does? As you can probably tell, I am far from a
database professional, being totally self taught and learn a great deal from
these discussion groups.

As always, any ideas or suggestions are greatly appreciated. To be honest,
I am a bit discourged. Do you think there is an answer out there?

The subquery looks fine. It might be due to the two instances of the
NumberofPieces table in your outer query, and that the subquery is
referencing the wrong instance of this table when correlating with it on the
N and QtyP columns. You'll probably have noticed that mine doesn't include a
JOIN clause at all; the joins are by means of criteria in the WHERE clause.
This was in fact how joins were done prior to the issuing of the SQL 92
standard, which introduced the JOIN operator to allow for outer joins. Try
the following, in which I've tried to reproduce my query with your table and
column names:

SELECT Orderheader.OrderNumber,
Num.N & " of " & [NumberofPieces].QtyP
AS ProductCount, [N]+
(SELECT COUNT(*)
FROM [NumberofPieces] AS [NumberofPieces_1],Num
WHERE [NumberofPieces_1].OrderNumber
= [NumberofPieces].OrderNumber
AND [NumberofPieces_1].Itemm < [NumberofPieces].Itemm
AND Num.N <= [NumberofPieces_1].QtyP)
& " of " &
(SELECT SUM(QtyP)
FROM [NumberofPieces] AS [NumberofPieces_1]
WHERE [NumberofPieces_1].OrderNumber
= [NumberofPieces].OrderNumber)
AS ItemCount, Items.Item
FROM Num, Items, Orderheader, NumberOfPieces
WHERE Items.Item = NumberOfPieces_1.Itemm
AND Orderheader.OrderNumber = NumberOfPieces.OrderNumber
AND Orderheader.OrderNumber=[forms]![orderEntry]![OrderNumber]
AND Num.N<=NumberofPieces].QtyP
ORDER BY Orderheader.OrderNumber, NumberofPieces.Itemm, Num.N;

Ken Sheridan
Stafford, England

Well it looks like I spoke a little too soon . When I copied the SQL into
the Northwind database it worked perfectly; however when I tried to adapt it
to my production database, I could not get it to totally work right.

Here is the query adapted with my field names.

SELECT Orderheader.OrderNumber, Num.N & " of " & [NumberofPieces].QtyP AS
ProductCount, [N]+(SELECT COUNT(*) FROM [NumberofPieces] AS
[NumberofPieces_1],Num WHERE [NumberofPieces_1].OrderNumber =
[NumberofPieces].OrderNumber AND [NumberofPieces_1].Itemm <
[NumberofPieces].Itemm AND Num.N <= [NumberofPieces_1].QtyP) & " of " &
(SELECT SUM(QtyP) FROM [NumberofPieces] AS [NumberofPieces_1] WHERE
[NumberofPieces_1].OrderNumber = [NumberofPieces].OrderNumber) AS ItemCount,
Items.Item
FROM ((Num INNER JOIN (Orderheader INNER JOIN NumberofPieces ON
Orderheader.OrderNumber = NumberofPieces.OrderNumber) ON Num.N <=
NumberofPieces.QtyP) INNER JOIN NumberOfPieces AS NumberOfPieces_1 ON
Orderheader.OrderNumber = NumberOfPieces_1.OrderNumber) INNER JOIN Items ON
NumberOfPieces_1.Itemm = Items.Item
WHERE (((Orderheader.OrderNumber)=[forms]![orderEntry]![OrderNumber]) AND
((Num.N)<=[NumberofPieces].[QtyP]))
ORDER BY Orderheader.OrderNumber, NumberofPieces.Itemm, Num.N;

Here is what I am getting:

1 of 2 pillows
2 of 2 pillows
1 of 1 bedskirts...again, this part is great; however then the Item count
field produces...

1 of 3 items on the order
2 of 3 items on the order
1 of 3 items on the order.

Of course I am looking for:
1 of 3 items on the order
2 of 3 items on the order
3 of 3 items on the order.

Can you see where my mistake is? I just can't seem to figure it out.

Thanks again.

Glad to hear that it fits the bill. Thinking further, though, you were
probably right in your last post to suggest summing rather than counting to

[quoted text clipped - 31 lines]

Any Help is greatly appreciated.

 

[LeLe]

Unfortunatly, the problem remains. So as I stare and stare at the query I
notice the following:
THe "N" of portion of the Item count appears never to exceed the the highest
"N" portion of the product count. So if I have 3 different products on the
order each with a QtyP of 3 the product count is perfect but the Item count
comes out 1 of 9, 2 of 9, 3 of 9, 1 of 9, 2 of 9, 3 of 9, 1 of 9 2 of 9 , 3
of 9. I think we defined the "N" to be limited to the QtyP, is that why I am
getting the results I am getting?

Although this part of the equation probobly changes things, but I am not
sure how. [N]+(SELECT COUNT(*)

Thanks for your patience and help. We in Boston have plenty of Irish
priests to whom I could seek advise, perhaps I will start putting my
questions in the negative so I can get the positive response I am looking for.

Thanks again.

A former colleague of mine, who became a priest in the Church of Ireland
(which was a good career move as he's now Archbishop of Armagh!) use to say
that prayers are always answered, but the answer is generally No. I'm more
optimistic and think the answer is more often than not out there.

What I've done is create copies of the Northwind tables and changed the table
and column names to match yours. The query I'd posted in which I'd tried to
change the object names to yours exhibited an error, but after debugging it
the following produced the correct results:

SELECT Orderheader.OrderNumber,
Num.N & " of " & [NumberofPieces].QtyP
AS ProductCount, [N]+
(SELECT COUNT(*)
FROM [NumberofPieces] AS [NumberofPieces_1],Num
WHERE [NumberofPieces_1].OrderNumber
= [NumberofPieces].OrderNumber
AND [NumberofPieces_1].Itemm < [NumberofPieces].Itemm
AND Num.N <= [NumberofPieces_1].QtyP)
& " of " &
(SELECT SUM(QtyP)
FROM [NumberofPieces] AS [NumberofPieces_1]
WHERE [NumberofPieces_1].OrderNumber
= [NumberofPieces].OrderNumber)
AS ItemCount, Items.Item
FROM Num, Items, Orderheader, NumberOfPieces
WHERE Items.Item = NumberOfPieces.Itemm
AND Orderheader.OrderNumber = NumberOfPieces.OrderNumber
AND Num.N<=NumberofPieces.QtyP
ORDER BY Orderheader.OrderNumber, NumberofPieces.Itemm, Num.N;

Try that with your data. If you still don't get the correct results we need
to look at the structure of your data to see how it differs from that in
Northwind.

The one difference I noticed between yours and the Northwind tables is that
in your case the Items table's key seems to be the Item column, which I
assume is the name of the Item, whereas products table in Northwind has an
autonumber ProductID column as its key and a text ProductName column, which
is why my query returned the latter not the ProductID. Having the item name
as the key shouldn't make any difference, however.

Ken Sheridan
Stafford, England

Unfortunately, I am still getting the same (ALMOST perfect) result

1 of 2 pillows
2 of 2 pillows
1 of 1 bedskirts...again, this part is great; however then the Item count
field produces...

1 of 3 items on the order
2 of 3 items on the order
1 of 3 items on the order.

Of course I am looking for:
1 of 3 items on the order
2 of 3 items on the order
3 of 3 items on the order.

I even tried using a DSUM column which produced the correct number of items
but when I created a new item count colum by putting the Num.N of infront of
it, the exact same result as above occurred.

Can you explain in just a few words what the second instance of the
NumberofPieces table does? As you can probably tell, I am far from a
database professional, being totally self taught and learn a great deal from
these discussion groups.

As always, any ideas or suggestions are greatly appreciated. To be honest,
I am a bit discourged. Do you think there is an answer out there?

The subquery looks fine. It might be due to the two instances of the
NumberofPieces table in your outer query, and that the subquery is

[quoted text clipped - 79 lines]

Any Help is greatly appreciated.