Using an sql algorithym?

Hello all
I wonder if anyone can help me with this..
I am building a piece of scheduling software for a small company.
I implemented an access database with the following tables:

Days (DayID, Day)

Hours (HourID,Time)

Shift (ShiftID, DayID, HourID)

StaffAvailability (AvailabilityID, staffID, ShiftID)

Staff (StaffID, Forename, Surname, JobID)

JobTitle (JobID, Title)

The table shift has 98 records in it, these are all the different shifts in a week (14 a day)

I am developing the software in vb6. I have built a vb6 application with the following forms.

FrmAddStaffMember
FrmEditStaffMember
FrmRemoveStaffMember
FrmMainMenu
FrmStaffAvailabllity
FrmStaffDetails
FrmStaffRota
FrmViewStaffDetails

I have managed to insert into and delete from the database using sql in the following forms: FrmAddStaffMember, FrmEditStaffMember, FrmRemoveStaffMember, FrmMainMenu, FrmStaffAvailabllity.

The form staff availablity consists of a series of 14 check boxes for each day so 98 in all. When the program is running the user selects availble working times for each meber of staff and adds the values in to StaffID and ShiftID

Now I need to generate a list of available employees for each shift and display it in a rota. I am not that sure how to do this, think it might be an algorithym or something and I was wondering if anyone could help?

I have attached the vb program and the database

Thanks

Ian

You have to decide what "available" means, in terms of the data you have in the various tables, then create a WHERE clause in your SELECT statement that selects available employees.

StaffAvalibility is a table in the database. Inside this table are the fields AvalabilityID, ShiftID and StaffID. This means you know who can work which shifts. What i need is the code so that the vb program will look at the availability table in the databse, decide on who should work when and display it. in the vb program.

Quote:

---

Originally Posted by ian_bateman3

StaffAvalibility is a table in the database. Inside this table are the fields AvalabilityID, ShiftID and StaffID. This means you know who can work which shifts. What i need is the code so that the vb program will look at the availability table in the databse, decide on who should work when and display it. in the vb program.

---

Thats what Al42 is telling you how to do. You need to determine what criteria in the DB is going to mean that an employee is available. Once you know how to tell if the employee is available (I guess I would use a 1 for available and 0 for not) you can use a WHERE clause in your SELECT statement. For example:

SELECT employee from table WHERE available = '1'

cool think i understand what you mean now, thanks for you help

Ok i have assigned a field in the database called available (value Yes or NO) and included this in my code. The code now seems to work but i am having problems getting in to print the result to a form in vb. This is my code:

Dim Conn As ADODB.Connection
Dim ADODB1 As ADODB.Recordset
Dim ADODB2 As ADODB.Recordset

Private Sub BtnUpdate_Click()

Set Conn = New ADODB.Connection
Conn.ConnectionString = "DSN=db1"

Conn.Open

sqlstring = "SELECT StaffID, ShiftID"
sqlstring = sqlstring + " FROM StaffAvailability"
sqlstring = sqlstring + "WHERE Availability = yes"
sqlstring = sqlstring + ";"
MsgBox (sqlstring)

End Sub


Think this works fine, the msgbox looks right. Now i just want to print it to a text box or something. Any ideas?

If you want to see the Persons name and shift available then like this:

For One Shift (you select the shift)

Code:

---

strsql = "Select Staff.Forename,Staff.Surname,JobTitle.Title "
strsql = strsql & "From Staff Inner Join JobTitle.JobID on Staff.JobID "
strsql = strsql & "Where StaffID In (Select StaffAvailability.StaffID From StaffAvailability "
strsql = strsql & "Where StaffAvailability.ShiftID = " & intShifID

---

For More then one shift:

Code:

---

dim strShifts as string
strShifts = "("
'Now for each checkbox see if it is checked and assign its value to the string strShifts
strShifts = strshifts & ")"
strsql = "Select Staff.Forename,Staff.Surname,JobTitle.Title "
strsql = strsql & "From Staff Inner Join JobTitle.JobID on Staff.JobID "
strsql = strsql & "Where StaffID In (Select StaffAvailability.StaffID From StaffAvailability "
strsql = strsql & "Where StaffAvailability.ShiftID In " & strShifts

---

Then yu can place the resulting record set into a Felxgrid or a combobox

ok thanks for your help. I am now using the following code:

Code:

---

Private Sub BtnUpdate_Click()

 Set Conn = New ADODB.Connection
 Conn.ConnectionString = "DSN=db1"

 Conn.Open

strShifts = "("
'Now for each checkbox see if it is checked and assign its value to the string strShifts
strShifts = strShifts & ")"
strsql = "Select Staff.Forename,Staff.Surname,JobTitle.Title "
strsql = strsql & "From Staff Inner Join JobTitle.JobID on Staff.JobID "
strsql = strsql & "Where StaffID In (Select StaffAvailability.StaffID From StaffAvailability "
strsql = strsql & "Where StaffAvailability.ShiftID In " & "strShiftsdim strShifts as string"
strShifts = "("
ViewReportpb.FontName = "arial"
ViewReportpb.FontSize = "10"
Set MyRecset = Conn.Execute(sqlstring)
      ViewReportpb.Print "" 'print the results into the picture box
    ViewReportpb.Print ""
 
End Sub

---

When i run the code i get the error message "comand text was not set for the command object." any ideas?

You still need a recordset to get the results so:

vb Code:

Dim rs as ADODB.Recordset
 Set rs = New ADODB.Recordset
 
  rs.Open strsql Conn

The Execute method on the Conn object is to perform modification of data only (DB structure also). To get data back you need to open the recordset using the Open method of the Recordset object explicitly.

I used the above recordset

Code:

---

Dim Conn As ADODB.Connection
Dim ADODB1 As ADODB.Recordset
Dim ADODB2 As ADODB.Recordset
Dim strShifts As String

   
Private Sub ReturnToMainMenubtn_Click() 'return to main menu button
Menufrm.Visible = True
OverdueReportfrm.Visible = False
End Sub

Private Sub BtnUpdate_Click()

 Set Conn = New ADODB.Connection
 Conn.ConnectionString = "DSN=db1"

 Conn.Open

strShifts = "("
'Now for each checkbox see if it is checked and assign its value to the string strShifts
strShifts = strShifts & ")"
strsql = "Select Staff.Forename,Staff.Surname,JobTitle.Title "
strsql = strsql & "From Staff Inner Join JobTitle.JobID on Staff.JobID "
strsql = strsql & "Where StaffID In (Select StaffAvailability.StaffID From StaffAvailability "
strsql = strsql & "Where StaffAvailability.ShiftID In " & "strShiftsdim strShifts as string"
strShifts = "("
ViewReportpb.FontName = "arial"
ViewReportpb.FontSize = "10"
Set MyRecset = Conn.Execute(sqlstring)
      ViewReportpb.Print "" 'print the results into the picture box
    ViewReportpb.Print ""
 
End Sub

---

Do you have another question?