X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Vba To Schedule Work By Daily Capacity, Access 2010    
post Mar 23 2020, 12:48 PM

Posts: 11
Joined: 5-October 16


I searched the forum for something to help me but couldn't find it. I chose the VBA forum bc I am assuming I will build this in VBA. If you have a function or query method to do this - please educate me.

I can provide mode detail if you need but in essence:
1. I have a record set of tests requested which have a date and priority. There are multiple test categories taht can be requested.
2. I have a calendar with dates that are working or non working days. This could be used to say we don't work Sat or Sun or we could have a training day or shut down and need to flag those days as non working - I use 1 or 0.
3. I have a capacity per day of how many tests I can do per day based on the test category. For instance, strength test can be 2 per day. Thermal test can be 1 day over 5 days.
4. I need to grab a record set from the test log, filter it by test type, prioritize it by priority order and then go through the working days to bucket the tests into days based on capacity for tat test.
5. For various reason including having visuals of capacity, my plan is to then log the date to start and end for each requested test.
6. Repeat this for each test type in the request.

It might be better to separate tests that have capacity of #/day vs the longer tests that are days / per test. Honestly, I can figure this out myself.

What I want to know is HOW would you do this?
I'm thinking:
1. I can pull a dataset from the test log and sorted by test type and priority
2. do next if loop through test types and nested within the test type loop I loop through the tests in order of priority.
3. This is where I need help. I'm thinking now I have to start with the first work day (today?) and check to see if the first test can be fit into today. If so, I can assign the date to that test. For the next test, check to see if I can fit it into the day (2 tests per day etc). If it can, I can assign same day to that test. If not, I need to go to the next working day in the calendar. BUT HOW DO I DO THIS? I guess for each loop I can track what day I'm on and to test if it is a working day I pull a second data set to check if that day is 1 or 0? Can I have two datasets open at once?
4. When done with that test (say strength test), I should have gone through each test request for the category "strength" and bucket the tests in order of priority into the working days by daily test capacity then output the start and end date for the test in teh test log record.

I hope this makes sense. I don't have many tables to show you as I'm just starting out but if you can help my develop the concept and work method I can do some work and ping the team back with more questions.

Go to the top of the page
post Mar 23 2020, 07:23 PM

UtterAccess VIP
Posts: 19,031
Joined: 29-September 03
From: Oklahoma City, Oklahoma


Note: You posted this in a VB/VB..net forum, not an Access VBA related forum.

The way I model this is to work backward. Start at the end. What is the final output required? Work backward from there until you reach the initial data entry.

Boyd Trimmell aka Hi Tech Coach ( HiTechCoach.com free Access stuff)
Microsoft MVP 2010-2015 - Access Expert
Inventory Control, Accounting, BPM, and CRM Software Developer
"If technology doesn't work for people, then it doesn't work."
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    1st April 2020 - 03:12 PM