Availability Calendar in access

[tommy93]

Hello,

Im currently in the process of developing a database for a friend for his company. This company controls in the region of about 100 - 200 engineers that visit homes and carry out repairs (Gas, electric etc), all of which are in the database. The database is mainly for the recording of jobs and later assigning engineers to them.



I need to find a way of controlling and recording engineer availability for assigning them to new jobs. To assign an engineer to a job, there is a query that searches the area of the job to find what engineers cover that area, for example London. At the moment, the query is showing up all engineers on the database, however I want it to only show engineers that are available. I.e. isn't on a job at the time, isn't on holiday, or isn't outside of his/her working hours.

Does anyone know of an efficient way to do this? Via a calendar or something similar.

Thanks

Tom

[Dal Jeanis]

The basic answer is that you must have the required information in the database before you can use that information to create a report. So, how does the database store the information regarding:
1) Which engineers are on holiday?
2) What hours do each engineer work?
3) Which engineers are on assignment?
4) Where, physically, are the available engineers?
5) In what region(s) do the available engineers accept work?

[tommy93]

Thats kinda what im looking for, an efficient way to record it.

The ideal solution would be having a calendar in access and you could just highlight days or weeks that the person is on holiday and such. The address and areas of work are already implemented and working in the search, I just need a way of recording holiday times and hours of work.

At the moment i have 2 date boxes which are "Holiday From" and "Until", i just need to include that in the query. But i was hoping that there was a way of doing it via a visual calendar...

[Dal Jeanis]

I don't see how a search looking FOR a free engineer could include date boxes for her holiday. The database has to have her vacation information already in it for that, in a table.

When the engineer is scheduling her vacation, you can use a date-picker to set the values, and save her scheduled time off into an [EngHolidays] table.

Then at assignment time, your SQL would have a

WHERE [EngID] NOT IN 
   (Select [EngID] from [EngHolidays] 
   WHERE [EngHolStart] <= [TempVars].[AssignmentDate] 
   AND [EngHolEnd] >= [TempVars].[AssignmentDate])

clause to exclude the engineers that will be on holiday on the AssignmentDate.

[June7]

I think there is an Access schedule database template available. Check through the available templates when you create a new database.

[tommy93]

Right, ill give that a go now.

Ive got an idea on how that could work. Another question though is how could I tweak that to work with multiple holidays? Since its in a separate table I could get it to record holidays through a "holiday booking" form, but then searching for engineers with multiple instances of a holiday?

[Dal Jeanis]

The code I gave you assumes that there is a [EngHoliday] table with at least three fields, EngID, EngHolStart, EndHolEnd. An engineer could schedule any number of holidays in that table, overlapping or not, and the NOT IN code I gave you would still eliminate from consideration any engineer who had a holiday planned across the date you were trying to schedule for.

[tommy93]

Hello,

Sorry its been a while since I replied again, been busy!

Right i got that code to work, but is it possible to show all engineers in the report, but highlight (or even just a simple check box) the ones that are unavailable?

Thanks

[June7]

Possibly. Take the query that shows only available engineers and join to table of all engineers (join type "Show all records from Engineers ..."). The query needs to have a field that will be null in the joined set for the engineers that are not available. Use that field in a textbox expression: =Nz([fieldname],"Not Available") and/or use Conditional Formatting.

[tommy93]

Possibly. Take the query that shows only available engineers and join to table of all engineers (join type "Show all records from Engineers ..."). The query needs to have a field that will be null in the joined set for the engineers that are not available. Use that field in a textbox expression: =Nz([fieldname],"Not Available") and/or use Conditional Formatting.

Sorry, i dont quite understand. Could you elaborate?
If it helps, here's the current query;

Basically from engineers, it selects which ones are not on holiday, then filters to the ones that cover a certain area based on an input value in the forms.

SELECT Engineers.Engineer_ID, Engineers.Engineer_Name, Engineers.AreaCovered, Engineers.AreaCovered.ValueFROM Engineers
WHERE (((Engineers.Engineer_ID) Not In (Select [Engineer_ID] from [EngHolidays] 
   WHERE [EngHolStart] <=[forms]![JobEntry]![Date_Booked] 
   AND [EngHolEnd] >= [forms]![JobEntry]![Date_Booked] )) AND ((Engineers.AreaCovered.Value) Like [forms]![Assignment]![AreaSearch]));

This basically outputs all engineers that, for example work under the postcode "b", that do not have a holiday booked during the time the job is booked. I need to show all engineers that work the postcode, but then either highlight, or a check box, to show the person is not actually available? If thats possible.

[June7]

Build another query. Use the Engineers table and the query you posted. Join them on the EngineerID fields with join type "Include ALL records from 'Engineers' ...".

The fields from the posted query should be null in the new query on the rows for engineers that aren't in the posted query dataset.

Try it, see what happens.

[tommy93]

Build another query. Use the Engineers table and the query you posted. Join them on the EngineerID fields with join type "Include ALL records from 'Engineers' ...".

The fields from the posted query should be null in the new query on the rows for engineers that aren't in the posted query dataset.

Try it, see what happens.

SELECT EngArea.Engineer_ID, EngArea.Engineer_Name, EngArea.AreaCovered, Engineers.Engineer_IDFROM Engineers INNER JOIN EngArea ON Engineers.Engineer_ID = EngArea.Engineer_ID;

Ok so thats my new code, but how do i tell it to include all from engineers?

Thanks

[June7]

In the query builder, click the line that connects table/query to open relationship dialog. Change the join type.

[tommy93]

SELECT Engineers.Engineer_ID, EngArea.Engineer_Name, EngArea.AreaCoveredFROM Engineers LEFT JOIN EngArea ON Engineers.Engineer_ID = EngArea.Engineer_ID;

That just seems to return this, which would work. But the one is duplicated?

EDIT: Okay no that wont work. It doesnt seem to actually carry out the query into area worked.

[June7]

I don't see duplicate in that image.

Sorry, don't know what you mean by the EDIT remark.