A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Date Grouping Query



 
 
Thread Tools Display Modes
  #1  
Old January 25th, 2005, 06:31 PM
Kenny Holden
external usenet poster
 
Posts: n/a
Default Date Grouping Query

Hi List,

I have a table of tasks;

tasks
====
task_number
task_opened
task_closed


Now im trying to figure out how to group by day. Basically, given a month
and year eg september 2004. I want to for each day in that month show the
day, the count of opened jobs on that day, and the cound of closed jobs on
that day.

Im really strugling with this query.

Obviously I can do both the queries separately. But I really want a graph
showing for each day of the month, the number opened and the number closed.
On my current 2 queries the ouput generated doesnt list days where there are
0 jobs logged, so it sometimes shows 15th, 18, 20 etc...

Does anyone know of an easy way to cycle through all possible days of the
given month and do an iif count on the date comparing to the task_opened and
task_closed and increasing the number counted by this.

Any ideas?

Many Thanks, Kenny


  #2  
Old January 26th, 2005, 12:15 AM
MGFoster
external usenet poster
 
Posts: n/a
Default

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to load a temp table of dates for the month(s) you want to
query on. Then use that table in the query to get the info you want:

Temp table:

CREATE TABLE date_temp (task_date Date)

Use VBA to load the table w/ dates.

The query to get what you want (untested):

PARAMETERS start_date Date, end_date Date;

SELECT T.task_number, D.task_date,

Count(IIf(Not IsNull(T.task_opened),1)) As Opened,
Count(IIf(Not IsNull(T.task_closed),1)) As Closed

FROM tasks As T, date_temp As D

WHERE T.task_opened BETWEEN start_date And end_date
OR T.task_closed BETWEEN start_date And end_date

GROUP BY T.task_number, D.task_date

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQfbS2oechKqOuFEgEQL8dACfcdeHBNEpcysFGYwJGwNEk8 A+aDwAn24u
MvHIerCe54gWXY1RN69H7i7E
=Zyum
-----END PGP SIGNATURE-----


Kenny Holden wrote:
Hi List,

I have a table of tasks;

tasks
====
task_number
task_opened
task_closed


Now im trying to figure out how to group by day. Basically, given a month
and year eg september 2004. I want to for each day in that month show the
day, the count of opened jobs on that day, and the cound of closed jobs on
that day.

Im really strugling with this query.

Obviously I can do both the queries separately. But I really want a graph
showing for each day of the month, the number opened and the number closed.
On my current 2 queries the ouput generated doesnt list days where there are
0 jobs logged, so it sometimes shows 15th, 18, 20 etc...

Does anyone know of an easy way to cycle through all possible days of the
given month and do an iif count on the date comparing to the task_opened and
task_closed and increasing the number counted by this.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query week to date, month to date, year to date hours Ty Running & Setting Up Queries 1 December 15th, 2004 04:46 AM
incorrect sums in report using 2 tables jkendrick75 Setting Up & Running Reports 22 December 13th, 2004 03:19 PM
query based on a date field Richard Running & Setting Up Queries 2 December 3rd, 2004 03:29 AM
Union Query Not Returning A Value Jeff G Running & Setting Up Queries 2 October 19th, 2004 05:47 PM
Does date fall between two ranges? MR Worksheet Functions 4 January 14th, 2004 05:08 PM


All times are GMT +1. The time now is 01:55 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.