Query to find available laptops

[mousemat]

I am building a database for the company I work for. We offer software training on a specific software.

We have 12 laptops and currently two trainers.

Courses are generally 2 days in length but due to having two trainers, both trainers can be out either at the same time or overlapping each other, for example, I might be out on Monday and Tuesday, the other other trainer might be out on Tuesday or Wednesday, or even Wednesday & Thursday etc.

I need to create a query that will tell me the available laptops (we currently have 12) for a specified date, but taking account of the variables as detailed.

So for instance I have 4 laptops booked out between 1st & 2nd of Month and I want to book another course for say 2nd & 3rd of the month requiring 9 laptops.

I need to be able to produce a query that will show that there are no available laptops for the second course as the 4 from the first course wont be back in time

 

[CJ_London]

Need more information to be able to provide assistance.

Can you post your table details - table name, field name and datatype plus some sample data and relationships

Solution very much depends on what you have at the moment

 

[mousemat]

CJ

thanks for your response

I have add a very simple database in A2007 format.

There are three courses within the database.

Normally, the trainer will take the laptops the day before the start date and return the day after the finish date. On courses starting on a Monday, the trainer will usually take the laptops on a friday evening.

Im certainly open to ideas if things need changing.

Also, I dont want to double book the trainers, which has happened in the past

 

[CJ_London]

Thanks, had a quick look - pleased not to see any spaces in names

Table design looks OK in principle but need to clarify a few things

Normally, the trainer will take the laptops the day before the start date and return the day after the finish date. On courses starting on a Monday, the trainer will usually take the laptops on a friday evening

You've said 'Normally' - is this a business rule that is always applied or will the trainer state when they are taking and returning the laptops. If it is a business rule, what happens around bank holidays, or when the trainer has to travel an extra day, or goes straight form one course to another, taking the equipment with them etc? Recommend for simplicity, you have extra DateFrom/DateTo fields for the booking of equipment (and trainer)

Also, I dont want to double book the trainers, which has happened in the past

You'll need to modify your design to either include trainers as resources or have a separate trainer table and another for when they are booked out. Trainers as resources is the simpler solution if you are not going to need to store things like trainer qualifications, contact details etc. Will also need to consider how to cater for holidays and other agreed absences.

Table design - tblResources. What happens as the business grows and you need more laptops or projectors or one breaks? suggest this also needs DateFrom, DateTo fields so if you are buying 3 new laptops to arrive in 2 weeks time, you can book more laptops in two weeks - it will then become a history table.

You have some duplication of data in tblbookedresources and tblcourses (no of laptops) and no of projectors is not catered for in tblcourses - recommend remove no of laptops and the laptops booked fields from tblcourses - they can be handled in the booked resources table.

Let me know your thoughts and I can build these into my suggestions

 

[mousemat]

CJ

Thanks for looking and your continued support.

I have added the extra date fields to the tblBookedCourses

It is a business rue tha we drive up the night before and return the day after, obviously having the extra data fields in the tblBookedCourses will highlight this.

I have added the trainers as a resource with a quantity of one for each trainer.

I can see the point of making the tblResources a history file but am unable to understand how to implement it within the table.

Updated database added

 

[CJ_London]

unable to understand how to implement it within the table

I'll show you how! I'll come back with some suggestions a bit later today

 

[mousemat]

CJ

Thanks

I'll look forward to it and of being further educated.

 

[mousemat]

CJ

Would I need two sets of dates in the tblBookedResources table?

If the resource is booked out for a course, and the trainer has an extra day either side, would we not just use the earliest and latest dates irrespective of the course dates?

 

[CJ_London]

You are tright - I had actually renamed them as coursefrom/to and resourcefrom/to but had also since realised this has effectively repeated the coursefrom/to data so now removed it - sometimes we go in circles!

Have also split the tblresources to get the history element, should have something for you in an hour or so

 

[mousemat]

CJ

Thanks.

Yes, we do go round in circles... I know I have been for ages now!!!

 

[CJ_London]

Took a bit longer than I thought, had some 'real' work to do

Attached is the updated db

I've modified tables, created new queries to demonstrate different things and modified the form and subform a bit

I have to go - got visitors - so full explanations will have to wait for now, but have a look through and I can explain more tomorrow.

 

[mousemat]

CJ

Thanks for that. I wasn't expecting you to be doing it that late into the day, although I know what you mean about 'real work' getting in the way.

I have had a quick look and it looks great, will need to have a look at the underlying queries and tables etc to understand what you have done.

Thanks

Ian

 

[mousemat]

CJ

Works well, but I seem to be having huge problems with dates.

Using the date picker to select the start and end date is fine, its when it transposes that information tothe sub form, the dates end up in american format.

Any way out of that?

 

[CJ_London]

Hmm,

I'm not getting that problem

try replacing "#" with """" at both ends of the code

"#" & Format(EndDate + IIf(Format(EndDate, "ddd") = "Fri", 3, 1), "dd/mm/yyyy") & "#"

 

[mousemat]

CJ

Yes that works for me now.

Is there a way to remove from the drop down list, the resources that are booked and have no free stock? For instance, if i am booked on a course Monday to Wednesday, I shouldn't appear in the list for a course to be booked on say Tuesday to thursday?

 

[CJ_London]

Yes it can be done - I produced a query called qryAvailable, you can modify this and use it as the rowsource for the combo box.

Things you will need to do to modify:
1. Add the tblResources to the query, linking to Stock.ResourceID so you can bring through the resource name
2. Change the [Enter... Date] parameters to refer to the main form start/end dates plus make the adjustment used in the start/end date after update event code
3. You will also need to make a further adjustment to account for whatever has already been booked to this particular training event - see qryFrmAvailable (which only matters if you are going to change an existing record)
4. Set the criteria for the available field to <>0