Searching A Recordset, for Certian Data

Hi there Experts, Having a problem with a search im trying to do, at the moment it will display a list of people who are not working on a particular day, but is it possible at all to show if a person would not be working at a certain time during the day, at the moment, it will only show them on the list if they are not on a job at all, The tables im using in the db are Oppname and job, I have addes the two extra fields to the job table with the start time and finish time. Would it be possible to show if say they are not working between 12:00 and 16:00 ? If they are booked in between 08:00 and 12:00. The code im using to show them for the full day is below as is the sql query using to determin if they are not booked in at all.  Any Help would be great, Thanks, Stephen

Dim SQL As String
Dim AppDate
Dim sConnect As String
Dim icol As Integer
Dim cnJobs As ADODB.Connection  'cnPatient is database name
Dim rsJobs As ADODB.Recordset 'Appointment table
Set cnJobs = New ADODB.Connection
Set rsJobs = New ADODB.Recordset
Dim cnJobs1 As ADODB.Connection  'cnPatient is database name
Dim rsJobs1 As ADODB.Recordset 'Appointment table
Set cnJobs1 = New ADODB.Connection
Set rsJobs1 = New ADODB.Recordset


DateClicked = Format(frmdiary.Calendar1.Value, "Short Date")


sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=Database\ClientDetails.mdb"


cnJobs.Open (sConnect)
cnJobs1.Open (sConnect)
'sql statement

SQL = "SELECT * From QueryJob WHERE Date=#" & DateClicked & "#;"
SQLname = "SELECT * From Oppname ORDER BY Operative"


Set rsJobs.ActiveConnection = cnJobs
rsJobs.Open SQL
Set rsJobs1.ActiveConnection = cnJobs1
rsJobs1.Open SQLname

With MSHFlexGrid2
    .Rows = 1
    .Cols = rsJobs.Fields.Count
   

    For icol = 0 To rsJobs.Fields.Count - 1
        .Col = icol
        .Text = rsJobs.Fields(icol).Name
    Next

    While Not rsJobs.EOF
        .Rows = .Rows + 1
        .Row = .Rows - 1


        For icol = 0 To rsJobs.Fields.Count - 1
            .Col = icol
            .Text = rsJobs(icol) & ""
        Next
        rsJobs.MoveNext
    Wend

End With
    If MSHFlexGrid2.Rows = 1 Then
    With MSHFlexGrid2
    .Rows = 1
    .Cols = rsJobs1.Fields.Count
   

    For icol = 0 To rsJobs1.Fields.Count - 1
        .Col = icol
        .Text = rsJobs1.Fields(icol).Name
    Next

    While Not rsJobs1.EOF
        .Rows = .Rows + 1
        .Row = .Rows - 1


        For icol = 0 To rsJobs1.Fields.Count - 1
            .Col = icol
            .Text = rsJobs1(icol) & ""
        Next
        rsJobs1.MoveNext
    Wend

End With
    End If
MSHFlexGrid2.RowHeight(0) = 400
MSHFlexGrid2.ColWidth(0) = 4000


---- SQL -----

SELECT DISTINCT Oppname.Operative, Job.Date
FROM Oppname, Job
WHERE (((Oppname.Operative) Not In (SELECT  Job1.Operative
FROM Job As Job1
Where Job1.Date=Job.Date)))
ORDER BY Job.Date;
StephenJnrAsked:
Who is Participating?
 
Noel_CastilloCommented:
It took me 6 select query and 1 union query to accomplish the task describe above. The queries are named Job0 to Job6. They are described as follow.

Select Query (Job0) - This part ensures all operative will have a distinct record for all the dates available in table Job
'===================================
SELECT DISTINCT Oppname.Operative, Job.Date
FROM Job, Oppname;
'===================================

Select Query (Job1) - Gets the original transaction from table job. This also includes empty job (unbooked from 8-4:30)
'===================================
SELECT DISTINCT Job0.Operative, Job0.Date, Job.StartTime, Job.EndTime
FROM Job0 LEFT JOIN Job ON (Job0.Date = Job.Date) AND (Job0.Operative = Job.Operative);
'===================================

Select Query (Job2) - Creates a formula fields namely IdleStartTime & IdleEndTime
'===================================
SELECT JOB1.Operative, JOB1.Date, JOB1.StartTime, JOB1.EndTime, IIf(IsNull([StartTime]),"08:00",[JOB1]![EndTime]) AS IdleStartTime, IIf(IsNull([StartTime]),#16:30:00#,IIf(IsNull(DMin("[StartTime]","Job1","[StartTime]> #" & [EndTime] & "# And [Operative] = '" & Trim([Operative]) & "' And [Date] = #" & [Date] & "#")),#16:30:00#,DMin("[StartTime]","Job1","[StartTime]> #" & [EndTime] & "# And [Operative] = '" & Trim([Operative]) & "'  And [Date] = #" & [Date] & "#"))) AS IdleEndTime
FROM JOB1;
'===================================

Select Query (Job3) - Gets the Minimum StartTime in order to get idletime from 8:00 to StartTime
'===================================
SELECT JOB1.Operative, JOB1.Date, Min(JOB1.StartTime) AS MinOfStartTime
FROM JOB1
GROUP BY JOB1.Operative, JOB1.Date;
'===================================

Select Query (Job4) - Get idletime from 8:00 to StartTime
'===================================
SELECT Job3.Operative, Job3.Date, "" AS StartTime, "" AS EndTime, #8:00:00# AS IdleStartTime, [Job3]![MinOfStartTime] AS IdleEndTime
FROM Job3
WHERE ((([Job3]![MinOfStartTime])>#8:00:00#));
'===================================

Union Query (Job5) - Combine the results of Job2 & Job4
'===================================
Select * From Job2 UNION Select * From Job4;
'===================================

Union Query (Job6) - Sort Accordingly
'===================================
SELECT Job5.Operative, Job5.Date, Job5.StartTime, Job5.EndTime, Job5.IdleStartTime, Job5.IdleEndTime
FROM Job5
ORDER BY Job5.Operative, Job5.Date, Format([StartTime],"hh:nn");
'===================================



0
 
Shahid ThaikaSole ProprietorCommented:
Maybe your query could be...
SELECT * From QueryJob WHERE (WorkTime > #TimeToCheck# AND "TimeToCheck# < (WorkTime + HourOfShift)

This query sees if the desired time is in between the range of the shift. Hope this answers your question.
0
 
StephenJnrAuthor Commented:
Thanks,but how would i go about siplaying what hours that a person wasnt working ? say hes booked in between 8-12, at the moment the query will not show him as free, even although he his free from 12 - 4, how would i show him on the list from 12 - 4 ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Shahid ThaikaSole ProprietorCommented:
Try putting a NOT parameter...

SELECT * From QueryJob WHERE NOT (WorkTime > #TimeToCheck# AND "TimeToCheck# < (WorkTime + HourOfShift)

It'll just do the opposite and give you those people who are not working. :)

If you want to show him on the list of people who are specifically free from 12 - 4, do a check in between these times as well.
SELECT * From QueryJob WHERE (NOT (WorkTime > #TimeToCheck# AND "TimeToCheck# < (WorkTime + HourOfShift)) AND ( > #12:00# AND TimeToCheck < (#16:00#)

However, I haven't checked the code, since I don't have a similar table as yours.

But hope it works and answers your question.
0
 
JR2003Commented:
You just need to change the SQL to add the start and end times to the query as below:

SELECT DISTINCT Oppname.Operative, Job.Date
FROM Oppname, Job
WHERE (((Oppname.Operative) Not In(SELECT  Job1.Operative
           FROM Job As Job1
           Where Job1.Date=Job.Date
             and Job1.StartTime < 16:00
             and Job1.EndTime > 12:00 )))
ORDER BY Job.Date;
0
 
JR2003Commented:
You may want to consider using "not exists" instead of "not in".
And I'm not sure of the exact formatting to represent times, but this should be easy to look up for your database.

JR
0
 
Noel_CastilloCommented:
maybe you are trying to filter based on current system time (is that what you mean by 'at the moment'?). try to paste this sql command as ur QueryJob.
I'd created an access file and tested this query, and works fine, that is if i understood what you are trying to ask.

SELECT DISTINCT Oppname.Operative, Job.Date
FROM Oppname, Job
WHERE (((Oppname.Operative) Not In (SELECT  Job1.Operative
FROM Job As Job1
Where Job1.Date=Job.Date And Job1.StartTime < time() And Job1.EndTime > time()  )))
ORDER BY Job.Date;

I also have the same query before that concerns taken subjects from a subject curriculum.
0
 
Noel_CastilloCommented:
If your form that displays this information - available operative as of the current time being - will stay on the screen for quite some time for monitoring purpose. You must refresh your recordset every minute.
0
 
StephenJnrAuthor Commented:
The time scale can be anytime between the hours of 8 - 4:30, someone might only be booked in for 1 hour in the morning, 8 - 9 there is not set time for one of the jobs in that day. How would i go about showing when the opperative is not working ? The range of hours working would be 8 am - 4:30 pm, is there a way to show the hours not being booked in. eg

booked in

Operative      Date           StartTime       FinishTime
Craig            12/12/04     08:00             09:00
Craig            12/12/04     10:00             15:00

Not booked in <<< Trying to show

Operative      Date           StartTime       FinishTime
Craig            12/12/04     09:00             10:00
Craig            12/12/04     15:00             16:30


 Sorry if im not being to clear. Just had no luck trying to fix the problem.
0
 
Noel_CastilloCommented:
oh i see. that looks difficult. a single booked record might create 2 unbooked record if its in the middle of schedule. Im not sure if this can be done through sql statement. i'll try to find some way. but im afraid we might end up creating a temporary file created programatically to accomplish this task. :)
0
 
StephenJnrAuthor Commented:
Thanks very much for the help, I have been stuck with this for a week, had no such luck with anything I have tried.
0
 
Noel_CastilloCommented:
I got it!!!!!!!!!! can u give me ur email. i'll send it to you. i'll post the sql statements here for the others future reference.
0
 
StephenJnrAuthor Commented:
Hi there, my email is webmaster@kornboy.com   Thats Very much for your help noel.

Stephen
0
 
Noel_CastilloCommented:
sorry. Job6 is select query. tnx. hope this helps. :)
0
 
StephenJnrAuthor Commented:
All i can say is thanks very much for the work you put in to help me. Much appreciated Noel. Thanks

Stephen
0
 
Noel_CastilloCommented:
im happy bcoz thats a good challenge. it makes me think deep and hard. im so happy when i got it. i thought it cant be done. the key there is the DMin(). im glad it solves ur prob. tnx for the points. i need that to see some premium service. but i still need to earn more points. tnx. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.