March 10, 2010 at 3:21 pm
Hello! Using Access but I get no help from access forums on this question so since it's 90% SQL I thought I'd come where the grownups play to ask.
I need help with how to code the following fields and need:
Empno, SeniorityDate, Birthdate, RequestDate, RequestTime, Choice#
I need to find the most senior person (with birthdate as tiebreaker for seniority match), look at his first choice of ReqDate and ReqTime and see if it exists in another assignment table. If it does not exist I need to insert all the above fields into that table then delete the entire request from the original table. I then need to repeat the process with the next most senior person's first choice and if available insert it and delete the entire record.
I'm having a logic and construction problem with how to accomplish this task.
It's function is to assign people to work overtime only ONCE per month, even though they put in several requests for the entire month. It would seem as though once a person's first choice is inserted I should then remove him from the table, but in the event there is a hole after I get through the entire table I need to check the everyone's second choice to see if they can fill the hole.
The more I explain this the simpler it seems to be but I can't get the structure down and the logic to make sure all spost are filled.
Anything else let me know, I'm getting burned out on the logic.
Thanks
March 10, 2010 at 3:33 pm
Please provide table def, some sample and expected result as described in the first link in my signature. It would help us help you.
The task itself sounds like a pretty easy one if you'd use SQL 2005 (and up).
But it's going to be challenging to do it using ACCESS tools only, I'd expect. We'll see...
March 10, 2010 at 4:50 pm
Hey thanks for taking a look.... if I understand your first link correctly you want to see the SQL for the table creation, some sample data and what I need as output. The SQL for table creation is a snap, I'm still trying to figure a way to provide some table data in a way that's presentable rather than just value,value,value. I'm not seeing a way to add to the post anything that looks like a table with data in it so I'm stumped on how you need the data presented.
As far as output it's the same structure as the source table in terms of fields and data, I just need only certain records and so many of them.
Sorry if this sounds stupid. I have only rudimentary skills at SQL coding although I do understand a fair amount of the capabilities I've never had to put them to practice so useage of the more powerful language is where I'm lost.
Here's how I'd make the table:
CREATE TABLE Requests
(Empno char(255),
Seniority_DT date,
Birth_DT date,
Choice char(1),
ReqDate date,
ReqTime date)
My problem at the moment isn't syntax so much as the best way to accomplish the task of hitting the above fields to determine a record that has the highest seniority (birthday as tiebreaker), the ReqDate & ReqTime and the choice, then comparing the Empno, ReqDate and ReqTime to an "assignment" table with the following structure
CREATE TABLE Assign
(Empno char(255),
Seniority_DT date,
Birth_DT,
ReqDate date,
ReqTime date)
and if the record exists, delete the record from the Requests table and continue on.
I guess I'm asking the program flow rather than the syntax to do it. The language and syntax I can find, it's the logic of how to get it done that escapes me.
I guess the format of the answer I'm looking for is something like:
You'll need 2 tables, one for requests the other for assignments. Pull the record with the lowest seniority, check if the date and time slot they want already exists in the assignment table and if so, delete the record and move on to the next highest seniority record and compare to insert or delete.
I could state the need in terms of how it's done on paper and state the end result if that would help, but I'm having a problem posting what it sounds like you need.
If you would be willing to look at the need and necessary results in "paper" terms it may help to advise best table structure and how to go about comparing the data in the tables to get the desired output.
That's a burden which almost amounts to advising me how to structure tables and recommending required programming steps to get the result.
If it's more than anyone wants to get into I completely understand and I'll keep trying my luck elsewhere while trying to devise different methods.
March 22, 2010 at 1:16 pm
I would like to know
(1) how do the employee put their request in? a sheet that they sign up and somebody will then type those data off the sheet? or you are going to have a web interface?
(2) how many assignments are available for each month? is it static or dynamic? how do you determine the availability of the assignments for further requests?
It sounds more like that you are generating the assignment table data with the data from request table.
March 23, 2010 at 6:04 am
I think I had another thread running that dealt with this, still haven't accomplished this task though. I did get a great deal of help from another user and that started me researching his code to see if I could tailor it to work for my application, which I haven't done yet :-D. In case it still interests you:
1. They fill out requests slips every month that show the days they are putting in for and the start times for each day they are putting in for. They can put multiple start times for each day as there's at least 2 starts each day.
2. Assignments are semi-static. On days 1 to 5 there are 2 start times with 2 people working each start, total of 4 a day for days 1 to 5. On days 6 & 7 there are 3 start times with 2 working each start for a total of 6 people working those days. It's not truly static because the number of assignments changes with the number of days in the month.
With the time spent so far, it seems that there's no need to consider which day of the week it is for assigning people because they can only request those late starts on days 6 & 7. There won't be a request in the table for the late start on other days.
It looks like the main problem really to pull the top 2 people in seniority who selected the same day and start time and if they haven't already been assigned a day before, assign them, if they have been given a day, goto the next person down in seniority and check for previous assignment, and so on until you find the top 2 remaining people who can be assigned. In the event that one of the two hasn't been assigned previously, they have to get that day, the other person gets tossed until a "clean" person is found by seniority.
I've tried simply just using subqueries but when the code runs it starts asking me for all the info it's supposed to be looking at a table for.
I've been spending more time working on the front end, data entry form, ensuring accurate data with minimal potential for errors. That's basically done and I can give them a report that shows every request for every day and start that they can use to manually assign people.
Having code that does it all for them is a bit far off, and I haven't had time to worry about that side of it since I had to get the front end working.
Thanks for looking.
March 24, 2010 at 5:56 am
I think I have got the idea on how the whole assigning process flows. Now come my following questions:
1. When will the employee put in request? do they all put in the requests for one time at the beginning of the month? or they submit their requests during the whole month? is there a deadline for the submiting time?
2. When will the assignments get assigned? It is ideal that the assigning process start after the requests are all in. otherwise, it may happen that one less senior employee gets assigned to certain time slot, while later a senior one puts in request for that time slot. This would violate your rule. Another question: WHAT if for a data/time assignment in a day , there is NO request found in the submited requests?
If you could provide the above two TIME POINTS (request-ending time and Assigning time) in the whole process, I think I would be able to provide a logic to accomplish the task.
March 24, 2010 at 6:38 am
well I'm all for taking any help I can get 😀
Every employee has until the 15th of each month to put in their requests for the upcoming month. They submit them on paper to the clerk who then will begin entering them all within a day or so after the 15th.
The assignment process happens after the deadline, otherwise someone with more seniority may get bumped by a filled position, you're correct there!
I know this seems to be mostly a reporting task, but it'd work better if an assignment table were generated and then I can report off that. In the event no one puts in for a particular date and time, or if only 1 person does the report can warn the user that a slot is missing.
Here's another problem with this as I see it now, if someone is assigned to a slot on 03 Apr 10, and they also chose 10 Apr 10, and a hole appears for 10 Apr 10, we'd need to check and see if someone else wanted that guy's 03 Apr 10 spot and if so, move the original guy over to 10 Apr and reassign his 03 Apr spot.
Yah, the more I think about the possibilities the more this is becoming a problem to just let a module or functions do all the assigning. I may have to look into a way of letting the user assign people manually by looking at a pool of available candidates that changes every time someone gets assigned, while at the same time let them see everyone's leftover picks to backfill any holes.
But if this is something you wanna take a whack at I'm all ears my friend!
March 24, 2010 at 11:51 am
Please see the attached.
March 24, 2010 at 3:22 pm
hmmmm.... lemme work on this and I'll let you know what I got!
Thanks sir!
March 25, 2010 at 6:42 am
I think I missed something in the above steps.
Here are the Modifications:
In step 2-a-ii, and 3-c; when updating the Assigned value, we should update the Assigned value of all the rows that have the same employee_ID as the request has just fulfilled for. This ASSIGNED value serves as a counter for how many times the employee has been assigned each month. ---Speaking of this, you may need a month column for this? or you create a requests table each month.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply