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

Difficult Vb / Access db Query

Hi there. Been having some problems for quite some time now with trying to make a query that will show when an (Operative) is not booked in for a job. The time scale can be anytime between the hours of 08:00 - 16:30 MON - THURSDAY, 08:00 - 15:30 FRIDAY. What i am trying to show is that 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 operative 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

I am using an access db, with two tables. one contains the list of operatives (Oppname) and the other contains the list of jobs booked in (Jobs) at the moment I have a query that will show if an operative is not booked in at all, but it doesnt help if he has only been booked in for one hour. The query am using just now is 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)))
ORDER BY Job.Date;

Any help with this would be much appreciated.

Thanks
0
Spankz
Asked:
Spankz
  • 5
  • 3
  • 3
  • +1
1 Solution
 
Mikal613Commented:
first i would make table called hours and join that so at least i would know what hours to deselect and joinn it with the start time using a (not in) and you should be set.
0
 
SpankzAuthor Commented:
The hours are already in the job table. starttime and finishtime are the fields.
0
 
Mikal613Commented:
noi mean a table called hours

7:00
8:00
9:00
10:00

and join that with your hours worked
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
SpankzAuthor Commented:
Oks made the seperate table, and entered the hours. The problem is that i dont know how to go about showing the range of hours not worked.
0
 
Mikal613Commented:
then  you select the start hours  from hours where not in the start time of your employees
0
 
SpankzAuthor Commented:
but how would i go about putting it all into one query ?

SELECT DISTINCT Oppname.Operative, Job.Date, Job.StartTime, Job.FinishTime, Hours.Hours
FROM Oppname, Job, Hours
WHERE (((Oppname.Operative) Not In (SELECT  Job1.Operative
FROM Job As Job1
Where Job1.Date=Job.Date
          AND job.starttime NOT IN hours.hours
          and job.finishtime NOT IN hours.hours)))
ORDER BY Job.Date, job.starttime;

As you can probably tell im a newbie to this. Am I on the right track ?
0
 
yusufmurabenCommented:
How about using your own minutes and iterating through the database

e.g.

(pseudocode)

Daystart=8:00
Dayend=16:30

Checkthistime=daystart
freetimestart = ""
freetimeend = ""

select operartive etc

do while not eof

If checkthistime on thisdate exists in rs("startime") in db then
   set checkthistime=rs("Endtime)
   loop
end if
If checkthistime=rs("endtime") then freetimestart = rs("endtime")
if checthistime=Dayend  then freetimeend = dayend
if freetime=empty then freetimestart=daystart

increment checkthistime 1 minute

movenext
loop



basically you will loop through the minutes in a workday looking for worktimes. You will set freetimes by the beginning of day if no work, or at the end of work period and iterate though the db

0
 
Noel_CastilloCommented:
heres the solution i posted for stephen. tnx.

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;
'===================================

Select 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
 
SpankzAuthor Commented:
Thanks Noel, had one little problem though, not with the query its self, that works great, but when i go to show the query in a mshflexgrid. i get an error saying undefined finction 'DMin' in expression, the code i am using to show the query is below.

TrackMouseWheel MSHFlexGrid1.hwnd
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
Dim dmin As String


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 Job6 WHERE Date=#" & dateclicked & "#;"


I tried declaring the dmin in the decleration, but didnt work. Any ideas ?
0
 
Noel_CastilloCommented:
I see the prob. the driver treat some querries as stored proc. DMin is an agregate function. I'll try to find some solution. tnx for the points. :)
0
 
SpankzAuthor Commented:
Hi Noel, did you have any luck in trying to find a solution ? I tried for a few hours with no luck.
0
 
Noel_CastilloCommented:
no luck yet spankz. but im still workin on it. :)
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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