Database Discussion - Access Query

Print Page | Close Window

Access Query

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=6230
Printed Date: 30 March 2026 at 7:16am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Access Query

Posted By: hockenpj
Subject: Access Query
Date Posted: 07 October 2003 at 8:09am

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:

Let me add a customer (FormNewCustomer)
Let me a new job (FormAddNewJob)
Let me allocate equipment to a job (FormAddEquipmentToJob)
Let me add a new venue (FormNewVenue)
Let me add new equipment (FormNewEquipment)
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.

Replies:

Posted By: anandsoft
Date Posted: 07 October 2003 at 8:59am

hello, What I understand is that you need to select a query based on time and date displayed. I think you need to do this programmatically. That is writing the code (or script) such that the right query is selected depending on the time and date.

-------------
http://web-hosting.anandsoft.com/ - web hosting http://website-design.anandsoft.com/ - website design seo optimization<

Posted By: michael
Date Posted: 07 October 2003 at 9:56am

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
)

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker