By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,412 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

How to Date Filter on a Group By Month query

P: n/a
All
I have an aggregate query using the function Month & Year on a
datereceived field ie:
TheYear: Year([DateReceived])
TheMonth: Month([DateReceived])
These are the group by fields to give me a Count on another field by
year & month

When I try to place a date filter 'Between x And y ' on an expression
field
CDate("1/" & TheMonth & "/" & TheYear) the filter dont work tho the
unfiltered expression returns the correct date

So do I Build an underlying base query with the date filters & sit my
aggregate query on top of that? & have to reference the sub parameters
in DAO

or create some sort of date & month table and link the aggregate query
to this

Never expected it to be so hard what am I doing wrong?
Thanks

Tony

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Check Access VB Help on the DateSerial() function.

--
Van T. Dinh

"Tony Miller" <tc****@xtra.co.nz> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
All
I have an aggregate query using the function Month & Year on a
datereceived field ie:
TheYear: Year([DateReceived])
TheMonth: Month([DateReceived])
These are the group by fields to give me a Count on another field by
year & month

When I try to place a date filter 'Between x And y ' on an expression
field
CDate("1/" & TheMonth & "/" & TheYear) the filter dont work tho the
unfiltered expression returns the correct date

So do I Build an underlying base query with the date filters & sit my
aggregate query on top of that? & have to reference the sub parameters
in DAO

or create some sort of date & month table and link the aggregate query
to this

Never expected it to be so hard what am I doing wrong?
Thanks

Tony

Nov 13 '05 #2

P: n/a
Thanks but it still dont like parameters

HardCoding the Date filter works:
In the QBE my column reads:
Firstofthemonth:
DateSerial([FirstInstanceA].[TheYear],[FirstInstanceA].[TheMonth],1)
Between #1/12/2004# And #30/04/2005#

This dont (in the QBE)
Between [EnterSTart] AND [EnterEnd]
No records returned

So I cant call it from code referencing the parameters
WHich leaves using dynamic strsql

And brings up another question Saved queries are faster than using sql
strings in code
Does this speed advantage also apply to saved queries with paramters
over dynamic Sql

Thanking You

Tony

Nov 13 '05 #3

P: n/a
1. I am not sure what you described???

The problem may be in how you referenced the Parameters or call the Query.

Post the relevant Table structure, what you want to get out of the Query /
SQL and code, the SQL String of the Query and your VBA code.

2. In theory, saved Queries are supposed to be faster because thet are
already compiled. In practice and my experience, the difference (if it
exists) is certainly not observable and I doubt anyone can see the
difference. Retrieving data is probably the most time consuming part of the
Query processing, IMHO.
--
Van T. Dinh
Tel. 0422-11 3708


"Tony Miller" <tc****@xtra.co.nz> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Thanks but it still dont like parameters

HardCoding the Date filter works:
In the QBE my column reads:
Firstofthemonth:
DateSerial([FirstInstanceA].[TheYear],[FirstInstanceA].[TheMonth],1)
Between #1/12/2004# And #30/04/2005#

This dont (in the QBE)
Between [EnterSTart] AND [EnterEnd]
No records returned

So I cant call it from code referencing the parameters
WHich leaves using dynamic strsql

And brings up another question Saved queries are faster than using sql
strings in code
Does this speed advantage also apply to saved queries with paramters
over dynamic Sql

Thanking You

Tony

Nov 13 '05 #4

P: n/a
tblCall

Call_ID - Autonumber
DateReceived - Date/Time

etc

Here you go

Final Query
SELECT FirstInstanceA.TheYear, FirstInstanceA.TheMonth,
[CountOfFollowUpRequired]/[CountOfCall_ID] AS Pct,
DateSerial([FirstInstanceA].[TheYear],[FirstInstanceA].[TheMonth],1) AS
Firstofthemonth
FROM FirstInstanceA INNER JOIN CountByMonth ON (FirstInstanceA.TheYear
= CountByMonth.TheYear) AND (FirstInstanceA.TheMonth =
CountByMonth.TheMonth)
WHERE
(((DateSerial([FirstInstanceA].[TheYear],[FirstInstanceA].[TheMonth],1))
Between [StartDate] And [EndDate]))
ORDER BY FirstInstanceA.TheYear, FirstInstanceA.TheMonth;

FirstInstanceA query:
SELECT Year([DateReceived]) AS TheYear, Month([DateReceived]) AS
TheMonth, Count(tblCall.FollowUpRequired) AS CountOfFollowUpRequired
FROM tblGroup INNER JOIN tblCall ON tblGroup.GroupID = tblCall.GroupID
WHERE (((tblCall.FollowUpRequired)=False))
GROUP BY Year([DateReceived]), Month([DateReceived])
ORDER BY Year([DateReceived]), Month([DateReceived]);

Query CountByMonth:
SELECT Year([DateReceived]) AS TheYear, Month([DateReceived]) AS
TheMonth, Format("1/" & Month([DateReceived]) & "/" &
Year([DateReceived]),"mmm yy") AS PeriodDesc, Count(tblCall.Call_ID) AS
CountOfCall_ID
FROM tblGroup INNER JOIN tblCall ON tblGroup.GroupID = tblCall.GroupID
GROUP BY Year([DateReceived]), Month([DateReceived]), Format("1/" &
Month([DateReceived]) & "/" & Year([DateReceived]),"mmm yy")
ORDER BY Year([DateReceived]), Month([DateReceived]);

Have yet to call it in VBA until I get the QBE going using the date
parameters
but when I add dates to the input boxes it gets nought I tried MM/DD/YY
& DD/MM/YY
What I want is the counts of the calls received in the months specified
by a date filter

Thanking you

Nov 13 '05 #5

P: n/a
The set of 3 Queries sounds very inefficient to me.

1. You use ORDER BY in the component Queries which will be superseded by
the ORDER BY in the final Query. I am not sure Access / JET will be smart
enough to ignore the ORDER BY in the component Queries or the sorting will
be done 3 times while once would do.

2. You included tblGroup in the FROM clause of both component Queries but
you did not refer to the tblGroup or its Fields anywhere else in the
component Queries. Is there any purpose of including tblGroup.

3. The number of calls to VBA function should be as small as possible. In
your case, you need to call the functions Year() and Month() in both
component Queries. (See also point 6)

4. There is a shortcut to count the number of False values in Query.

5. In fact, you need only 1 Query / SQL String not 3 Queries / SQL Strings.

I tested this single Query / SQL with a Test Table4 (3 Fields: ID -
AutoNumber, Date1 - DateTime, Bool1 - Boolean) which I guess equivalent to
your tblCall stripped to essential structure (for the Query) and it works
perfectly:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT Year(T.[Date1]) AS TheYear, Month(T.[Date1]) AS TheMonth,
Sum(T.[Bool1]+1)/Count(*) AS PCT
FROM Table4 AS T
WHERE (((T.[Date1]) Between [StartDate] And [EndDate]))
GROUP BY Year([Date1]), Month([Date1])
ORDER BY Year(T.[Date1]), Month(T.[Date1]);
Translating to your Table (ignoring tblGroup), I guess it should be
something like:

****Untested****
PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT Year(T.[DateReceived]) AS TheYear, Month(T.[DateReceived]) AS
TheMonth,
Sum(T.[FollowUpRequired]+1)/Count(*) AS PCT
FROM tblCall AS T
WHERE (((T.[DateReceived]) Between [StartDate] And [EndDate]))
GROUP BY Year([DateReceived]), Month([DateReceived]);
********

6. I left out the calculated Field "TheFirstOfMonth" since this doesn't add
to the information content of the Query and this is much more efficient to
re-construct in the Report / Form from TheYear and TheMonth. However, this
can also be included in the Query. (see point 3 for the reason).

--
HTH
Van T. Dinh


"Tony Miller" <tc****@xtra.co.nz> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
tblCall

Call_ID - Autonumber
DateReceived - Date/Time

etc

Here you go

Final Query
SELECT FirstInstanceA.TheYear, FirstInstanceA.TheMonth,
[CountOfFollowUpRequired]/[CountOfCall_ID] AS Pct,
DateSerial([FirstInstanceA].[TheYear],[FirstInstanceA].[TheMonth],1) AS
Firstofthemonth
FROM FirstInstanceA INNER JOIN CountByMonth ON (FirstInstanceA.TheYear
= CountByMonth.TheYear) AND (FirstInstanceA.TheMonth =
CountByMonth.TheMonth)
WHERE
(((DateSerial([FirstInstanceA].[TheYear],[FirstInstanceA].[TheMonth],1))
Between [StartDate] And [EndDate]))
ORDER BY FirstInstanceA.TheYear, FirstInstanceA.TheMonth;

FirstInstanceA query:
SELECT Year([DateReceived]) AS TheYear, Month([DateReceived]) AS
TheMonth, Count(tblCall.FollowUpRequired) AS CountOfFollowUpRequired
FROM tblGroup INNER JOIN tblCall ON tblGroup.GroupID = tblCall.GroupID
WHERE (((tblCall.FollowUpRequired)=False))
GROUP BY Year([DateReceived]), Month([DateReceived])
ORDER BY Year([DateReceived]), Month([DateReceived]);

Query CountByMonth:
SELECT Year([DateReceived]) AS TheYear, Month([DateReceived]) AS
TheMonth, Format("1/" & Month([DateReceived]) & "/" &
Year([DateReceived]),"mmm yy") AS PeriodDesc, Count(tblCall.Call_ID) AS
CountOfCall_ID
FROM tblGroup INNER JOIN tblCall ON tblGroup.GroupID = tblCall.GroupID
GROUP BY Year([DateReceived]), Month([DateReceived]), Format("1/" &
Month([DateReceived]) & "/" & Year([DateReceived]),"mmm yy")
ORDER BY Year([DateReceived]), Month([DateReceived]);

Have yet to call it in VBA until I get the QBE going using the date
parameters
but when I add dates to the input boxes it gets nought I tried MM/DD/YY
& DD/MM/YY
What I want is the counts of the calls received in the months specified
by a date filter

Thanking you

Nov 13 '05 #6

P: n/a
Thanks for all your efforts.The base queries were used in other reports
but still you're 1 query works great Love beauty in the line
Sum(T.[FollowUpRequired]+1)/Co*unt(*) AS PCT

Adding 1 to a boolean 0 to get 1 & Suming it ( & no inverse required)
I didnt know you could use Count(*) as a denominator here

I see untold applications for this 1 query structure

I am in awe & shall pin this to the wall

Many Thanks

Tony

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.