Count Function (conditional counts) in Reports

  • Thread starter Thread starter Melchiz
  • Start date Start date
M

Melchiz

Alright, so I'm trying to set up a query and corresponding report to provide
a census of sorts for customers. I am using Access 2003.

The query contains all the raw data needed for the report, which includes:
Customer ID
Date of Birth
Gender
Last Visit
Favorite Items
Preferred Payment Method
Account Manager

I want to take these values and get counts for them, with and without
conditions. So, for example, I want to get a count of the Customer IDs,
without any duplicate values (simply a total customer count). I also want to
get a count of "active" customers by counting the Last Visit values greater
than a given date. Another value that I need is the number of customers per
Account Manager.

Can anyone provide me with some information on how to accomplish these
things? I've tried messing with the Control Source for text boxes in the
report, but I either get the wrong values (duplicates are counted) or #Error.
 
You need several queries.
qryTotalCust_1 --
SELECT [Customer ID]
FROM Melchiz
GROUP BY [Customer ID];

qryTotalCust_2 --
SELECT Count([Customer ID]) AS CountCust
FROM qryTotalCust_1;

qryCustomer --
SELECT [Customer ID]
FROM Melchiz
WHERE [Last Visit] >=[Enter date]
GROUP BY [Customer ID];

qryCustPerMgr --
SELECT [Account Manager], Count([qryCustomer].[Customer ID]) AS CountCust
FROM Melchiz INNER JOIN qryCustomer ON Melchiz.[Customer
ID]=[qryCustomer].[Customer ID]
GROUP BY [Account Manager];
 
Looks promising. I will test it and get back to you.

Thanks!

KARL DEWEY said:
You need several queries.
qryTotalCust_1 --
SELECT [Customer ID]
FROM Melchiz
GROUP BY [Customer ID];

qryTotalCust_2 --
SELECT Count([Customer ID]) AS CountCust
FROM qryTotalCust_1;

qryCustomer --
SELECT [Customer ID]
FROM Melchiz
WHERE [Last Visit] >=[Enter date]
GROUP BY [Customer ID];

qryCustPerMgr --
SELECT [Account Manager], Count([qryCustomer].[Customer ID]) AS CountCust
FROM Melchiz INNER JOIN qryCustomer ON Melchiz.[Customer
ID]=[qryCustomer].[Customer ID]
GROUP BY [Account Manager];


--
Build a little, test a little.


Melchiz said:
Alright, so I'm trying to set up a query and corresponding report to provide
a census of sorts for customers. I am using Access 2003.

The query contains all the raw data needed for the report, which includes:
Customer ID
Date of Birth
Gender
Last Visit
Favorite Items
Preferred Payment Method
Account Manager

I want to take these values and get counts for them, with and without
conditions. So, for example, I want to get a count of the Customer IDs,
without any duplicate values (simply a total customer count). I also want to
get a count of "active" customers by counting the Last Visit values greater
than a given date. Another value that I need is the number of customers per
Account Manager.

Can anyone provide me with some information on how to accomplish these
things? I've tried messing with the Control Source for text boxes in the
report, but I either get the wrong values (duplicates are counted) or #Error.
Click to expand...
Click to expand...
 
Back
Top