Running Count

[michael c]

I have a query, qryShipped, that has a record source
called qryOrders with the following fields:

AccountNumber InvoiceDate InvoiceAmount

I'm hoping to add an additional column that will keep a
running count of which order number each account is on
like so:

AccountNumber InvoiceDate Order InvoiceAmount
12345 1/1/03 1 $534
12345 1/5/03 2 $234
12345 1/9/03 3 $566
10000 1/3/03 1 $100

This will help me figure out how much an average first
order is, an average second order etc. Eventually, I'll
try to figure out how much time elapses between the first
order and the second order (i.e. how much time it takes a
typical account to try the product for the first time and
decide if they like it) and then how much time elapses
between all orders for each account...but for now I'm just
trying to figure out how to number my orders. Any
suggestions would be great! Thanks!

 

[Michel Walsh]

Hi,


a computed column:

DSum("InvoiceAmount", "TableNameHere", "AccountNumber=" & AccountNumber & "
AND [Order] <=" & Order )


So, for the third line, the third argument is

"AccountNumber=" & 12345 & " AND [Order] <= " & 3

or, after concatenation:

"AccountNumber = 12345 AND [Order] <= 3"

The sum will then occur over the records satisfying that relation, ie, for
the first three records, as we desired.




To get the difference in date between two orders? Bring the table twice,
one will get an _1 append to the end of its name. Join the tables through
their AccountNumber fields. Add, to TableName_1.[Order] the criteria

= [TableName].[Order] +1

Then, the computed column

DiffInTime: tableName_1.InvoiceDate - tableName.InvoiceDate


does the trick.


Hoping it may help,
Vanderghast, Access MVP

 

[michael c]

Michel,

Thanks for the reply. I'm not really trying to sum the
amount of the order at this point, although that was good
to know. I'm actually trying to just keep a running total
of how many times an account has ordered.

"Order" would be the calculated column for me and instead
of using Sum, I think I need to use Count right? Would it
looks something like this?

Order: DCount
("[AccountNumber]", "[qryShipped]", "[AccountNumber] =" &
[AccountNumber] & "")

This doesn't work for me but I think it might help you
understand what I'm trying to do. I often have problems
constructing that third part of the argument, the part
that goes through and keeps a running count of how many
times the AccountNumber has appeared. Any thoughts? Thanks
again!!

-----Original Message-----
Hi,


a computed column:

DSum("InvoiceAmount", "TableNameHere", "AccountNumber=" & AccountNumber & "
AND [Order] <=" & Order )


So, for the third line, the third argument is

"AccountNumber=" & 12345 & " AND [Order] <= " & 3

or, after concatenation:

"AccountNumber = 12345 AND [Order] <= 3"

The sum will then occur over the records satisfying that relation, ie, for
the first three records, as we desired.




To get the difference in date between two orders? Bring the table twice,
one will get an _1 append to the end of its name. Join the tables through
their AccountNumber fields. Add, to TableName_1. [Order] the criteria

= [TableName].[Order] +1

Then, the computed column

DiffInTime: tableName_1.InvoiceDate - tableName.InvoiceDate


does the trick.


Hoping it may help,
Vanderghast, Access MVP

I have a query, qryShipped, that has a record source
called qryOrders with the following fields:

AccountNumber InvoiceDate InvoiceAmount

I'm hoping to add an additional column that will keep a
running count of which order number each account is on
like so:

AccountNumber InvoiceDate Order InvoiceAmount
12345 1/1/03 1 $534
12345 1/5/03 2 $234
12345 1/9/03 3 $566
10000 1/3/03 1 $100

This will help me figure out how much an average first
order is, an average second order etc. Eventually, I'll
try to figure out how much time elapses between the first
order and the second order (i.e. how much time it takes a
typical account to try the product for the first time and
decide if they like it) and then how much time elapses
between all orders for each account...but for now I'm just
trying to figure out how to number my orders. Any
suggestions would be great! Thanks!

.

 

[Michel Walsh]

Hi,



Close. You still need some sort of <= comparison to "order" the stuff.


Note that ORDER is a reserved word, as in ORDER BY, so, I would use RANK,
instead:


Rank: DCount("*", "qryShipped", "AccountNumber=" & AccountNumber & " AND
InvoiceDate <=" & Format( InvoiceDate, "\#mm-dd-yyyy\#"))



Hoping it may help,
Vanderghast, Access MVP

Michel,

Thanks for the reply. I'm not really trying to sum the
amount of the order at this point, although that was good
to know. I'm actually trying to just keep a running total
of how many times an account has ordered.

"Order" would be the calculated column for me and instead
of using Sum, I think I need to use Count right? Would it
looks something like this?

Order: DCount
("[AccountNumber]", "[qryShipped]", "[AccountNumber] =" &
[AccountNumber] & "")

This doesn't work for me but I think it might help you
understand what I'm trying to do. I often have problems
constructing that third part of the argument, the part
that goes through and keeps a running count of how many
times the AccountNumber has appeared. Any thoughts? Thanks
again!!

-----Original Message-----
Hi,


a computed column:

DSum("InvoiceAmount", "TableNameHere", "AccountNumber=" & AccountNumber & "
AND [Order] <=" & Order )


So, for the third line, the third argument is

"AccountNumber=" & 12345 & " AND [Order] <= " & 3

or, after concatenation:

"AccountNumber = 12345 AND [Order] <= 3"

The sum will then occur over the records satisfying that relation, ie, for
the first three records, as we desired.




To get the difference in date between two orders? Bring the table twice,
one will get an _1 append to the end of its name. Join the tables through
their AccountNumber fields. Add, to TableName_1. [Order] the criteria

= [TableName].[Order] +1

Then, the computed column

DiffInTime: tableName_1.InvoiceDate - tableName.InvoiceDate


does the trick.


Hoping it may help,
Vanderghast, Access MVP

I have a query, qryShipped, that has a record source
called qryOrders with the following fields:

AccountNumber InvoiceDate InvoiceAmount

I'm hoping to add an additional column that will keep a
running count of which order number each account is on
like so:

AccountNumber InvoiceDate Order InvoiceAmount
12345 1/1/03 1 $534
12345 1/5/03 2 $234
12345 1/9/03 3 $566
10000 1/3/03 1 $100

This will help me figure out how much an average first
order is, an average second order etc. Eventually, I'll
try to figure out how much time elapses between the first
order and the second order (i.e. how much time it takes a
typical account to try the product for the first time and
decide if they like it) and then how much time elapses
between all orders for each account...but for now I'm just
trying to figure out how to number my orders. Any
suggestions would be great! Thanks!

.