• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

How to query within a specific date and time range in Access Database

Hello,
A continuation of question

http://www.experts-exchange.com/Databases/MS_Access/Q_21822615.html

I have the following query...

SELECT working.t_mode AS Mode, Count(working.t_mode) AS [Number by Mode], Count(t_mode)/(SELECT COUNT(*) FROM try) AS [Mode Percent]
FROM working
WHERE Cdate(Format(working.t_dttm,'mm/dd/yyyy')) BETWEEN
#3/1/2006# and #3/3/2006#
GROUP BY working.t_mode;

Note: The date field I will be using is a date and time stamp...so will have to figure out how to deal with it with the time in it or how to loose the tiem.
e.g. 2006-03-03 7:58:31 AM

1) How do I query within a time range associated to a date or perhaps not associated to a date...and
2) How do I count the records returned so that when I calculate the Mode Percent it is divided by the records returned (within the date/time range queried) versus the entire number of records in the table.

Thanks Groovymonkey

0
groovymonkey
Asked:
groovymonkey
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you can use  TimeValue to get the time from a date field

TimeValue(#2006-03-03 7:58:31 AM#) will give you    

         7:58:31 AM
0
 
nico5038Commented:
You can also include the min and max time for the start and end date like:

SELECT working.t_mode AS Mode, Count(working.t_mode) AS [Number by Mode], Count(t_mode)/(SELECT COUNT(*) FROM try) AS [Mode Percent]
FROM working
WHERE working.t_dttm BETWEEN
#3/1/2006 00:00:00# and #3/3/2006 23:59:50#
GROUP BY working.t_mode;

Nic;o)
0
 
nico5038Commented:
Oops, overlooked part 2, for that you can use:

SELECT working.t_mode AS Mode, Count(working.t_mode) AS [Number by Mode], Count(working.t_mode)/(SELECT COUNT(*) FROM working) AS [Mode Percent]
FROM working
WHERE working.t_dttm BETWEEN
#3/1/2006 00:00:00# and #3/3/2006 23:59:50#
GROUP BY working.t_mode;

or the slower use of the Dcount function like:

SELECT working.t_mode AS Mode, Count(working.t_mode) AS [Number by Mode], Count(working.t_mode)/DCOUNT("*","working") AS [Mode Percent]
FROM working
WHERE working.t_dttm BETWEEN
#3/1/2006 00:00:00# and #3/3/2006 23:59:50#
GROUP BY working.t_mode;

Nic;o)
0
 
groovymonkeyAuthor Commented:
Hello,
I could be wrong but the code above will divide by the total number of records in the db...I only want it to divide by the number of the records that is returned by the specific date/time query.
0
 
nico5038Commented:
Hmm, try:

SELECT A.t_mode AS Mode, Count(working.t_mode) AS [Number by Mode], Count(A.t_mode)/(SELECT COUNT(*) FROM working B WHERE B.t_dttm BETWEEN #3/1/2006 00:00:00# and #3/3/2006 23:59:59#) AS [Mode Percent]
FROM working A
WHERE A.t_dttm BETWEEN
#3/1/2006 00:00:00# and #3/3/2006 23:59:59#
GROUP BY A.t_mode;

Nic;o)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now