Date Range Query

  • Thread starter Thread starter Kenny Holden
  • Start date Start date
K

Kenny Holden

Hi List,

Im a first time poster, and new to access. But I have used sql in the past.

I have a table called tasks listing all tasks (complete or not). What I am
trying todo is to provide a moth eg. september 2004 and then the query would
list for each day of that given month. How many tasks were opened as new
tasks on that day, and also how many tasks were closed on that day. And
finally how many tasks remained opened on this day (this is tasks that were
opened before this date, and closed after this date).

Here is a cut-down version of the table:

Tasks
------
task_id
task_opendate
task_closeddate

I am hoping the resultant query would look something like:

Day num_opened num_closed num_pending
1 23 15 22
2 5 20 24

etc...

My problem is I can do bits and bats of the query separately, but not the
whole thing, and I just keep going round in circles. Specifically I am
unable to list in a column each day of the month (I believe this is the key
to it, but I may be wrong). Incidentally, I dont know if it makes a
difference but the query will be used to make an access report.

Finally, sorry for rambling, but can anyone please recommend any books
specfic to ms-access and complex queries.

Many Thanks, Kenny
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps something like this:

SELECT
Count(IIf(task_opendate=Date(),1)) As num_opened,
Count(IIf(task_closedDate=Date(),1)) As num_closed,
Count(IIf(task_openedate<Date() And
(task_closeddate Is Null OR
task_closeddate >=Date()),1)) As pending

FROM table_name
WHERE ... etc. ...

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

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

iQA/AwUBQdsebIechKqOuFEgEQL62wCg+alUfcxK1kASMDaT4ndK+bWm4H4An2Zj
hHz406NQTXZJWmxgDfFZSEzD
=D6/Y
-----END PGP SIGNATURE-----
 
Hi,

Yeah, I already have that part of it, and assumed that was the way to do it.
Thanks for confirming that for me. The bit I cant do is the bit where the
user can enter a month like september and then it create the first column
called day which would contain the date from the 1st to the 30th. Then using
this first column I could do the count comparison.

SELECT [date] as day,
Count(IIf(task_opendate=day,1)) As num_opened,
Count(IIf(task_closedDate=day,1)) As num_closed,
Count(IIf(task_openedate<day And
(task_closeddate Is Null OR
task_closeddate >=day),1)) As pending
ect...

Any Ideas?

Thanks, Kenny
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This is incorrect:

"...then it create the first column called day which would contain the
date from the 1st to the 30th."

You can't have multiple dates in one parameter/variable in the SELECT
clause. You could use VBA to put the dates in a temp table then JOIN
that table in the current query.

CREATE TABLE date_table (
[Day] DATE
)

This will give a row for each date in the temp table. This is a
Cartesian product - a row for each date in date_table. E.g.:

SELECT D.day,
Count(IIf(task_opendate=D.day,1)) As num_opened,
Count(IIf(task_closedDate=D.day,1)) As num_closed,
Count(IIf(task_openedate<D.day And
(task_closeddate Is Null OR
task_closeddate >=D.day),1)) As pending
....
FROM table_name As T, date_table As D

====

Using VBA to produce dates in date_table:

If the user just enters a Month number you can use DateSerial() to get
the 1st of the month:

DateSerial(Year(Date()), bytMonth, 1)

To get the last date of the current month:

dateadd("m",1,dateserial(year(date()),month(date()),1))-1

Then use a VBA loop to put the dates into date_table (be sure to delete
any previous dates already in the table):

=== untested ===

