Cumulative Query

[Guest]

Hi.

I have a query that produces a Count of Items, by Customer, by Week.

I need a report to show the cumulative total of customers over a specific
time period:

W/E Customer count
02/06/05 - 1
09/06/05 - 3
16/05/05 - 7

The count should only show Unique customers.

Im not sure if ive explained very well, but if anyone has any suggestions,
please let me know.

Cheers,
Steve.

 

[Allen Browne]

Presumably you have the report giving the correct total for each week, and
the problem is that you can't just sum for the report total because the same
customer may have come back during those weeks.

You will probably need a separate expression in the Control Source of a text
box in the Report Footer section to get the total. Unfortunately the
DCount() build into Access does not handle counting distinct records, but
there is an extended one named ECount() you can copy from here:
http://allenbrowne.com/ser-66.html

The Control Source would be something like this (one line):
=ECount("CustomerID", "Orders", "OrderDate Between " &
Format([StartDate], "\#mm\/dd\/yyyy\#") & " And " &
Format([EndDate], "\#mm\/dd\/yyyy\#"), True)

That's assuming the report's query has Date/Time parameters named StartDate
and EndDate. If the query is actually reading values from a form, you would
replace [StartDate] in the expression above with a full reference to the
text box on the form, e.g.:
[Forms].[Form1].[txtStartDate]

The ECount() expression is probably easier than creating a subreport just to
get the total.

 

[Guest]

Thanks for the reply.

I am using a query, based on another query to count the count of customers.

SELECT DateAdd("d",-Weekday([Received])+6,[Received]) AS we, tblAll.SSC,
Count(tblAll.MPR) AS CountOfMPR
FROM tblAll
GROUP BY DateAdd("d",-Weekday([Received])+6,[Received]), tblAll.SSC;

This brings me back the count of customers in each week.

My next idea was to do a count of customers by use of another query.

SELECT qryAQ065_Count_SSC_by_Wk.we, Count(qryAQ065_Count_SSC_by_Wk.SSC) AS
CountOfSSC
FROM qryAQ065_Count_SSC_by_Wk
GROUP BY qryAQ065_Count_SSC_by_Wk.we;

How would I incorporate the ECount function into this query to give me the
correct results?

Many thanks,
Steve.

Presumably you have the report giving the correct total for each week, and
the problem is that you can't just sum for the report total because the same
customer may have come back during those weeks.

You will probably need a separate expression in the Control Source of a text
box in the Report Footer section to get the total. Unfortunately the
DCount() build into Access does not handle counting distinct records, but
there is an extended one named ECount() you can copy from here:
http://allenbrowne.com/ser-66.html

The Control Source would be something like this (one line):
=ECount("CustomerID", "Orders", "OrderDate Between " &
Format([StartDate], "\#mm\/dd\/yyyy\#") & " And " &
Format([EndDate], "\#mm\/dd\/yyyy\#"), True)

That's assuming the report's query has Date/Time parameters named StartDate
and EndDate. If the query is actually reading values from a form, you would
replace [StartDate] in the expression above with a full reference to the
text box on the form, e.g.:
[Forms].[Form1].[txtStartDate]

The ECount() expression is probably easier than creating a subreport just to
get the total.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi.

I have a query that produces a Count of Items, by Customer, by Week.

I need a report to show the cumulative total of customers over a specific
time period:

W/E Customer count
02/06/05 - 1
09/06/05 - 3
16/05/05 - 7

The count should only show Unique customers.

Im not sure if ive explained very well, but if anyone has any suggestions,
please let me know.

Cheers,
Steve.

 

[Allen Browne]

There doesn't seem to be any limiting dates in those queries, so presumably
you just want the total of all distinct customers that have ever ordered
anything from you.

That would be just:
=ECount("CustomerID", "Orders", ,True)
substituting the name of the customer field for CustomerID, and the name of
your table for Orders.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply.

I am using a query, based on another query to count the count of
customers.

SELECT DateAdd("d",-Weekday([Received])+6,[Received]) AS we, tblAll.SSC,
Count(tblAll.MPR) AS CountOfMPR
FROM tblAll
GROUP BY DateAdd("d",-Weekday([Received])+6,[Received]), tblAll.SSC;

This brings me back the count of customers in each week.

My next idea was to do a count of customers by use of another query.

SELECT qryAQ065_Count_SSC_by_Wk.we, Count(qryAQ065_Count_SSC_by_Wk.SSC) AS
CountOfSSC
FROM qryAQ065_Count_SSC_by_Wk
GROUP BY qryAQ065_Count_SSC_by_Wk.we;

How would I incorporate the ECount function into this query to give me the
correct results?

Many thanks,
Steve.

Presumably you have the report giving the correct total for each week,
and
the problem is that you can't just sum for the report total because the
same
customer may have come back during those weeks.

You will probably need a separate expression in the Control Source of a
text
box in the Report Footer section to get the total. Unfortunately the
DCount() build into Access does not handle counting distinct records, but
there is an extended one named ECount() you can copy from here:
http://allenbrowne.com/ser-66.html

The Control Source would be something like this (one line):
=ECount("CustomerID", "Orders", "OrderDate Between " &
Format([StartDate], "\#mm\/dd\/yyyy\#") & " And " &
Format([EndDate], "\#mm\/dd\/yyyy\#"), True)

That's assuming the report's query has Date/Time parameters named
StartDate
and EndDate. If the query is actually reading values from a form, you
would
replace [StartDate] in the expression above with a full reference to the
text box on the form, e.g.:
[Forms].[Form1].[txtStartDate]

The ECount() expression is probably easier than creating a subreport just
to
get the total.

Hi.

I have a query that produces a Count of Items, by Customer, by Week.

I need a report to show the cumulative total of customers over a
specific
time period:

W/E Customer count
02/06/05 - 1
09/06/05 - 3
16/05/05 - 7

The count should only show Unique customers.

Im not sure if ive explained very well, but if anyone has any
suggestions,
please let me know.

Cheers,
Steve.