End of Month: Parameter Query?

  • Thread starter Thread starter Flopbot
  • Start date Start date
F

Flopbot

Thanks in advance to anyone who can help here! From what I’ve read so far, I
want to create a “Parameter Queryâ€. My current query (SQL below) groups the
total hours volunteered in several departments by month. When run, it
returns all the results going back to the beginning of my database. I would
like to have the query prompt me with [What month & year?]. Just so you know
I don’t really understand what SQL is or how it works but I at least know
where to find it.


Current Query SQL:

SELECT DateSerial(Year([Date Worked]),Month([Date Worked]),1) AS [Month],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS
Total, [Volunteer Hours Tracking Table].Department
FROM [Volunteer Hours Tracking Table]
GROUP BY DateSerial(Year([Date Worked]),Month([Date Worked]),1), [Volunteer
Hours Tracking Table].Department
ORDER BY DateSerial(Year([Date Worked]),Month([Date Worked]),1) DESC;


Example Data:

Jun. 2009 ~ 10 ~ Education
Jun. 2009 ~ 10 ~ Exhibits
Jun. 2009 ~ 10 ~ Admissions

Apr. 2009 ~ 5 ~ Education
Apr. 2009 ~ 5 ~ Exhibits
Apr. 2009 ~ 5 ~ Admissions


What I want it to return when prompted for “June 2009â€:

Jun. 2009 ~ 10 ~ Education
Jun. 2009 ~ 10 ~ Exhibits
Jun. 2009 ~ 10 ~ Admissions
 
It sounds like you are describing a situation in which you want to find the
Month([DateWorked]) and the Year([DateWorked]) when these are equal to the
parameter entered by the user.

So if you add two fields to your query in design view, setting them equal to
the above functions, then put your parameter prompts in the Selection
Criterion area under the respective (new) fields, your query should only get
records that have the month of your [DateWorked] field equal to ...

Is that what you're trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
That is amazing! Thank you Jeff. Seriously, how do you know this? Your
instructions worked wonderfully. I’m starting to wonder if there’s anything
Access can’t do. It’s just a matter of finding the right person to ask. Out
of curiosity. . .is their a way to set the prompt to allow me to enter “Juneâ€
instead of “6�

Thank you again Jeff!


For anyone who’s following along here is the SQL View:

SELECT DateSerial(Year([Date Worked]),Month([Date Worked]),1) AS [Month],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS
Total, [Volunteer Hours Tracking Table].Department, Year([Date Worked]) AS
Expr1, Month([Date Worked]) AS Expr2
FROM [Volunteer Hours Tracking Table]
GROUP BY DateSerial(Year([Date Worked]),Month([Date Worked]),1), [Volunteer
Hours Tracking Table].Department, Year([Date Worked]), Month([Date Worked])
HAVING (((Year([Date Worked]))=[What year? 2009]) AND ((Month([Date
Worked]))=[What month? Aug is 8]))
ORDER BY DateSerial(Year([Date Worked]),Month([Date Worked]),1) DESC;


Jeff Boyce said:
It sounds like you are describing a situation in which you want to find the
Month([DateWorked]) and the Year([DateWorked]) when these are equal to the
parameter entered by the user.

So if you add two fields to your query in design view, setting them equal to
the above functions, then put your parameter prompts in the Selection
Criterion area under the respective (new) fields, your query should only get
records that have the month of your [DateWorked] field equal to ...

Is that what you're trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Flopbot said:
Thanks in advance to anyone who can help here! From what I've read so
far, I
want to create a "Parameter Query". My current query (SQL below) groups
the
total hours volunteered in several departments by month. When run, it
returns all the results going back to the beginning of my database. I
would
like to have the query prompt me with [What month & year?]. Just so you
know
I don't really understand what SQL is or how it works but I at least know
where to find it.


Current Query SQL:

SELECT DateSerial(Year([Date Worked]),Month([Date Worked]),1) AS [Month],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS
Total, [Volunteer Hours Tracking Table].Department
FROM [Volunteer Hours Tracking Table]
GROUP BY DateSerial(Year([Date Worked]),Month([Date Worked]),1),
[Volunteer
Hours Tracking Table].Department
ORDER BY DateSerial(Year([Date Worked]),Month([Date Worked]),1) DESC;


