Availability Status Box showing the max value of resources tasked in a specific date

[Anehab]

I am having trouble figuring out how to calculate a value if depending on the date range, the resources needed for those dates, and if and how many resources are available to schedule on those days. I am using a data model that very closely resembles the one on the link below.

databaseanswers. org/data_models
/multi_resource_scheduling/index
.htm"

I just want to add a field in the "Resources for Appointments" table, called "Qty_Available". I want it to calculate the some of each resource scheduled within that date range. Getting the minimum qty of resources available within any given day. So an example would be.

Event----Start Date----End Date----Resource_Required-----Resource available
PArty----1/1/2016------1/20/2016---2 cars-----------------1 car


say I have 10 cars in my inventory but for that date range i can only give one because some/or all of my cars in that date range have a total of 9 cars already scheduled. therefore i can catch it before i am over scheduling my cars.

I need that to work with multiple issues. I have a database ready to be sent if you request it in order to get a better idea of what i am talking about.: pray2:

[jonh]

Data that constantly changes should be calculated on the fly, not stored in a table.


So in this case you probably want to calculate how many resources you have available with a query.


Get the id's for resources that are being used


Connect the tables you need in a query and filter the data to return the resourceID's where


resource type = 'car' AND
start date >= x AND
end date <= y AND
status <> 'cancelled' etc


Save that query e.g. 'qryResourcesInUse', then use that as filter..


To get records from tables where there is no match you use an outer join and check if values from the other table are null (empty).


You want to find 'car' resources that aren't returned by the query qryResourcesInUse



SELECT Count(1) AS TotalResources
FROM (
SELECT resources.ResourceID
FROM resources LEFT JOIN qryResourcesInUse ON resources.ResourceID = qryResourcesInUse.ResourceID
WHERE qryResourcesInUse.ResourceID Is Null AND resources.CatID='car'
)

[Anehab]

Data that constantly changes should be calculated on the fly, not stored in a table.


So in this case you probably want to calculate how many resources you have available with a query.


Get the id's for resources that are being used


Connect the tables you need in a query and filter the data to return the resourceID's where


resource type = 'car' AND
start date >= x AND
end date <= y AND
status <> 'cancelled' etc


Save that query e.g. 'qryResourcesInUse', then use that as filter..


To get records from tables where there is no match you use an outer join and check if values from the other table are null (empty).


You want to find 'car' resources that aren't returned by the query qryResourcesInUse

That makes alot of sense.
I am still new to this and I will try it out. I will post a reply about my findings.

Thank you so much!

[Anehab]

Im having a hard time using your code. I tried pasting it into SQL and using the logic to make my own query. Im sorry im so new to this.

[Anehab]

Got it to work I used the following code i came up with using your template.


Public Sub maxResourcesAvailable()
On Error GoTo ErrorHandler


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsFiltered As DAO.Recordset
Dim strSQL As String
Dim MyArray() As Variant
'''''New Dims
Dim txtStartDate As Long
Dim txtEndDate As Long
Dim maxResources As Integer
Dim days As Integer
Dim resourcesid As Integer
Dim x As Integer
Dim availableResources As Integer



'sets controls and variables
txtStartDate = Nz(Forms("Event Details").Controls!Event_Start_DateTime)
txtEndDate = Nz(Forms("Event Details").Controls!Event_End_DateTime, 0)
resourcesid = Forms![event details]![resource list].Form![Resource_ID]
days = txtEndDate - txtStartDate


'checks if a resource has been selected
If resourcesid = 0 Or Null Then

Set rsFiltered = Nothing
Set db = Nothing
Set rs = Nothing


Exit Sub

Else

For x = 0 To days

'create a record set to check if there are events with date range
strSQL = "SELECT SUM(Quantity_required) as resourcesRequested " _
& " FROM qryResourcesInUse" _
& " WHERE [Event_Start_DateTime] <=" & txtStartDate + x & "AND [Event_End_DateTime] >=" & txtStartDate + x & "AND [Resource_ID] =" & resourcesid

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)


'This line ensures that the recordset is populated

If Not rs.BOF And Not rs.EOF Then
If rs![resourcesRequested] > maxResources Then
maxResources = rs![resourcesRequested]
End If
End If
Next x

'This line gets the available resources from the resources table

strSQL = "SELECT Resource_ID, Available " _
& " FROM Resources" _
& " WHERE [Resource_ID] =" & resourcesid

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

availableResources = rs![Available]



x = availableResources - maxResources

Forms![event details]![resource list].Form![Available] = x & "/" & availableResources

Set rsFiltered = Nothing
Set db = Nothing
Set rs = Nothing


Exit Sub




End If






ExitSub:
Exit Sub
ErrorHandler:
MsgBox "There has been an error. Please reload the form.", , "Error"
Resume ExitSub









End Sub

[jonh]

awesome :)