Spankz
asked on
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
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
Mikal613
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.
ASKER
The hours are already in the job table. starttime and finishtime are the fields.
noi mean a table called hours
7:00
8:00
9:00
10:00
and join that with your hours worked
7:00
8:00
9:00
10:00
and join that with your hours worked
ASKER
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.
then you select the start hours from hours where not in the start time of your employees
ASKER
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 ?
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 ?
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"
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
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"
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
sConnect = "Provider=Microsoft.Jet.OL
"Data Source=Database\ClientDeta
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 ?
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.
sConnect = "Provider=Microsoft.Jet.OL
"Data Source=Database\ClientDeta
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 ?
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. :)
ASKER
Hi Noel, did you have any luck in trying to find a solution ? I tried for a few hours with no luck.
no luck yet spankz. but im still workin on it. :)