Access Query

Hi,

I am in the process of developing an Access Database for Resource Scheduling project I have.  I am not going to use a web interface, just run it straight from Access.

The problem I have at the moment is trying to create a query that will change depending on the times and dates.

The database is quite simple other than that. I will have the following table strucutures:

TableCustomers
CustomerID
FirstName
LastName
etc...

TableJobs
JobID
JobName
CustomerID
VenueID
JobStartTime
JobEndTime
JobStartDate
JobEndDate

TableVenues
VenueID
VenueName
VenueDescription

TableJobDetails
JobDetailsID
JobID
EquipmentID

TableEquipment
EquipmentID
EquipmentTypeID
EquipmentMake
EquipmentModel

TableEquipmentType
EquipmentTypeID
EquipmentType

I will have a forms that will:

1. Let me add a customer (FormNewCustomer)
2. Let me a new job (FormAddNewJob)
3. Let me allocate equipment to a job (FormAddEquipmentToJob)
4. Let me add a new venue (FormNewVenue)
5. Let me add new equipment (FormNewEquipment)
6. Let me add a new equipment type (FormNewEquipmentType)

With regards to my problem metioned above, FormAddEquipmentToJob is the most relavant. This form will be a form with the information about the equipment and a subform where I can allocate any equipment that is available during the time and date period outlined in the top part of the form.

So the query required to do this needs to take the dates and time from the top part of the form to create a list of available equipment.

If you have any suggestions that would be great!

I am sure that I am missing something in the way the table have been strucutured.

Should not be too hard to do, basically you could use the raw script below and just adjust it to your needs, hope it helps you on the way...

Select EquipmentID, EquipmentType, EquipmentMake, EquipmentModel from TableEquipment e 
INNER JOIN TableEquipmentType t ON e.EquipmentTypeID = t.EquipmentTypeID
WHERE NOT EquipmentID IN 
(
Select EquipmentID from TableJobDetails d JOIN TableJobs j ON d.JobID = j.JobID
WHERE NOT 'thedatefromyourform' BETWEEN j.JobStartDate AND j.JobEndDate
)