running sum

[Bobby L]

Is there a way to put a running sum in a query for a specific field (inv_cm#
is the field). There is no primary key in my table. The format for the
invoice number (inv_cm#) under the table is text. All the other data in the
table would be the same data for each line. This is because we sold more than
one of the same product and is referenced on a separate line. I only want
this to work on queries that I specify and not all of them (because some
invoice numbers don't need to have the letter after it). Hope you can
help me out on this.
Thanks

 

[Stefan Hoffmann]

Is there a way to put a running sum in a query for a specific field (inv_cm#
is the field). There is no primary key in my table.

As long as you have an order:

SELECT *,
DSum("fieldToSum", "yourTable", "orderField <= " & orderField)
FROM yourTable

You can of course use a sub select instead of the DSum():

SELECT *,
(
SELECT Sum(I.fieldToSum)
FROM yourTable I
WHERE I.orderField <= rderField
)
FROM yourTable O


mfG
--> stefan <--

 

[Bobby L]

The running sum needs to be A, B, C, D or 1, 2, 3, 4 for each of the invoice
numbers (inv_cm#) that are the same within the query.

 

[Stefan Hoffmann]

hi Bobby,

The running sum needs to be A, B, C, D or 1, 2, 3, 4 for each of the invoice
numbers (inv_cm#) that are the same within the query.

Sorry, I don't get it. What do you mean?



mfG
--> stefan <--

 

[Bobby L]

I have an invoice number (inv_cm#) field (text format in the table) in my
query and if we sold more than one for the same product, it will reference
the invoice number on each of the line items. All the data on each of the
line items are all of the same data. I need to put an A,B,C or 1,2,3 after
the invoice number that have the same invoice number.

 

[Stefan Hoffmann]

hi Bobby,

I have an invoice number (inv_cm#) field (text format in the table) in my
query and if we sold more than one for the same product, it will reference
the invoice number on each of the line items. All the data on each of the
line items are all of the same data. I need to put an A,B,C or 1,2,3 after
the invoice number that have the same invoice number.

I still don't get it, can you give me an example with data and the
desired result?


mfG
--> stefan <--

 

[Bobby L]

revised invoice Invoice Number Invoice Date Line Description
72703708A 72703708 2/2/2009 SPRINKLER-TEST & INSPECT
72703708B 72703708 2/2/2009 KITCHENHOOD -TEST/INSP ALL PART
72703708C 72703708 2/2/2009 KITCHENHOOD -TEST/INSP ALL PART
72703708D 72703708 2/2/2009 EX-TEST & INSPECT
72703708E 72703708 2/2/2009 EX-TEST & INSPECT
72703708F 72703708 2/2/2009 SPRINKLER-TEST & INSPECT
72703708G 72703708 2/2/2009 SPRINKLER-TEST & INSPECT
72703708H 72703708 2/2/2009 SPRINKLER-TEST & INSPECT
72703708I 72703708 2/2/2009 SPRINKLER-TEST & INSPECT
72703708J 72703708 2/2/2009 SPRINKLER-TEST & INSPECT
72703708K 72703708 2/2/2009 ALARM & DETECTION-TEST
72703708L 72703708 2/2/2009 ALARM & DETECTION-TEST
72703776A 72703776 2/2/2009 SPRINKLER-TEST & INSPECT
72703776B 72703776 2/2/2009 ALARM & DETECTION-TEST
72703776C 72703776 2/2/2009 SPRINKLER-TEST & INSPECT
72703776D 72703776 2/2/2009 SPRINKLER-TEST & INSPECT
72704378A 72704378 2/2/2009 SPRINKLER-TEST & INSPECT
72704378B 72704378 2/2/2009 EX-TEST & INSPECT
72704378C 72704378 2/2/2009 KITCHENHOOD -TEST/INSP
72704378D 72704378 2/2/2009 ALARM & DETECTION-TEST
72704378E 72704378 2/2/2009 SPRINKLER-TEST & INSPECT