Solved

Difficult Vb / Access db Query

Posted on 2004-10-17
12
157 Views
Last Modified: 2010-05-02
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
Comment
Question by:Spankz
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 48

Expert Comment

by:Mikal613
ID: 12332041
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
 

Author Comment

by:Spankz
ID: 12334079
The hours are already in the job table. starttime and finishtime are the fields.
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 12334100
noi mean a table called hours

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

and join that with your hours worked
0
 

Author Comment

by:Spankz
ID: 12334154
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 12334163
then  you select the start hours  from hours where not in the start time of your employees
0
 

Author Comment

by:Spankz
ID: 12334208
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Expert Comment

by:yusufmuraben
ID: 12361284
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
 
LVL 3

Accepted Solution

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

Author Comment

by:Spankz
ID: 12368356
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
 
LVL 3

Expert Comment

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

Author Comment

by:Spankz
ID: 12434392
Hi Noel, did you have any luck in trying to find a solution ? I tried for a few hours with no luck.
0
 
LVL 3

Expert Comment

by:Noel_Castillo
ID: 12440889
no luck yet spankz. but im still workin on it. :)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

705 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

20 Experts available now in Live!

Get 1:1 Help Now