Count versus Sum

[michael c]

I have a query that sums the values in a table record
source like so:

QTDDollarsShipped: Sum(Abs(([InvoiceDate]>=Forms!Dashboard!
FirstDayOfQuarter))*[DollarsShipped])

It does this for MTD (month-to-date) and YTD also. There's
two products that the query is grouped on, so it actually
shows two rows, one for product one and one for product
two.

I'm trying to use the same method to count the number of
orders for each product in each time period. I can do this
for the whole recordset by using the usual count(product),
but I'm having trouble counting by MTD, QTD, YTD.

I tried this:

MTDOrders: Count(Abs(([InvoiceDate]>=[Forms]![Dashboard]!
[FirstDayOfMonth]))*[ProductLevelFive])

I'm getting an error that says that the expressions is
typed incorrectly or is too complex. Any suggestions would
be a huge help. Thanks!!

 

[John Vinson]

I'm trying to use the same method to count the number of
orders for each product in each time period. I can do this
for the whole recordset by using the usual count(product),
but I'm having trouble counting by MTD, QTD, YTD.

I tried this:

MTDOrders: Count(Abs(([InvoiceDate]>=[Forms]![Dashboard]!
[FirstDayOfMonth]))*[ProductLevelFive])

I'd try an altogether different approach, using DCount:

MTDOrders: DCount("*", "[yourtablename]", "[InvoiceDate] >= #" &
DateSerial(Year(Date()), Month(Date()), 1))

I'm not sure how ProductLevelFive relates to a count of orders - the
DCount expression will count records in your table. For year to date
use DateSerial(Year(Date()), 1, 1); for QTD DateSerial(Year(Date()), 1
+ 3*((Month(Date()) - 1) \ 3), 1).

 

[michael c]

John,

The only thing I don't like about DCount and DSum is that
they seem to be a lot slower than using Sum/Abs in unison.
If I can't figure it out, I'll definately use DCount.
Thanks for the suggestion.

Mike

-----Original Message-----

I'm trying to use the same method to count the number of
orders for each product in each time period. I can do this
for the whole recordset by using the usual count (product),
but I'm having trouble counting by MTD, QTD, YTD.

I tried this:

MTDOrders: Count(Abs(([InvoiceDate]>=[Forms]![Dashboard]!
[FirstDayOfMonth]))*[ProductLevelFive])

I'd try an altogether different approach, using DCount:

MTDOrders: DCount("*", "[yourtablename]", "[InvoiceDate]
= #" &
DateSerial(Year(Date()), Month(Date()), 1))

I'm not sure how ProductLevelFive relates to a count of orders - the
DCount expression will count records in your table. For year to date
use DateSerial(Year(Date()), 1, 1); for QTD DateSerial (Year(Date()), 1
+ 3*((Month(Date()) - 1) \ 3), 1).


.