March 17, 2010 at 5:14 pm
just a side note to grovelli:
The popup works for me on more.... maybe a javascript setting issue?
March 17, 2010 at 5:35 pm
:satisfied: I guess they got down to it
March 24, 2010 at 2:26 pm
Hi nvrwrkn2,
Doh - I made the last change without thinking it through! What is now happening is that for each unique combination of REQDATE and REQTIME it's inserting the top 3 records for that REQDATE and REQTIME combo if the weekday's a Friday or Saturday, and the top 2 if the weekday's anything else. It looks like you spotted this yourself from the code/SQL and through debugging it, so I'm probably telling you what you already know!
I've got a quiet and lazy Sunday afternoon this weekend, so I'll have another look at this from scratch then. As you can't get to a date dimension table, I'll show you how to create one as we'll need it to easily pick up the unfilled slots via SQL (SQL is almost always quicker and more efficient than using VBA in these situations). I'll have a think on my lunch break tomorrow to try to understand exactly what you're trying to do - I haven't up to now, but that's not necessarily through any fault of your own as I should have been asking questions to clarify that what I "thought" you wanted was actually what you needed! You'll find that most developers - myself included - are more keen on actually getting their hands dirty and coding as quickly as they can when they should have first made sure they have a clear understanding of the requirements. Personally, I blame my approach on my managers, both past and present, who've always insisted that they "need this yesterday", whereas if they'd given me an extra hour or so to plan and understand their requirements, they could have had it up and running today instead of next week... 😀
Expect a few questions tomorrow and maybe a "So am I right in thinking this is how you want to do this?" kinda post the following lunchtime that we can hopefully both agree on. Apart from this being an intriguing puzzle that has got me hooked, determining your correct requirements will be extremely valuable to me as experience - especially as it's outside of my normal work environment and the incredibly unreasonable expectations of the directors I deal with who make ever more frequently bizarre daily requests. :w00t:
We'll probably start on Sunday by putting something in place to determine the slots that aren't assigned, as this is absolutely key in the whole process. I'll dig out the VBA code I've got somewhere to populate a date dimension and explain to you what how we use the date dimension in figuring out the free slots; more importantly I'll explain why you need it in the long-term and how it can be used for reporting. Once this is in place and you understand what it's doing, then we'll have incredibly strong foundations for assigning the requests, and potentially any future projects you may have.
I'd expect that the code we've been using up until now will be redundant when we've finished, but by the sound of it, you've figured out what it was trying to do and it's pointed you in a few interesting tangents as far as VBA is concerned - if that's the case then it can't be said that it was code that was wasted.
Oh, and don't be afraid of opening your "big mouth" at work and getting more projects like this. I might be wrong, but I reckon you really enjoy this sort of thing. You'll be getting this one finished soon, and once you got it producing a few good-looking reports, you'll be wanting another project to keep you occupied. If it works well then don't be shy of promoting it - and yourself - and what it does.
Cheers,
RF
p.s. Grovelli's a nice guy isn't he? 🙂
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
March 24, 2010 at 2:50 pm
Thanks RF, maybe we should get together on Windows Live Messenger for a three-way webcam chat or make it four-way if we invite jd 60382 http://www.sqlservercentral.com/Forums/Topic807393-131-2.aspx#bm888908
"As you can't get to a date dimension table, I'll show you how to create one as we'll need it to easily pick up the unfilled slots via SQL (SQL is almost always quicker and more efficient than using VBA in these situations)."
but then you say, "I'll dig out the VBA code I've got somewhere to populate a date dimension and explain to you what how we use the date dimension in figuring out the free slots" So, which is it, VBA or SQL?
Giorgio
March 24, 2010 at 3:41 pm
Hi Giorgio,
We'd create the date dimension table either manually, or as this is a forum, it would probably be quicker to do it through DDL using an SQL statement. We'd then only use VBA to populate this table, and subsequently use SQL to deal with the records the table holds. We can also use the table via VBA - we're definitely not limited to SQL - but as far as using the data in the table is concerned, MS Access queries and SQL are pretty much the king.
As I know you've a decent amount of experience with VBA, you'll most likely understand this approach...
1) We'll loop through a range of dates (maybe from 1990 to 2020 to cover the past and the future, and also any other projects nvrwrkn might have to deal with).
2) For each date we loop through, we'll use VBA's various date-related functions to extract weekdays, week numbers, quarters, months etc. and then write these and the date to a row, and then move on to the next.
There may be a way to do it directly through SQL, but the population of a table like this with dates is a one-off process which executes in a matter of seconds via VBA, and as I've already written something before it seems the best approach is to reuse what I've already done. To not reuse something which efficiently does a task I need to do goes completely against everything I believe in when it comes to programming... 😀
I really like the fact that with a forum like this I can think before answering a question, and then most importantly evaluate my answer before clicking send. I've played enough games of chess as a teenager to know that I've made plenty of mistakes with snap-decisions (and I should say that my teenage mistakes weren't limited to just playing chess!), and even if postal chess is well before my time, I know I'd have really appreciated on more than a few occasions the thinking-space it makes available. Saying that, when we're near the end of nvrwrkn2's project I'll drop you a PM as it'll be nice for the three of us to chat and bounce a few ideas on how to report on the data. Until then I'll stick to the old-fashioned posts I'm afraid!
Cheers,
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
March 30, 2010 at 11:57 am
Ok, well I've been running around and around with this and I've come a bit closer with the following code, which doesn't use recordsets 😀
I have enough problems understanding the required syntax to write in depth subqueries and sometimes resort to just trying to use the query tool in access to come close and give me syntax, then I make adjustements.
So what I ended up doing is this:
Public Sub FirstRun()
Dim insertSQL As String
DoCmd.SetWarnings (WarningsOff)
insertSQL = "INSERT INTO Assignments " & _
"SELECT DISTINCT TOP 2 AvPool.Emp_No, " & _
"AvPool.Seniority, " & _
"AvPool.Birth, " & _
"AvPool.ReqDate, " & _
"AvPool.ReqTime, " & _
"AvPool.ID " & _
"FROM AvPool " & _
"ORDER BY AvPool.ReqDate, " & _
"AvPool.ReqTime, " & _
"AvPool.Seniority, " & _
"AvPool.Birth;"
DoCmd.RunSQL insertSQL
DoCmd.SetWarnings (WarningsOn)
End Sub
So that inserts the TOP 2 most senior people into Assignments for each date and start time. But it doesn't solve the problem of ensuring people only get one day a month.
For that I came up with AvPool (actually a query) with the following code:
SELECT *
FROM Requests
WHERE (((Exists (select * from Assignments Where (Assignments.Emp_no = Requests.Emp_no or (Assignments.ReqDate = Requests.ReqDate and Assignments.ReqTime = Requests.ReqTime))))=False));
This works, (mostly). The results produced chronologically show the top 2 senior people assigned to each start time, and they don't show up a second time because their emp_no appears in Assignments. I included Dupe ReqDate and ReqTime as well to prevent the INSERT from putting more than two people into the same start time, but it starts breaking down the farther it runs.
So everytime we insert selected records from AvPool, AvPool will only show those remaining requests WITHOUT a matching emp_no in Assignments and where a request already matches the same date and time. Only problem is we have 2 slots for each start time, not one. I think this is what's causing the problem.
If I could make AvPool change to reflect ONLY those requests where emp_no doesn't exist in Assignments and where the COUNT of ReqTime < 2 for the particular ReqDate of the request. I think that'd put me in business.
I've attached Requests table, Assignments table (results after the above code has run fully) and results of AvPool BEFORE running the assigning code above, AFTER the code runs the AvPool is empty.
It likely is a mess, but it's the closest I've come so far, it runs in around 8 seconds and it's inserting what I need, it's the AvPool isn't showing the correct requests the longer the code runs.
Let me know what you guys think...
March 30, 2010 at 4:29 pm
And... I was just stepping thru the insert module one at a time to see what's happening in the assignments table. The code works correctly until only one person can be assigned to a start time, then the module selects that one person AND the next one person for the next day and start time.
I've attached a copy of the assignments table where the error happens.... it's the last two records inserted from the TOP 2, one is for 06 FEB 10 at 20:00 and the last is for 07 FEB 10 at 14:00.
I think if I can get this solved in the module this may be over!
Lemme know what you think.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply