Staff Availability


D

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
 
Ad

Advertisements

G

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.
 
C

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
 
D

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])
 
D

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])


DMWM said:
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])

Clifford Bass said:
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
 
Ad

Advertisements

C

Clifford Bass

Hi,

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

Clifford Bass
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top