Resources to Schedule Query (2 Viewers)

[GSevensM]

Hi All,

I have a really big ask for some help!

I want to automate a resource plan against a forecast schedule. Specific details are the forecast is how many computers are to be deployed on a given day and how many people I need to make that happen over a period of time.

The reason for the automation is that the schedule is very fluid and a lot of effort is required to calculate resource requirements on a daily basis.

I've been trying to work out how to do this in Access and I am stumped. I don't even know if it is possible.

I have a Schedule table with the columns: date, day, location, number of deployments, number of logistic resource, number of rollout resource and number of floorwalker resource.

The resource calculation works on a ratio basis:

Logistics resource is 1 to 40 deployments, required the working day before.
Rollout is 1 to 20 deployments on the day of the deployment.
Floorwalker is 1 to 60 deployments on the day of deployment and following 2 days after deployment.

So an example schedule and resource forecast would look like

Deployments No of Logistic No of Rollout No of FWs
Day 1: 0 2 0 0
Day 2: 60 1 3 1
Day 3: 40 0 2 2
Day 4: 0 0 0 2
Day 5: 0 0 0 1

Logic to the above is:

Day 1 - Logistic resources work on a 40 to 1 ratio. As there are 60, there would be two required as you can't have 1.5 people!

Day 2 - 1x Logistic resource is required to prepare 40 on Day 3. 3 rollout engineers are required based on 20 to 1 ratio. 1 FW is required based on 60 to 1 ratio.

Day 3 - 0x logistic resource required because there are no more deployments that week. 2x rollout engineers are required to rollout the 40 devices. FW is where it gets complicated. There are 60 devices from the previous day, so 1 FW is required for that. Another 40 devices means that there are now 100 devices to support, so two FW are required.

Day 4 - Just the two FWs supporting 100.

Day 5 - 60 of the 100 deployments are now outside the two day support window, leaving just 40 to support. So now only one FW is required.

If I can get he resolution to just one of the above types of resources I can probably figure it out for the others.

Is what I am trying to do achievable? And if so, any advice is greatly appreciated!

Thanks,

G

 

[jdraw]

How do you do this now?
What are the issues?

You may get some ideas from some free data models for Resource Scheduling.

If you can review your requirements and create a model and, using some test data and test scenarios vet the model (adjust as needed) to ensure it works in concept, then you have a blueprint for database design.

Good luck.

 

[GSevensM]

How do you do this now?
What are the issues?

You may get some ideas from some free data models for Resource Scheduling.

If you can review your requirements and create a model and, using some test data and test scenarios vet the model (adjust as needed) to ensure it works in concept, then you have a blueprint for database design.

Good luck.

Hi,

Thanks for the response.

Currently I export the table from Access into Excel, manually work out the resourcing requirements and then import back into Access.

I know exactly how I need the resourcing to work, I just can't figure out what queries I need to make it happen!

 

[jdraw]

Can you make a list/point form to show?
I know exactly how I need the resourcing to work

Perhaps you could provide a copy of the Access you have now.(zip format)

 

[GSevensM]

Can you make a list/point form to show?
I know exactly how I need the resourcing to work

Perhaps you could provide a copy of the Access you have now.(zip format)

Unfortunately I can't send the DB due to the customer data in it. Is there any easy way to insert a table on this forum? I notice the example in my OP the formatting got screwed up.

I've attached a screenshot of an example as to what I am trying to achieve, using the ratios set out in the OP.

 

[jdraw]

You know your environment and need intimately --readers do not.
Perhaps someone else understands your need, but I'm not following the terminology.

Can you give more description (perhaps an example also) re
logistics resource
deployment
rollout engineer
floor walker
support

Do any of these have multiple "roles"?

Via Google:

In its IT context, deployment encompasses all the processes involved in getting new software or hardware up and running properly in its environment, including installation, configuration, running, testing, and making necessary changes. The word implementation is sometimes used to mean the same thing.

Rollout is an informal term for the introduction of a new product or service to the market. A rollout often refers to a significant product release, sometimes accompanied by a strong marketing campaign to generate consumer interest.

Logistics is a specialized field of its own comprised of shipping, warehousing, courier services, road/rail transportation and air freight. ... Examples of supply chain activities include farming, refining, design, manufacturing, packaging, and transportation.

Floorwalker - a person employed in a retail store to oversee the salespeople and aid customers

 

[GSevensM]

You know your environment and need intimately --readers do not.
Perhaps someone else understands your need, but I'm not following the terminology.

Can you give more description (perhaps an example also) re
logistics resource
deployment
rollout engineer
floor walker
support

Do any of these have multiple "roles"?

Thanks for the prompt and regular responses, they're much appreciated! All the criteria is set out in the OP but I'll try and rephrase the requirements below.

There are three types of distinct resources.

Logistics - Being there for the arrival of hardware, unboxing, powering on and making sure they work etc.

Rollout - The engineer responsible for handing the device to the end user and taking them through the logon and helping them set up their device.

Floorwalkers - These guys will be roaming around the Office offering support to any newly deployed user who requires it.

