Query and Year Question

  • Thread starter Thread starter DavidW
  • Start date Start date
D

DavidW

How do you sort through 15 years with months and only end up with 1 year
representing each 12 month period instead of 12 different entries.
I am trying to do a query that will sum each years data.
What I have tried shows each individual month instead of summing the year,
maybe I am thinking wrong?
How would you do this?
Thanks
David
 
Dear David

You could add a year field to your query, and then put a
criteria on that field to select just the records for that
year. IE in your query you would add a new column and in
the Field name of the new column you would enter something
like YearField:=YEAR([YourTableName]![YourDateFieldName])
This will extract just the year part of the date. To limit
the records to the year that you want you can either enter
the year into the criteria of the new year field, or to
make it more usable, you could type [Please enter The Year
You Wish To Sum (YYYY)] into the criteria field. This
second method will open a pop up form asking you for the
year to limit the query to every time the query is opened.

Hope this helps

Paul
 
Try adding (or calculating in the query) a field for Year and then sum by the Year field

----- DavidW wrote: ----

How do you sort through 15 years with months and only end up with 1 yea
representing each 12 month period instead of 12 different entries
I am trying to do a query that will sum each years data
What I have tried shows each individual month instead of summing the year
maybe I am thinking wrong
How would you do this
Thank
Davi
 
DavidW said:
How do you sort through 15 years with months and only end up with 1 year
representing each 12 month period instead of 12 different entries.
I am trying to do a query that will sum each years data.
What I have tried shows each individual month instead of summing the year,
maybe I am thinking wrong?
Click to expand...

Use a Totals query. Set the amount field to Sum and the
year field to Group By.
 
YOU MADE MY DAY ! ! !
That is the winning ticket to a bunch of "ifs" I would of had to contend
with later.
One of these days I will learn
TAL!
David
 
Is there a way to change the header of the message
I can see where if a stranger was introduced, he is not going to understand
what "Enter A Parameter Value" is.
 
DavidW said:
Is there a way to change the header of the message
I can see where if a stranger was introduced, he is not going to understand
what "Enter A Parameter Value" is.
Click to expand...

No. Parameter queries are the *most basic* way of providing configurable
criteria and aren't used much in "production quality" applications. You could
build you own form where the user enters the criteria and then provide a button
to run the query. The Query can "look" at your form and use the value entered
there with a criteria expression along the lines of...

SELECT * FROM YourTableName
WHERE Year([YourDateField]) = Forms!NameOfForm!NameOfTextBox
 
I got the year thing to work, now how do you get the month and year combined
with

monthField: Month([usage]![pumpda])
 
Back
Top