Staff Availability

[DMWM]

Hi Im creating a database for a friends company which is a car workers agency.

I have the following tables:

tblCustomer

tblStaff

tblJob

Each staff member has an availablilty section comprising of days of the week
and rough times split as:

Early
Late
Long Day
Night

Each availability field is a yes/no field. It has to be this way as it is
the only way of representing that staff are available every Monday every
week, for example.

Once a job is booked the date of the job is entered into tblJob.

I am trying to achieve a query that shows which staff are available for a
particular shift and have not been booked.

For example,

A list that will show all staff that are available but not booked for a
Monday Early shift on 22nd June 2009.

I hope this makes sense and look forward to a response.

Thanks

 

[ghetto_banjo]

It sounds like you need something like the "Unmatched Query" that
access tries to automate with a wizard.

Basically, you want to join tblStaff to tblJob via staffID (or
whatever the link is between the tables), but do a Left Join (right
click on the join link and select choose ALL records from tblStaff...)

I am a little unclear on how your availability fields are setup, but
you will need your criteria to something like: MondayLate = Yes.

Then criteria for tblJob.jobDate = 6/22/2009 (or whatever).

then criteria for tblJob.StaffID = Null (this is the trick to
finding staff that do NOT have a job for that date but otherwise
available).




hopefully that makes a little sense. If you do the Unmatched Query
Wizard, while it might not do it exactly right, it should point you in
the right direction of what to do.

 

[Clifford Bass]

Hi,

Try something like this (you will need to adjust the column names):

select Staff_ID, Staff_Last_Name, Staff_First_Name
from tblStaff
where Staff_ID not in (select Staff_ID from tblJob where Job_Date =
#6/22/2009# and Early) and Early;

If you have trouble with that, please post the columns of interest from
the tblStaff and tblJob tables along with the SQL that you have come up with
that is not working.

Clifford Bass

 

[DMWM]

Thankyou Clifford Bass very helpful indeed.

The problem i face and the example i am working with:

I want to find staff that are available on friday early shifts

I also need the query to show me only the staff that have not been booked on
a certain date for example 19/6/09 but the date is variable i am trying to
get around it with a double WHERE SQL statement. is there a way around this -
code posted below:

SELECT tblStaff.ID, tblStaff.Staff_Name, tblStaff.Telephone_Home,
tblStaff.Telephone_Mobile, tblStaff.Cornwall, tblStaff.Plymouth,
tblStaff.Torbay, tblStaff.Exeter, tblStaff.South_Hams, tblStaff.Friday,
tblStaff.Early
FROM tblStaff
WHERE (((tblStaff.Friday)=Yes) AND ((tblStaff.Early)=Yes))
AND
WHERE ID Not in (Select Staff_ID from tblJob where DateofJob = [Enter Date])

 

[DMWM]

I have had a play with it and the answer is quite simple...Always is when you
get there!!

This is the SQL i am using in case it is helpful to anyone else:

SELECT tblStaff.ID, tblStaff.Staff_Name, tblStaff.Telephone_Home,
tblStaff.Telephone_Mobile, tblStaff.Cornwall, tblStaff.Plymouth,
tblStaff.Torbay, tblStaff.Exeter, tblStaff.South_Hams
FROM tblStaff
WHERE (((tblStaff.Friday)=Yes) AND ((tblStaff.Early)=Yes)) AND tblStaff.ID
Not in (Select Staff_ID from tblJob where DateofJob = [Enter Date])

Thankyou Clifford Bass very helpful indeed.

The problem i face and the example i am working with:

I want to find staff that are available on friday early shifts

I also need the query to show me only the staff that have not been booked on
a certain date for example 19/6/09 but the date is variable i am trying to
get around it with a double WHERE SQL statement. is there a way around this -
code posted below:

SELECT tblStaff.ID, tblStaff.Staff_Name, tblStaff.Telephone_Home,
tblStaff.Telephone_Mobile, tblStaff.Cornwall, tblStaff.Plymouth,
tblStaff.Torbay, tblStaff.Exeter, tblStaff.South_Hams, tblStaff.Friday,
tblStaff.Early
FROM tblStaff
WHERE (((tblStaff.Friday)=Yes) AND ((tblStaff.Early)=Yes))
AND
WHERE ID Not in (Select Staff_ID from tblJob where DateofJob = [Enter Date])

Hi,

Try something like this (you will need to adjust the column names):

select Staff_ID, Staff_Last_Name, Staff_First_Name
from tblStaff
where Staff_ID not in (select Staff_ID from tblJob where Job_Date =
#6/22/2009# and Early) and Early;

If you have trouble with that, please post the columns of interest from
the tblStaff and tblJob tables along with the SQL that you have come up with
that is not working.

Clifford Bass

 

[Clifford Bass]

Hi,

Glad to see you got it to work and that my comments were helpful. You
are welcome!

Clifford Bass