2

I am working on a conversion script in Access VBA where i need a query to change the column requested quantity to contain the sum of the delivered quantity per order line. Right now it looks like this:

ordernumber  orderlinenumber  requestedquantity  deliveredquantity
123456       1000             1                  1
123456       1001             2                  2
123456       2000             50                 50
123456       3000             10                 10

So for order number 123456 line number 1000 & 1001 requested quantity should become 3.

Basically what i need is an update query that goes through order line 1000-1999, calculates the sum of the delivered quantity and sets it for the requested quantity. I tried a few different constructions using the SUM clause but i can not seem to find a way to loop through the order lines.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Ultratapir
  • 23
  • 4
  • What about the other order lines? What is special about 1000/1001? – Gordon Linoff Dec 03 '20 at 19:43
  • @GordonLinoff Basically order line 1000 & 1001 are the same product in an order but have been delivered seperately. I can not tell you why is it constructed like that, i didn't come up with the system, just trying to convert it from excel to access. – Ultratapir Dec 03 '20 at 19:53
  • So you want to group by thousands or is there something special about 1000 and 1001? – JeffUK Dec 03 '20 at 20:29
  • Does this answer your question? [In SQL, how can you "group by" in ranges?](https://stackoverflow.com/questions/232387/in-sql-how-can-you-group-by-in-ranges) – JeffUK Dec 03 '20 at 20:30
  • @JeffUK Exactly, i need to be able to group by the thousands in order line and find the sum of delivered quantity. It's not just 1000 & 1001, it should also be possible say you have order 121212 with line order 3000, 3001 and 3002. – Ultratapir Dec 03 '20 at 20:33
  • @JeffUK I found that one, but that doesn't really work because it needs to calculate the sum of orderline 1000-1999, 2000-2999, etc. of order 123456 but also 1000-1999 of any other order number. – Ultratapir Dec 03 '20 at 20:40

1 Answers1

2

Hmmm . . . You can use aggregation

select ordernumber, min(orderlinenumber), sum(requestedquantity), sum(deliveredquantity)
from t
group by ordernumber, orderlinenumber \ 1000

The backwards slant is MS-Access-speak for integer division.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786