Running Count? Report Chart 1

[ChrisCalvert]

Hello.
What I have are a list of customers that have signed up. I have a chart that shows how many signed up per day. However, what I also want is a chart that lists the total number of records each day. I can't really figure that second one out. Could someone assist? It would be much appreicated.

-Chris

 

[GingerR]

in you query that the report is based on, use DCOUNT to count the number of customers signed up prior to or including the current date.

so say your table has a field SignUpDate.
in a regular TOTALS query, you'd have i.e.

SignUpDate CustomerID
{GROUP ON} {COUNT}

so this shows what you already have, Number of Customers signed up each day.

if you put another field in the query, and put in this formula

dcount(&quot;CustomerID&quot;,&quot;CustomerTable&quot;,&quot;SignUpDate <= #&quot; & SignUpDate & &quot;#&quot

then you will get what you want
substitute a unique ID field for 'CustomerID', and the name of your table for 'CustomerTable', and whatever your date field is called for 'SignUpDate'.

it probably takes a lot of time to calculate, so you might want to make a table instead and base your chart off of the table. OR what i do is make the table, but each time the user clicks a button to see the report, i first run a query to delete the current records, then append new records.

so say you make a table that has three fields (called tblTABLE):

SignUpDate CustomerCountPerDay CustomerCountTotal
11/1/03 15 15
11/2/03 13 28
11/3/03 10 38

what i would do is this:
make a delete query that deletes all records.
then write an append query that adds in just the fields SignUpDate and CustomerCountPerDay (what you already have).

then make an UPDATE query has this:

FIELD: CustomerCountTotal
UPDATE TO: dsum(&quot;CustomerCountPerDay&quot;,&quot;tblTABLE&quot;,&quot;SignUpDate <= #&quot; & SignUpDate & &quot;#&quot

then base your chart off of this table. each time the user clicks a button to see the report, the three queries run then the report/chart opens. ok?

 

[ChrisCalvert]

Thanks for the help. This seems to work well, but there is one problem. The SignUpDate is really a web form Timestamp. So it is grouping on each datetime value. What would be the best way to get it to just look at the date and ignore the time?

 

[GingerR]

Date(TimeStamp)

&quot;DATE&quot; is a function that just gets the DATE of the field. so group on that.

 

[ChrisCalvert]

Sorry to keep bugging you but, here is what I have now.
A TOTALS Query.

My Items are listed below like this
Value in Field, Value in Total


ID, COUNT

Date(Timestamp), GROUP BY

[TotalCount]: DCount(&quot;ID&quot;,&quot;Results&quot;,&quot;Timestamp <= #&quot; & [Timestamp] & &quot;#&quot, GROUP BY


When I try to tab from entering Date(Timestamp) it tells me not enough arguments. I looked at the function in help and it seems like it expects three (Year,Month, Date) arguments.

 

[GingerR]

sorry
try this instead of the date thing:

format(Timestamp,&quot;mm/dd/yy&quot

g

 

[GingerR]

btw you're not bugging me at all