Solved

Searching A Recordset, for Certian Data

Posted on 2004-10-12
16
187 Views
Last Modified: 2010-05-02
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;
0
Comment
Question by:StephenJnr
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12290748
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
 

Author Comment

by:StephenJnr
ID: 12290811
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
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12290929
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
 
LVL 18

Expert Comment

by:JR2003
ID: 12293029
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
 
LVL 18

Expert Comment

by:JR2003
ID: 12293036
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
 
LVL 3

Expert Comment

by:Noel_Castillo
ID: 12305274
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
 
LVL 3

Expert Comment

by:Noel_Castillo
ID: 12305288
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
 

Author Comment

by:StephenJnr
ID: 12307357
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 3

Expert Comment

by:Noel_Castillo
ID: 12316469
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
 

Author Comment

by:StephenJnr
ID: 12316772
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
 
LVL 3

Expert Comment

by:Noel_Castillo
ID: 12336710
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
 

Author Comment

by:StephenJnr
ID: 12337561
Hi there, my email is webmaster@kornboy.com   Thats Very much for your help noel.

Stephen
0
 
LVL 3

Accepted Solution

by:
Noel_Castillo earned 500 total points
ID: 12344030
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
 
LVL 3

Expert Comment

by:Noel_Castillo
ID: 12345321
sorry. Job6 is select query. tnx. hope this helps. :)
0
 

Author Comment

by:StephenJnr
ID: 12345405
All i can say is thanks very much for the work you put in to help me. Much appreciated Noel. Thanks

Stephen
0
 
LVL 3

Expert Comment

by:Noel_Castillo
ID: 12345656
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now