Count in Report (Access 2003)

[mattlau]

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

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.

 

[genomon]

DCount wont work with DISTINCT, so that's out. You could either do recordset manipulation in VBA with SQL in the reports detail print or format events, or create a query and use it as the recordsource of the textbox (you can also type in a SQL statement as a recordsource as well). Some SQL statements to check out, if you're unfamiliar: SELECT, COUNT, DISTINCT, WHERE clause.

Cogito eggo sum – I think, therefore I am a waffle.

 

[lameid]

Why do you have duplicates of Customer ID in this query?

If you did not you could group by Account manager and simply count customer ID in an Account Manger section (header or footer) and in a report section.

 

[dhookom]

It would help if you had described your relationships a little. However, if you want to count the number of records in your report where the [Last Visit] is less than 60 days ago, you could use a text box with a control source like:

=Sum(Abs(DateDiff("d",[Last Visit],date())<60))

If you want the number of customers per account manager, you can group by account manager and use a text box in the group header or footer with a control source of:

=Count(*)

Duane
Hook'D on Access
MS Access MVP

 

[mattlau]

Thanks for the tips. Let me provide more information on my data.

There are multiple tables from which the data above is queried from:

Customer Data: matches Customer ID to Date of Birth, Gender, Last Visit, Preferred Payment Method*, and Account Manager*

Shopping Data: matches Customer ID to Favorite Items* (each Customer ID may have more than 1 value in Favorite Items)

*relies on a definitions table (ex: in the Customer Data table, Account Manager is a #, which is referenced to a name in the Account Manager List table)

So basically, each customer has an ID, a Date of Birth, a Last Visit date, a Preferred Payment Method, an Account Manager, and multiple Favorite Items. I want to count the total number of unique Customer IDs, the number of customers over age 60 (DOB > 8/1/1949), the number of customers whose Last Visit is > 8/1/2008, the number of unique Customer IDs for each Account Manager (we have 5 managers), and the number of unique Customer IDs per each Favorite Item.

I have considered creating a separate query for each value, but is that wasteful? If I go that route, that means that every Favorite Item would require a different query (there are dozens of Favorite Items). Even if I do that, is the only way to get counts in a single report for different queries via group headers/footers and =count(*)? I don't even know what the significance of group headers and footers are (can't find any clear documentation on what they do and why).

Thanks!

 

[dhookom]

I would pull some detail information into a subreport. This would allow you to total or count records in your main report without duplication.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

I agree with Duane...

But here is some clarification in case it is not obvious...

The main report is based on Customer data so that the grouping and counts I mentioned are valid.

Secondly, the detailed Favorite information is displayed via a subreport.

 

[mattlau]

Rather than go with subreports, I broke things up into multiple queries. With some SQL code (SELECT COUNT(X)) and the Access query builder, I was able to put together everything I needed.

Thanks for the tips guys!