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
