Bid query to show awarded department request

[scopes456]

I have a master query called [Master_Bid] that returns an employee request for department change . Each employee gets to pick four different departments label 1,2,3,4. Also each employee has their hire date attached to their request. The query is arranged from oldest hire date to newest.

Since each department has only 10 slots , I would like a query that will award each employee with their request until a department is full.

For example query go to the first employee using seniority and look at their first request, if that department is not full it will award it. If that department is full it would look at their second option and so on until the employee is placed.

After that it will go to the next employee in line and repeat the process.

When I view the query for each department is will show the employees that's were awarded for that department.

Thank you

 

[Galaxiom]

How have you structured the requests? Four fields or a related table with separate records for each requested Department and a Priority field?

 

[scopes456]

Galaxies Thank You for your reply.

I have it with four fields. Below is how the master_bid query looks. Each employee types in their department request. I how this helps. If you need addition information let me know.



BID_ID EMPLOYEE NAME | 1ST | 2ND | 3RD | 4TH | DATE OF HIRE

1 JOHN SMITH |100 | 200 | 150 | 050 |1/02/2011
2 DAVE JOHNSON |200 | 100 | 050 | 150 | 2/1/2015

 

[arnelgp]

can be easily achieved using udf and recordset.

 

[scopes456]

arnelgp i never used udf and recordset, any way you can give me an example how and where to use it

 

[arnelgp]

show me your db and i'll be happy to code it for you.

 

[scopes456]

arnelgp , thank you. i attached my database ,please let me know if you need additation information.

 

[arnelgp]

the problem is, on my part, i cannot do it using sql alone. first you have to count how many are already alloted for that department, if less than less than 10, add this record to that department, if not go to 2nd choice and do the same counting.

a put another field in [tbl employee] , AwardedSlot to put the the value, if not the first, choice of department.

run first to BidAssign() sub, to update AwardedSlot field then open your query [Master_Bid]. or to do both task, run subOpenQuery sub.

view the code behind in Module1 to see what's going on.

 

[scopes456]

arnelgp a millon thanks. thank you for all your help. I am wondering if each department would to have different slot amount, would you use this same appoach?

 

[scopes456]

say if department 100 has 15 spot, dept 200 has 5, dept 150 has 20 , dept 236 has 20 . can i use the same code and change the value?

 

[arnelgp]

the one i used was hard coded, its better that you create a table also for dept with fields like dept name/number and slots, so we can easily query this table.

 

[scopes456]

arnelgp, I created another db, using a department table that has a list of department name and a column with amount of available slots for that department.

 

[arnelgp]

the process is almost the same, you still have to run subOpenQuery.

 

[scopes456]

arnelgp a thank you a million times. I learn something new. Thank you for all your help.