The amount of resource required is ratio driven against the amount of deployments scheduled.

Logistics = 1 resource for every 40 devices.
Rollout = 1 resource for 20 devices.
Floorwaker = 1 resource for every 60 devices.

Logistics staff are required the business day before the scheduled deployment date.

The Rollout staff are required the day of the scheduled deployment date.

The Floorwalkers are required for the day of the scheduled deployment date and for two additional days after the scheduled deployment date.

So with regards to the example in the attachment. There are two sites being delivered too. One may be in Canada, one may be in the UK for example but that's irrelevant to this problem.

So for just Site 1. Site 1 has 60 deployments scheduled on the Monday.

The logistics resources need to be there on the Friday (one business day before). There are 60 devices and the ratio is 40 devices per one logistics resource. 60/40 = 1.5 resources. Well we can't have half a resource so that needs to be rounded up to 2 resources. That is why there are 2 logistics resources required at Site 1 for the Friday.

On the Monday we need rollout engineers to perform the rollout. There are 60 devices and the ratio is 20 devices to one rollout engineer. So we need 3 rollout engineers that day. We'll also need floorwalking to start that day. The floorwalker ratio is 60 devices to one floorwalker. So we'll need a floorwalker in addition to the 3x rollout resources. We've also got 40 deployments on the Tuesday. So we'll need 1x logistics resource to unbox, test etc. So in total on the Monday we'll require 5 resources. 1x Logistic, 3x Rollout, 1x FW.

On the Tuesday, Site 1 has 40 deployments scheduled. So we'd need 2x rollout engineers. There are no deployments scheduled for Wednesday so we don't need any logistic engineers. However, our total number of devices requiring floorwalking is now 100 (60 from the day before, plus the 40 today). So now we need 2 FWs to support the 100 (60 to 1 ratio).

On Wednesday there are no more deployments, so we just need the 2 FWs as we are supporting 100 users.

On Thursday, we no longer need to floorwalk the 60 deployed on Monday (floorwalking support only lasts two days), but we still need to floorwalk support the 40 deployed on Tuesday. So we need 1 floorwalker.

Does that make sense?

 

[jdraw]

Yes that helps.
So these resources are drawn from your Employee roster?
Is each Resource limited to a specific role? That is a FloorWalker is not (not ever) a Logistics Resource, nor a RollOut person.

Are there different devices that have different requirements? Could there be?

It seems doable to me, but, as always, the devil is in the details.

You have processes that require resources. Those processes can be "invoked" on predetermined Dates. By knowing the forecast Date of a specific Process(deployment) and the relationship of associated processes (Logistics support, Rollout support and Floorwalker support) and the related stats (timing, Number of specific resources/device deployment..), seems you have all relevant pieces.

I would make a model of potential tables and relationships, then using some representative test data and scenarios, "work the model" to see if it "works" --adjust and retest as necessary. I would not jump into a physical database until I had vetted the model.

Good luck with your project.

For consideration:

Peter Hibbs on UtterAccess has a Holiday Planner that may provide some insight re presentation. I don't use it, but it is often referenced. Again, this is for reference only, and I strongly advise the model and vetting process to get a blueprint for your database design.

 

[GSevensM]

Yes that helps.
So these resources are drawn from your Employee roster?
Is each Resource limited to a specific role? That is a FloorWalker is not (not ever) a Logistics Resource, nor a RollOut person.

Are there different devices that have different requirements? Could there be?

It seems doable to me, but, as always, the devil is in the details.

You have processes that require resources. Those processes can be "invoked" on predetermined Dates. By knowing the forecast Date of a specific Process(deployment) and the relationship of associated processes (Logistics support, Rollout support and Floorwalker support) and the related stats (timing, Number of specific resources/device deployment..), seems you have all relevant pieces.

I would make a model of potential tables and relationships, then using some representative test data and scenarios, "work the model" to see if it "works" --adjust and retest as necessary. I would not jump into a physical database until I had vetted the model.

Good luck with your project.

Thanks. The current format and modelling for the resource assignment works, I just don't know how to automate it using a query, as my Access skills aren't that strong. Worst comes to the worst I can probably muster something up in Excel using IF and LOOKUPS, but I'd much rather just get the output directly from Access than having to export to an Excel template and then re-import again. Still beats doing it manually though!

With regards to your other queries, assume that the resources are unique and can't do each others jobs. We will be using a resource partner to bring in the resource and we need to explicitly tell them what resources are required on what day.

 

[jdraw]

If you can post a copy --even a mock up with anonymized names etc -you may get more focused responses. A single query may be overly hopeful, but devil and details again.

The current format and modelling for the resource assignment works

If you have a model of your set up for assignment, that would be helpful to readers.

Re your earlier question about posting a table.
It is probably easier to create a new database, by importing a few tables from your current database. If you have an Employees table, then in the "new" database, change the Names to Porky Pig, Polly Dactyl etc. That is , anonymize the "mock data". You must import related tables if this new database is to be meaningful to a reader. But you can re-value/change other fields to keep things anonymous. Things like --Employee Names, Customer/Contact Names, Cities,etc.

Again, good luck with your project.