Problem: Parameter query with Count

[stevekos07]

I have a frustrating problem. I have a table of clients (ClientID) and dates they are called (CallDate). The table is an Update table populated from daily backups. So I have a list of every client and every date that each has received a call.

I want to generate a report that summarises how many calls each client received throughout the month, each month.

I have created the query with all of the data, and sorting by ClientID I can show a count of dates called for each client. However if I attempt to filter the date range with a Between...And... parameter I get a "too complicated" error. It is obviously the parameter which messes with the Count sort on the CallDate field.

This runs ok:

SELECT tblDailyStatsBackup.MavisID, tblDailyStatsBackup.ClientID, Count(tblDailyStatsBackup.CallDate) AS CountOfCallDate
FROM tblDailyStatsBackup
GROUP BY tblDailyStatsBackup.MavisID, tblDailyStatsBackup.ClientID

But this doesn't:

SELECT tblDailyStatsBackup.MavisID, tblDailyStatsBackup.ClientID, Count(tblDailyStatsBackup.CallDate) AS CountOfCallDate
FROM tblDailyStatsBackup
GROUP BY tblDailyStatsBackup.MavisID, tblDailyStatsBackup.ClientID
HAVING (((Count(tblDailyStatsBackup.CallDate)) Between [Enter Start Date] And [Enter End Date]));

Any thoughts on how I can deal with this?

 

[JHB]

Try the below:

HAVING CallDate Between [Enter Start Date] And [Enter End Date];

 

[stevekos07]

Try the below:

HAVING CallDate Between [Enter Start Date] And [Enter End Date];

Unfortunately that didn't work.

In it's most basic form this is what I want:

SELECT tblDailyStatsBackup.ClientID, tblDailyStatsBackup.MavisID, Count(tblDailyStatsBackup.CallDate) AS CountOfCallDate
FROM tblDailyStatsBackup
GROUP BY tblDailyStatsBackup.ClientID, tblDailyStatsBackup.MavisID, tblDailyStatsBackup.CallToday
HAVING (((tblDailyStatsBackup.CallToday)="Yes"));

The "Yes" is simply to differentiate client who did get a call that day. (We call most of our clients every day).

But filtered by a specific month or a date range.

The expression Between [Start Date] And [End Date] seems to me to be the most flexible way to do that. And that works by itself, but not if I try to group by count for each client.

Can anyone think of a way to produce a summary report of the number of times an event based on date occurs within a given date range?

 

[JHB]

..
Can anyone think of a way to produce a summary report of the number of times an event based on date occurs within a given date range?

I think so, if you provide some sample data + shows how you want the result.

 

[Galaxiom]

HAVING conditions are applied to the result fields.

If you want to limit the input records use a WHERE condition.

SELECT tblDailyStatsBackup.MavisID, tblDailyStatsBackup.ClientID, Count(tblDailyStatsBackup.CallDate) AS CountOfCallDate
FROM tblDailyStatsBackup
WHERE CallDate Between [Enter Start Date] And [Enter End Date]
GROUP BY tblDailyStatsBackup.MavisID, tblDailyStatsBackup.ClientID;

 

[arnelgp]

SELECT tblDailyStatsBackup.ClientID, tblDailyStatsBackup.MavisID, Count(tblDailyStatsBackup.CallDate) AS CountOfCallDate
FROM tblDailyStatsBackup
WHERE CallDate Between [Enter Start Date] And [Enter End Date]
GROUP BY tblDailyStatsBackup.ClientID, tblDailyStatsBackup.MavisID, tblDailyStatsBackup.CallToday
HAVING (((tblDailyStatsBackup.CallToday)="Yes"));

 

[stevekos07]

SELECT tblDailyStatsBackup.ClientID, tblDailyStatsBackup.MavisID, Count(tblDailyStatsBackup.CallDate) AS CountOfCallDate
FROM tblDailyStatsBackup
WHERE CallDate Between [Enter Start Date] And [Enter End Date]
GROUP BY tblDailyStatsBackup.ClientID, tblDailyStatsBackup.MavisID, tblDailyStatsBackup.CallToday
HAVING (((tblDailyStatsBackup.CallToday)="Yes"));

That did the trick arnelgp! Thank you.

Interesting how in design mode the SQL doesn't compose correctly some times. I am still learning the finer points of SQL so this is a good lesson for me.

Cheers,
Steve.

 

[Galaxiom]

Interesting how in design mode the SQL doesn't compose correctly some times.

Other than adding copious unnecessary bracketing and scrambling the layout I have never had any problems with inconsistency between the design view and sql view in the Access query designer.

I suspect you just didn't realise that you needed to add the CallDate field twice. Once for the COUNT and again for the WHERE.

 

[stevekos07]

Other than adding copious unnecessary bracketing and scrambling the layout I have never had any problems with inconsistency between the design view and sql view in the Access query designer.

I suspect you just didn't realise that you needed to add the CallDate field twice. Once for the COUNT and again for the WHERE.

Yes you are right, I didn't realise that.