Count Number Of Days That Had One Or More Records

[JC]

Creating a crosstab query that counts number of records for a specified
period of time. Displays each day of the month with the sum of records for
the month and has a column that adds the total. Need to be able to count the
number of days in which records are found in order to get an average of
records for the month? If there was one record or 100 record, in a day, that
day would count as 1. How?

 

[Tom Wickerath]

Hi JC,

Try creating a second crosstab query that does the counting that you need.
It should have the same criteria as your first crosstab query (remember to
define parameters for crosstab query criteria:
http://support.microsoft.com/?id=209778). For example, you could have a form
with two text boxes to define your starting and ending dates. Each crosstab
query would include criteria that looked to the same open form to get the
date values.

One you have the individual crosstab queries working, try adding each of
them as source tables to a new SELECT query. You'll need to have a unique key
field that you can join the two source crosstab queries on, to prevent a
cartesian product result.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ken Sheridan]

You don't say on what the crosstab query is grouped so for this example I'll
assume you have a table Orders, with columns OrderDate and CustomerID and its
grouped on CustomerID, i.e. it returns the number of orders per customer per
day of the month.

You can count the number of days on which a customer placed an order within
a specific month with a query like this:

SELECT CustomerID, COUNT(*) AS DayCount
FROM
(SELECT DISTINCT CustomerID,
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth
FROM Orders)
WHERE OrderYear = [Enter Year:]
AND OrderMonth = [Enter Month:]
GROUP BY CustomerID;

This can now be joined in a third query to the crosstab query on CustomerID.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

You don't say on what the crosstab query is grouped so for this example I'll
assume you have a table Orders, with columns OrderDate and CustomerID and its
grouped on CustomerID, i.e. it returns the number of orders per customer per
day of the month.

You can count the number of days on which a customer placed an order within
a specific month with a query like this:

Correction. That should have been:

SELECT CustomerID, COUNT(*) AS DayCount
FROM
(SELECT DISTINCT CustomerID, OrderDate
FROM Orders)
WHERE YEAR(OrderDate) = [Enter Year:]
AND MONTH(OrderDate) = [Enter Month:]
GROUP BY CustomerID;

The first one would have counted distinct month not days.

Ken Sheridan
Stafford, England