dblStart = CDbl(DateSerial(Year(Date()), bytMonth, 1)
dblEnd = CDbl(DateAdd("m", 1, _
DateSerial(Year(Date()), byMonth, 1)) -1

dim dblDate as double

for dblDate = dblStart to dblEnd
currentdb.Execute _
"INSERT INTO date_table ([Day]) " & _
"VALUES (" & dblDate & ")", dbFailOnError
next

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

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

iQA/AwUBQdwlS4echKqOuFEgEQKhJgCgm9YWC293sjcsjzr4464DyjLrXoUAoKhU
I8GIi4OkXhZEPTBUXBIs+StG
=D8JP
-----END PGP SIGNATURE-----

Kenny said:
Hi,

Yeah, I already have that part of it, and assumed that was the way to do it.
Thanks for confirming that for me. The bit I cant do is the bit where the
user can enter a month like september and then it create the first column
called day which would contain the date from the 1st to the 30th. Then using
this first column I could do the count comparison.

SELECT [date] as day,
Count(IIf(task_opendate=day,1)) As num_opened,
Count(IIf(task_closedDate=day,1)) As num_closed,
Count(IIf(task_openedate<day And
(task_closeddate Is Null OR
task_closeddate >=day),1)) As pending
ect...

Any Ideas?

Thanks, Kenny



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

Perhaps something like this:

SELECT
Count(IIf(task_opendate=Date(),1)) As num_opened,
Count(IIf(task_closedDate=Date(),1)) As num_closed,
Count(IIf(task_openedate<Date() And
(task_closeddate Is Null OR
task_closeddate >=Date()),1)) As pending

FROM table_name
WHERE ... etc. ...

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

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

iQA/AwUBQdsebIechKqOuFEgEQL62wCg+alUfcxK1kASMDaT4ndK+bWm4H4An2Zj
hHz406NQTXZJWmxgDfFZSEzD
=D6/Y
-----END PGP SIGNATURE-----



past.

am

would

were

the

key
Click to expand...
Click to expand...
 
DOn't know if that will answer the question, but I think I would use SUM vice
COUNT in this situation or at least specify NULL as the second argument of the
IIF clause just to be sure.

Count(IIf(task_opendate=Date(),1,Null)) As num_opened
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

According to my experience: in JET SQL, using IIf() w/o a False
expression defaults to NULL when the test comparison is False.

Don't know which of Sum() or Count() is most efficient - haven't run any
comparison tests, just use Count() to "count" instances where some
expression is True; use Sum() only in situations where I need to
actually sum a column value. Do you have any empirical evidence for use
of one function over the other?

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

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

iQA/AwUBQdxt/YechKqOuFEgEQL90QCgzjLu+SdjFtDczb6tPBpnOuTXuskAniOZ
Xxk/Qz/vQ5srK5hqWIYzUn4E
=P3xq
-----END PGP SIGNATURE-----

DOn't know if that will answer the question, but I think I would use SUM vice
COUNT in this situation or at least specify NULL as the second argument of the
IIF clause just to be sure.

Count(IIf(task_opendate=Date(),1,Null)) As num_opened

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

Perhaps something like this:

SELECT
Count(IIf(task_opendate=Date(),1)) As num_opened,
Count(IIf(task_closedDate=Date(),1)) As num_closed,
Click to expand...


<snip>
 
Hi,

Many thanks for your help. I havent tried any code as provided by yourself
yet, but I understand the problem with the dates, and will test it later on
today.

Thanks again, - Kenny


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

This is incorrect:

"...then it create the first column called day which would contain the
date from the 1st to the 30th."

You can't have multiple dates in one parameter/variable in the SELECT
clause. You could use VBA to put the dates in a temp table then JOIN
that table in the current query.

CREATE TABLE date_table (
[Day] DATE
)

This will give a row for each date in the temp table. This is a
Cartesian product - a row for each date in date_table. E.g.:

SELECT D.day,
Count(IIf(task_opendate=D.day,1)) As num_opened,
Count(IIf(task_closedDate=D.day,1)) As num_closed,
Count(IIf(task_openedate<D.day And
(task_closeddate Is Null OR
task_closeddate >=D.day),1)) As pending
...
FROM table_name As T, date_table As D

====

Using VBA to produce dates in date_table:

If the user just enters a Month number you can use DateSerial() to get
the 1st of the month:

DateSerial(Year(Date()), bytMonth, 1)

To get the last date of the current month:

dateadd("m",1,dateserial(year(date()),month(date()),1))-1

Then use a VBA loop to put the dates into date_table (be sure to delete
any previous dates already in the table):

=== untested ===

dblStart = CDbl(DateSerial(Year(Date()), bytMonth, 1)
dblEnd = CDbl(DateAdd("m", 1, _
DateSerial(Year(Date()), byMonth, 1)) -1

dim dblDate as double

for dblDate = dblStart to dblEnd
currentdb.Execute _
"INSERT INTO date_table ([Day]) " & _
"VALUES (" & dblDate & ")", dbFailOnError
next

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

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

iQA/AwUBQdwlS4echKqOuFEgEQKhJgCgm9YWC293sjcsjzr4464DyjLrXoUAoKhU
I8GIi4OkXhZEPTBUXBIs+StG
=D8JP
-----END PGP SIGNATURE-----

Kenny said:
Hi,

Yeah, I already have that part of it, and assumed that was the way to do it.
Thanks for confirming that for me. The bit I cant do is the bit where the
user can enter a month like september and then it create the first column
called day which would contain the date from the 1st to the 30th. Then using
this first column I could do the count comparison.

SELECT [date] as day,
Count(IIf(task_opendate=day,1)) As num_opened,
Count(IIf(task_closedDate=day,1)) As num_closed,
Count(IIf(task_openedate<day And
(task_closeddate Is Null OR
task_closeddate >=day),1)) As pending
ect...

Any Ideas?

Thanks, Kenny



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

Perhaps something like this:

SELECT
Count(IIf(task_opendate=Date(),1)) As num_opened,
Count(IIf(task_closedDate=Date(),1)) As num_closed,
Count(IIf(task_openedate<Date() And
(task_closeddate Is Null OR
task_closeddate >=Date()),1)) As pending

FROM table_name
WHERE ... etc. ...

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

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

iQA/AwUBQdsebIechKqOuFEgEQL62wCg+alUfcxK1kASMDaT4ndK+bWm4H4An2Zj
hHz406NQTXZJWmxgDfFZSEzD
=D6/Y
-----END PGP SIGNATURE-----


Kenny Holden wrote:

Hi List,

Im a first time poster, and new to access. But I have used sql in the
past.

I have a table called tasks listing all tasks (complete or not). What I
am

trying todo is to provide a moth eg. september 2004 and then the query
would

list for each day of that given month. How many tasks were opened as new
tasks on that day, and also how many tasks were closed on that day. And
finally how many tasks remained opened on this day (this is tasks that
were

opened before this date, and closed after this date).

Here is a cut-down version of the table:

Tasks
------
task_id
task_opendate
task_closeddate

I am hoping the resultant query would look something like:

Day num_opened num_closed num_pending
1 23 15 22
2 5 20 24

etc...

My problem is I can do bits and bats of the query separately, but not
the

whole thing, and I just keep going round in circles. Specifically I am
unable to list in a column each day of the month (I believe this is the
key

to it, but I may be wrong). Incidentally, I dont know if it makes a
difference but the query will be used to make an access report.

Finally, sorry for rambling, but can anyone please recommend any books
specfic to ms-access and complex queries.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top