Example Data:

Jun. 2009 ~ 10 ~ Education
Jun. 2009 ~ 10 ~ Exhibits
Jun. 2009 ~ 10 ~ Admissions

Apr. 2009 ~ 5 ~ Education
Apr. 2009 ~ 5 ~ Exhibits
Apr. 2009 ~ 5 ~ Admissions


What I want it to return when prompted for "June 2009":

Jun. 2009 ~ 10 ~ Education
Jun. 2009 ~ 10 ~ Exhibits
Jun. 2009 ~ 10 ~ Admissions
Click to expand...
Click to expand...
 
I've not found a way to get Access to balance my checkbook yet ... but I'm
sure someone somewhere has!

The answer to your "June" question is a qualified "yes". That is, it is
possible to do, but will take additional functions to convert that
Month([YourField]) to the monthname ... check Access HELP. If nothing else
is working, you could try using Format() to change date to "mmm", then
compare to "June".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Flopbot said:
That is amazing! Thank you Jeff. Seriously, how do you know this? Your
instructions worked wonderfully. I'm starting to wonder if there's
anything
Access can't do. It's just a matter of finding the right person to ask.
Out
of curiosity. . .is their a way to set the prompt to allow me to enter
"June"
instead of "6"?

Thank you again Jeff!


For anyone who's following along here is the SQL View:

SELECT DateSerial(Year([Date Worked]),Month([Date Worked]),1) AS [Month],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS
Total, [Volunteer Hours Tracking Table].Department, Year([Date Worked]) AS
Expr1, Month([Date Worked]) AS Expr2
FROM [Volunteer Hours Tracking Table]
GROUP BY DateSerial(Year([Date Worked]),Month([Date Worked]),1),
[Volunteer
Hours Tracking Table].Department, Year([Date Worked]), Month([Date
Worked])
HAVING (((Year([Date Worked]))=[What year? 2009]) AND ((Month([Date
Worked]))=[What month? Aug is 8]))
ORDER BY DateSerial(Year([Date Worked]),Month([Date Worked]),1) DESC;


Jeff Boyce said:
It sounds like you are describing a situation in which you want to find
the
Month([DateWorked]) and the Year([DateWorked]) when these are equal to
the
parameter entered by the user.

So if you add two fields to your query in design view, setting them equal
to
the above functions, then put your parameter prompts in the Selection
Criterion area under the respective (new) fields, your query should only
get
records that have the month of your [DateWorked] field equal to ...

Is that what you're trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Flopbot said:
Thanks in advance to anyone who can help here! From what I've read so
far, I
want to create a "Parameter Query". My current query (SQL below)
groups
the
total hours volunteered in several departments by month. When run, it
returns all the results going back to the beginning of my database. I
would
like to have the query prompt me with [What month & year?]. Just so
you
know
I don't really understand what SQL is or how it works but I at least
know
where to find it.


Current Query SQL:

SELECT DateSerial(Year([Date Worked]),Month([Date Worked]),1) AS
[Month],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS
Total, [Volunteer Hours Tracking Table].Department
FROM [Volunteer Hours Tracking Table]
GROUP BY DateSerial(Year([Date Worked]),Month([Date Worked]),1),
[Volunteer
Hours Tracking Table].Department
ORDER BY DateSerial(Year([Date Worked]),Month([Date Worked]),1) DESC;


Example Data:

Jun. 2009 ~ 10 ~ Education
Jun. 2009 ~ 10 ~ Exhibits
Jun. 2009 ~ 10 ~ Admissions

Apr. 2009 ~ 5 ~ Education
Apr. 2009 ~ 5 ~ Exhibits
Apr. 2009 ~ 5 ~ Admissions


What I want it to return when prompted for "June 2009":

Jun. 2009 ~ 10 ~ Education
Jun. 2009 ~ 10 ~ Exhibits
Jun. 2009 ~ 10 ~ Admissions
Click to expand...
Click to expand...
Click to expand...
 
Back
Top