Query by Month

[bceo]

I have a database that keeps track of tasks my staff does through the day, I have a simple query that counts the number of times each task was performed.
I have been running the database for a few months now and I would like to modified the query to perform the query by month and not just the total of the database entries.
I have tried to use the date field (format 01-Jan-14) but I always get a listing that totals every day in the month.
How do I modify the query or do I have to use another method?

 

[Minty]

Assuming you are using an Aggregate query (one with the groupBy row in the query grid) you need to change the "group by" on your date criteria to "where" This will remove the date from the grouping.

You might want to include the Month in your output in which case use something like ReportMonth: Format(([YourDateField]),"yyyy - mm") in your query.

 

[bceo]

Thank you for the suggestion
I assume that the ReportMonth: Format statement goes in the Criteria line.
When I did this I got a error back:
"The expression you entered has an invalid . (dot) or ! operator or invalid parentheses"
I kept deleting the item that the program highlighted, but it did not help.
Where did I go wrong?

 

[namliam]

Not in the criteria line, in the select (top) line

 

[Minty]

Sorry it should be in the Select line, as namliam has said.
I also got carried away with the brackets... Try this;

ReportMonth: Format([YourDateField],"yyyy - mm")

 

[bceo]

Thanks once again for your help
I ran the query without any modifications and printed out the results. I then modified it as you suggested and printed the results.
The two printouts were identical.
but for some reason it still does not separate between the months.

I have put the following in the top line (Field) of the query:
ReportMonth: Format(([Date]),"yyyy - mm"). My date field is called "Date"
I have selected "Where" in the Group By line.
What have I done wrong?

 

[namliam]

1) Date is a reserved word, dont use it as a column name
2) You probably still have your [Date] column in the select line, you cant have that there anymore you need to remove it

 

[Brianwarnock]

It would help if become posted his SQL .

Brian

 

[namliam]

It would help if become posted his SQL .

Brian

Say what? :banghead:

 

[bceo]

As you suggest I changed the field name from DATE to TblDate, did not get any error stating the name being a reserve name, I than moved the data to the new field and deleted the original "Date" field. I ensure that I used the revised statement, did not get an format errors.
I ran the query and still it did not separate the months.

I have attached a snapshot of the query design window

Once again I do appreciate the effort you are making to solve my problem

 

[Brianwarnock]

Say what? :banghead:

I hadn't noticed that the iPad's autocorrect had changed bceo to become.

Bceo choose Group by for report month and include the original date selection with Where and the criteria as you started out.

Brian

 

[bceo]

I am still having problems getting what I want.
To recap

In the field line (top line) I have entered `TblDate`
In the Group By line I have entered `Where`
In the Criteria line I have entered `Format([TblDate],"yyyy - mm")`
When I run the query I am not ask for the month or year I want
and I get a blank page.

Where did I go wrong. I really appreciate your patience.

 

[vbaInet]

Field: MonthField: Month([TblDate])
Total: Group By

Remove any other fields that will cause it not to group by the new month field.

 

[Brianwarnock]

I had assumed that the criteria in the date column would be like
Between startdate and enddate

If you only want one month then in the design grid you showed you can enter [yyyy-mm] in the criteria row prompting an entry such a 2014-04 as this is the only selection there is no need to add a further column to group by the year and month, unless you want that shown in the query result

Brian

 

[vbaInet]

I have been running the database for a few months now and I would like to modified the query to perform the query by month and not just the total of the database entries.

I inferred from the OP that the poster wants to group by month and s/he also mentioned that they've been entering data for the past couple of months. I could be wrong though Brian.

 

[Brianwarnock]

When I run the query I am not ask for the month or year I want
and I get a blank page.

Where did I go wrong. I really appreciate your patience.

Agreed VbaInet but he later talks about criteria on the date and also the above seems to suggest that s/he only wants to select one month, so all we can do is give pointers to options and hope that s/he can work it out as required.


Brian

 

[vbaInet]

Hmmm... I missed that. Yep, see what s/he comes back with.

 

[bceo]

My query now works exactly like I want, as always thank you to everybody to help solve this problem. Sorry it took so long.

 

[RainLover]

A short note of help for the future.

You need to apply a better naming solution. There are articles in our archives. But for a start a table is preceded with tbl. e.g. tblBuilding and no spaces in the name of any object. qryQuery qryForm qryReport. etc

HTH.