473,722 Members | 2,216 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to Date Filter on a Group By Month query

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
6 17920
Check Access VB Help on the DateSerial() function.

--
Van T. Dinh

"Tony Miller" <tc****@xtra.co .nz> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.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
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
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.goo glegroups.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
tblCall

Call_ID - Autonumber
DateReceived - Date/Time

etc

Here you go

Final Query
SELECT FirstInstanceA. TheYear, FirstInstanceA. TheMonth,
[CountOfFollowUp Required]/[CountOfCall_ID] AS Pct,
DateSerial([FirstInstanceA].[TheYear],[FirstInstanceA].[TheMonth],1) AS
Firstofthemonth
FROM FirstInstanceA INNER JOIN CountByMonth ON (FirstInstanceA .TheYear
= CountByMonth.Th eYear) AND (FirstInstanceA .TheMonth =
CountByMonth.Th eMonth)
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.F ollowUpRequired ) AS CountOfFollowUp Required
FROM tblGroup INNER JOIN tblCall ON tblGroup.GroupI D = tblCall.GroupID
WHERE (((tblCall.Foll owUpRequired)=F alse))
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.C all_ID) AS
CountOfCall_ID
FROM tblGroup INNER JOIN tblCall ON tblGroup.GroupI D = 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
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.[FollowUpRequire d]+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 "TheFirstOfMont h" 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******** *************@g 43g2000cwa.goog legroups.com...
tblCall

Call_ID - Autonumber
DateReceived - Date/Time

etc

Here you go

Final Query
SELECT FirstInstanceA. TheYear, FirstInstanceA. TheMonth,
[CountOfFollowUp Required]/[CountOfCall_ID] AS Pct,
DateSerial([FirstInstanceA].[TheYear],[FirstInstanceA].[TheMonth],1) AS
Firstofthemonth
FROM FirstInstanceA INNER JOIN CountByMonth ON (FirstInstanceA .TheYear
= CountByMonth.Th eYear) AND (FirstInstanceA .TheMonth =
CountByMonth.Th eMonth)
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.F ollowUpRequired ) AS CountOfFollowUp Required
FROM tblGroup INNER JOIN tblCall ON tblGroup.GroupI D = tblCall.GroupID
WHERE (((tblCall.Foll owUpRequired)=F alse))
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.C all_ID) AS
CountOfCall_ID
FROM tblGroup INNER JOIN tblCall ON tblGroup.GroupI D = 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
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.[FollowUpRequire d]+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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
8586
by: Bayla Frankl | last post by:
Hi all, I am a little stumped. I have a query I am trying to run to retrieve the last Progress Note record from the database for the current patient so that the therapists can see the last note while typing the current note. This seemed to be simple to me, since I figured, you take the max(TxDate) that exists before my current date and all should be fine. I tried that and got all the records that exist for this patient before that date.
5
2392
by: Alicia | last post by:
Yes, but will that skip a week and group by the date for me? I basically wanted something that would do a count of the dates, then group them by their week name.. BEFORE: Resource Date Total number of times that date Appeared (Count) 4/3/03 4 4/4/03 2 (note 4/4/03 showed up twice)
13
5844
by: Sue | last post by:
I'm working on a database that keeps track of employees hired by a general contractor on a project by project basis. These employees are hired to work on a project and are then laid off either at the end of the work they were hired to do or at the end of the project. Any employee may be hired, laid off and rehired several times during the course of time. The tables I have and the critical fields to my question are: TblEmployee EmployeeID...
13
1900
by: Alan | last post by:
Hi Have asked before elsewhere but think I will have to rephrase the question. Using MS Access, what's the best way of tackling dates if I want to list all say; birthdays/birthdates in a date field? Many thanks as I'm totally lost on this.
3
2189
by: Eddie901 | last post by:
I have a date field in a table formatted as a "Medium Date", e.g., 29-Mar-06. When I filter by that selection it's okay when the date is after the 13th of the month, but for the 12th or below it transposes the month with the day, so if I filter by 1-Feb-06, I get those records from 2-Jan-06. Anything I can do about this? Thanks for any help. Rachel Bourne
3
2233
by: academynaomi | last post by:
hi everyone, i am trying to create a form which calls another form filtered according to the values in a text control and date control in the original form. i am using this code to do so: Private Sub GetTasks_Click() Dim strForm As String 'Name of form to open. Dim strAccField As String 'Name of the account field. Dim strMonField As String 'Name of the month field. Dim strHaving As String 'Where...
6
1830
by: Klipvis | last post by:
The months I am working with does not coincide completely with a calender month. Most months start on the 23rd and end on the 22nd. Months are still called Jan, Feb etc or month 1 to 12. I have a table with the following fields: start date, end date, month name and month number, one record per month. I have a second table with events or records which occurred on specific dates. One record for each event but more than one event/record per date...
3
5723
by: gmazza via AccessMonster.com | last post by:
Hi there, I am trying to run a report using a parameter for where the user chooses a month from a combo box. Then on the report, I want it to compare the month to a date field and choose only those dates with the month chosen from the parameter form. Basically, its to see who's birthdays are coming up. So on the parameter form you choose November, so then I want the report to display all the kids who's birthdays are in November. Any help...
3
1810
by: inglesp | last post by:
Hi everyone I've come across some perculiar behaviour in a little database app I'm making with Access. I have a form for users to create a filter for data that goes into a cross-tab query. One of the filters is by date - users can choose a start month and an end month. From this, I construct a string - something like "between #1/7/2008# and #31/7/2008#" - which I then use as a where condition in the sql statement needed to generate the...
0
8860
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9234
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9086
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8042
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5994
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2600
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2146
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.