Hi Stacy,
you're welcome
the SQL I gave you for
qMinutesOfDay
is:
SELECT #7/16/2008# + TimeSerial(0,[num],0) AS time_
FROM Numbers;
what you need to be able to do is replace the SQL using a different date
before you run the query that uses it. Since we did not give the other
query a name, I will call it qConcurrentJobs
~~~
in looking at the criteria I gave you for qConcurrentJobs, you would NOT
need the third part, which limits for the StartJob being the same day --
this is actually not what you want. All you care about it is it is
running in that minute, not if it started that day -- so just his:
WHERE ( (Jobs.StartJob<=[time_]) AND (Jobs.EndJob >=[time_]) )
~~~
the best way to collect the date criteria is to make a form to collect
the Date that you want.
on this form:
1. make the following textbox control:
Name --> Date1
(that way, you can add Date2 down the road in case you want a range --
and Date is a reserved word so we certainly don't want to use that)
2. and make a command button:
Name --> cmd_RptConcurrentJobs
on the Click event of the command button, this will be the code for your
[Event Procedure]
'~~~~~~~~~~~~~~
if isNull(me.Date1) then
me.Date1.SetFocus
MsgBox "You must choose a date",,"Can't run report"
exit sub
end if
if Not IsDate(me.Date1) then
me.Date1.SetFocus
MsgBox "You must enter a valid date",,"Can't run report"
exit sub
end if
dim strSQL as string
strSQL = "SELECT #" & me.date1 & "# _
+ TimeSerial(0,[num],0) AS time_" _
& " FROM Numbers;"
MakeQuery strSQL, "qMinutesOfDay"
docmd.OpenQuery "qConcurrentJobs"
'~~~~~~~~~~~~~~
this code will go into a standard (general) module so you can use it
anywhere:
'~~~~~~~~~~~~~~~~~~~~~ MakeQuery
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)
'modified 3-30-08
'crystal
'strive4peace2008 at yahoo dot com
On Error GoTo Proc_Err
debug.print pSql
'if query already exists, update the SQL
'if not, create the query
If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
'if query is open, close it
on error resume next
DoCmd.Close acQuery, qName, acSaveNo
On Error GoTo Proc_Err
CurrentDb.QueryDefs(qName).sql = pSql
End If
Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub
Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"
Resume Proc_Exit
'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~
to use the MakeQuery procedure, put this in your code:
MakeQuery strSQL, "YourQueryName"
*** How to Create a Standard (General) Module ***
1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in
once the code is in the module sheet, from the menu, do -->
Debug,Compile
if there are no syntax/reference errors, nothing will appear to happen
-- in this case, nothing happening is good <g>
Make sure to give the module a good name when you save it. You can have
several procedures (Subs and Functions) in a module, which gives you a
way to categorize them ... ie: basic procedures that would be useful in
any database; procedures that are specific to a particular database;
procedures for converting data; etc
~~~~~~~~~~~~~~~~`
to make the code easier to read, I did not include an error handler to
open your query -- but you should put it in.
'~~~~~~~~~~~~~~
'set up Error Handler - 'put this at the top of your program
' -- right after the procedure declaration
On Error GoTo Proc_Err
'...then come the statements of your procedure ...
' this will be the code I gave you above
'then the exit code and error handler statements at the bottom
Proc_Exit:
On Error Resume Next
'close and release object variables if applicable
Exit Sub ' or Function
Proc_Err:
'NOTE: replace ProcedureName with YOUR procedure name
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"
Resume Proc_Exit
'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
'~~~~~~~~~~~~~~
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
D. Stacy said:
Crystal,
Thanks for the great information its working great and I did notice the
hour / min difference but the minutes actually is working better for the time
being.
My next task is to modify these querys so that I can [enter the date]
each time that I run the query.
How do you get the "first" query to take the date upon running of the
second qry?
:
oops! you said for each hour, not each minute -- then you only need 0-24...
TimeSerial([num],0,0) AS time_
.... I see MGFoster gave you some good information too
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
strive4peace wrote:
Hi Stacy (is that your name?)
make this table:
Numbers
- Num, integer -- primary key
with records from 0 to 1440 (# minutes in a day)
then, make a query like this:
query name --> qMinutesOfDay
SELECT #7/16/2008# + TimeSerial(0,[num],0) AS time_
FROM Numbers;
where #7/16/08# is the date you want minutes for
then, make a query on top of that like this:
SELECT qMinutesOfDay.time_, Count(Jobs.JobID) AS NumJobs
FROM Jobs, qMinutesOfDay
WHERE (((Jobs.StartJob)<=[time_]) AND ((Jobs.EndJob)>=[time_]) AND
((DateValue([StartJob]))=#7/16/2008#))
GROUP BY qMinutesOfDay.time_;
once you have it working for a particular day, you can work on making
the day a variable
~~~
btw, a Numbers table is very handy for other things too -- like:
1. getting reports for every day in a month even if there is no data
2. printing multiple copies of a report
etc...
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
D. Stacy wrote:
I'm dealing with data concerning timed events. My data consist of
start times, stop times, case number, et. al. the start / stop times
data is formatted such that the date / time is in the same field
therefore I frequently use the DateDiff() function to calculate total
Minutes of each case.
The report / data that I would like to produce is how many cases are
concurrently running during any given hour of the day (1 -24).
The mystry (to me) is how to deal with the counting of cases that for
example start at 10:55 am and stop at 12:05 pm. This cases should
count as "active" during the 10am, 11am and the 12pm hour and summed
up with all the other cases that were "active during these same hours.
