March 12, 2010 at 3:15 am
Hi,
I've amended some of the SQL (removed the DMax) so you might want to replace the code and see if that works. I created a quick and dirty couple of tables to test it, and it did work fine but obviously that's no good if it's not working on yours!
A few things:
1) Does your code compile OK? You can test this by going into a module and choosing Compile from the Debug menu.
2) Just to check that the SQL used in the constant is correct, copy the following into the SQL query pane of a new query and see if it executes OK.
SELECT Requests.REQDATE
FROM Requests
WHERE Requests.REQDATE>Nz((SELECT Max(Assignments.REQDATE)
FROM Assignments))
GROUP BY Requests.REQDATE;
3) What date format do you use - dd/mm/yyyy (UK) or mm/dd/yyyy (US)?
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 12, 2010 at 6:39 am
Morning RainboFfolly,
OK... changed dmax to max, got same error number but:
Wrong number of arguments used with function in query expression "REQDATE>Nz(Max('REQDATE',Assignments')
Clicked compile and got no response. Never tried it before so I'm guessing it went OK.
Date format in fields is dd-mmm-yy (medium date)
Debugger stops at same place as before.
In case it matters, the dev machine is Windows 7, deployment machine is XP
Changed to short date structure and got farther, INSERT INTO statement regarding Emp_no run-time error....gonna check that
Working now, results a bit off:
Same employee showing up twice in same day at different times. It's most important that the only time the same person can show up more than once is if no one chose that date and time, and then the assignment has to be by seniority again.
The idea being to skip a request if it's already been assigned or the employee has already been assigned a day. Then if there are any date & time slots not filled, go back through requests by seniority and plug in any matching employees, dupes are OK this time around.
Monstrous work though! Been trying to sift through the code to see what you're doing...
hmmmm.... going over this again... lemme see if I've gone through every name.... that might explain it
OK. I had a total of 15 people in the requests table, and only 14 showed up in assignements. The duplicates are from no one else choosing, it appears.
One thing though as that the person with the lowest seniority never got a day and doesn't show in the assignments table. Since there are more than 15 dates and positions available this lowest guy should have been picked once at least the first time around.
March 12, 2010 at 7:38 am
yep.... no matter how you change seniority dates, the last person never gets assigned a day.
It looks correct otherwise... I'll have better info after I go through a real set of requests with all 212 people.
March 12, 2010 at 8:25 am
Working through your points...
Aaaaahhhhh! You should have re-copied the whole of code from the previous post as I made a few other minor changes to the SQL, and there was a bit more to it than just changing DMax to Max...
If you want to try replacing all the code you've got with all the code in my edited post (second from top message) then we can rule out a couple of other things....
You should only get a message if there's a problem when you compile, so no news was definitely good news as far as getting no response is concerned...
I've never really bothered using the Medium Date format before, so it's interesting that changing to Short Date seemed to help in some way...
You might want to check exactly which short date you're using - if you're using UK (dd/mm/yyy) then I'll recommend you make a quick amendment to the code so it's formatted correctly when used in the WHERE clause of the cstrAppendSQL constant...
I'm not sure how to sort out the problem with the last person not being assigned. I'm glad that it's starting to coming together, but the real proof as you say will be when you test it with some real requests...
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 12, 2010 at 9:53 am
ok.... I recopied the edited code, added Requests.STAR back in 🙂 and ran it again.
Still leaves out one employee, BELL, even though he's not the lowest in seniority.
I've attached a copy of the requests and assignments table for you to see what I'm talking about.
MARTINEZ is taking up all of BELL's choices when BELL should get one of them.
It's only a hair away from perfect it seems!
I thank you again for all the time you've spent.
*edit* after looking at the BELL thing I notice he chose 3/1/10 at 18:00 when no one else did and he wasn't assigned, or the record wasn't inserted into the assignments table.
March 12, 2010 at 4:25 pm
Since I obviously didn't know what a connection object was for let me ask another possibly pointless question:
After re-reading the comments and related code I'm wondering where in the code we're taking the starting time of REQTIME into account? I may be missing it, but it looks like it's looking for specific dates and then pulling the top X for those dates.
If we assign 2 people to each starting time should we be looking for a specific date and a specific start time as well since we're assigning TOP 2 people to each start time rather than a date?
I can't see where it's assigning 2 people to a single start time, to beat a dead horse again.
Still seems to be working tho...
doh... I noticed something else....
Comparing the comments to the code where it determines the value of X for the TOP statement, it appears as tho it sets X to 2 for Sun & Sat and 3 for anything else. Should that be Case 6,7 for Friday & Saturday?
Fri & Sat are the days where there are 3 start times during the day.
I'm likely wrong... but I got the weekend to stare at it.
So, I changed it to Case 6,7 x=3 and Case Else x=2
Once again I'm not understanding the function because that killed off the second employee for every start time :hehe:
edit: hmmm..... I cut and pasted the code back into the module and I'm getting the same missing second person for every start time...
March 12, 2010 at 6:37 pm
I've just got back from the pub and as I didn't drink too much (HIC :-P) I couldn't resist checking up how you got on - I've gotta say well done!
Since I obviously didn't know what a connection object was for let me ask another possibly pointless question:
Think of a connection object as the link to the source of your data; it could be an MS Excel spreadsheet or workbook, it could be a text file, or in your case it's an MS Access database. I remember when I was learning ADO that the relevant help in VBA was really ambiguous when I was trying to work out how to link to the MS Access database I was developing in. In reality it's ludicrously simple; when we say "CurrentProject.Connection", what we're actually saying is "set the connection of this recordset to the same connection as the current database" and, lo and behold, we're in the database we're using. A recordset object needs a connection passed to it so it knows where to get the data from; by telling it "CurrentProject.Connection" we're telling it that the SQL we're passing to it should be applied to the current MS Access database that the code is running in. No connection string is necessary - just "CurrentProject.Connection". Press Ctrl-G and type in ?CurrentProject.Connection and hit Return - you'll then see what it's doing for you.
After re-reading the comments and related code I'm wondering where in the code we're taking the starting time of REQTIME into account? I may be missing it, but it looks like it's looking for specific dates and then pulling the top X for those dates.
If we assign 2 people to each starting time should we be looking for a specific date and a specific start time as well since we're assigning TOP 2 people to each start time rather than a date?
I can't see where it's assigning 2 people to a single start time, to beat a dead horse again.
It doesn't - well spotted! I based the code on some of your original SQL and didn't read too much further; if I had I would have realised that it wasn't just the REQDATE that was unique, but what you wanted was a combination of both it and also the REQTIME. I have to admit it's pretty darned satisfying when you try to help someone and they actually make the effort to solve their own problem rather than expect someone else to do it for them!
doh... I noticed something else....
Comparing the comments to the code where it determines the value of X for the TOP statement, it appears as tho it sets X to 2 for Sun & Sat and 3 for anything else. Should that be Case 6,7 for Friday & Saturday?
Fri & Sat are the days where there are 3 start times during the day.
Again, I didn't read your messages properly and assumed that weekends were treated differently to days in the normal working week (Monday to Friday). You read my comments in the code and understood that it wasn't doing what you wanted it to and questioned it. I'm quite evangelical about the value of comments so I'll most likely say something about how important they are before I finish... 😉
Still seems to be working tho...
It seems like it is, but the reality is it that isn't; what it's doing is just grouping by REQDATE, and REQTIME is not being taken into consideration.
I'm likely wrong...
You're not - you're absolutely right and all credit to you for discovering that you are, but you should lose a few points for lack of confidence when you said "I'm likely wrong" instead of "I'm likely right". Although to be honest I reckon you were just being polite. 😀
...but I got the weekend to stare at it.
Nah... not really an issue. Once you explained clearly that REQTIME was as important as REQDATE it was a quick job to amend the relevant SQL. I've played with the code and data and Mr. B is now assigned where he should be.
Anyway,back to what you said earlier so I can stand on my "Vote For Comments" soapbox...
Comparing the comments to the code...
I might get shot down by a few people for saying this, but I know from experience that in the "real world" that comments are THE most important thing in any code that you write. If you're learning then they're incredibly important when you go back to your code (I know this from experience). If you're maintaining someone else's code then they're essential. I could go on - but I'll possibly end up ranting - but you should always realise and try to remember that no matter how knowledgeable you are, the person who one day has to look after your code may not have your experience. You may not have understood exactly what the code was doing, but you've questioned what you should have via a combination of comments and code - think how much harder it would have been for you without the comments. Hopefully - if you weren't already aware - you now understand the value of them... 🙂
I'll post the amended code tomorrow that sorts out the REQTIME issue, but there's a caveat - you'll have to answer the following easy question about the "Case 1, 7" line of code, i.e. If Saturday is "7" and Sunday is "1", and you want to use Friday and Saturday, then what numbers should you use?
Oh, and there's still the massively important question of when the code should actually be run, and if the existing data in the Assignments table should be taken into consideration or just be replaced.
Cheers,
RF
p.s. I know that you can do what you want to do with just T-SQL (SQL Server's SQL) if you had an SQL Server backend, but because of MS Access's limited version of SQL you have little choice but to find an alternative.
_____________________________________________________________
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 12, 2010 at 10:45 pm
sorry to hear you got home early 😀
I didn't understand why a connection was needed when operating inside the same database application, I just assumed that part was taken care of within the Access application itself. I need to learn much more about VBA to be effective with this application, since it's the only one I'm allowed to use now. I really need more SQL under my belt before I consider tackling VBA.
I was given about a month to get this done, so you know I needed someone else to help solve this since I only have rudimentary SQL and no VBA. I surely hate that I have to ask for help so you can bet that I need to learn how someone else accomplished the task. Not just for my own edification but to make better use of my time at work by working on other applications. Doing these kinds of projects comes up for me once every couple years so I don't have repeated exposure to coding, I'm only required to generate simple reports off of a datawarehouse and it's always the same tables I'm looking at.
Comments, I comment everything in my reports because they end up being distributed to others without my knowledge, and I could easily have 25 to 30 people calling me up to ask about a report formula to correct for their situation. I comment and generate the report in the easiest format possible and I repeat the format every time so people get used to where to look for the things they need to change.
As far as the date thing, I don't deserve points for that answer because I use it in crystal almost constantly 🙂 Sun to Sat is 1 to 7, so we'll need Case 6,7 for Fri,Sat and else for 1 to 5. I will go grab a beer tho as a reward before they're gone!
I still have plenty of time to at least understand this piece of code, so don't waste your weekend looking at it! I'm off Fri, Sat and I have little else to do and this is a problem that bugs me so I'm staring until I understand it.
I'll leave VBA as a "it just works" part of the code until I get a better understanding of what the entire piece does. Even then I still need to get up to speed on SQL so until I feel at least somewhat satisfied on where I'm at with SQL I'll leave VBA alone for now. I gotta learn the analytical use of SQL rather than just SELECT statements.
Back to BEGINNING QUERIES FROM NOVICE TO PROFESSONAL!
*EDIT Couldn't help myself, changed Case dayofweek values, added ID to top SELECT so I can use a simple query on it to show which picks didn't get into the assignments table, changed STAR domain to number to remove leading 0's... I'll fix the original data importer to reflect the change.
March 14, 2010 at 7:33 am
Decided to burden the network and external database for the personal information at report time. Revised the requests table, contains only EMP_NO, REQ_DATE, REQTIME. Seniority and Birth can be obtained from linked external personnel table as Personnel.SENIORITY_DT, Personnel.BIRTH_DT using EMP_NO field.
I'm thinking I need to pull each date, specific time, check a temp table for existing EMP_NO and if not exists, insert the request for that date and time, insert that same EMP_NO into the temp table and then repeat, limiting the number of people to 2 for each start time per day, and limiting the number of times a person can be chosen. Then I need to compare the assignment table dates to see if there are any missing compared to the requests table and if so, fill in those missing spots again by seniority.
I'm thinking there's no need to check the day of the week because people either request the extra start time or they don't. I can take care of missing 3rd shifts on Fri & Sat in the report side.
Every day of the month has to be accounted for and I should be able to handle missing days or start times in reporting.
March 16, 2010 at 3:36 pm
Hi,
Sorry I didn't get back on Sunday - I blame the lack of a hangover! As promised, I've amended the code in the previous post so it handles REQUEST_TIME, and although it won't meet your new requirements, it should definitely work in initially populating your Assignments table. The main changes you should look at are how it deals with request times in the SQL in the two constants, 'cstrDatesSQL' and 'cstrAppendSQL', and lower down in the "Select... Case..." block. The Friday/Saturday issue is covered, but you will need to amend it to reflect your changes you mentioned in your last couple of posts. I've saved the amended code as a text file and attached it to this post so you don't need to go looking for it.
A few really quick thoughts on your last post about inserting records into the Assignments table and filling any gaps:
If you have access to your data warehouse then you will most likely be able to link to a date dimension table (this will be a table holding dates, and probably called something like "dim_date"). This table will come in extremely useful in determining which dates haven't any assignments if you relate it to the Assignments table. Speak to your manager or DBA if you're unsure if you have access to it - if you can't get access to it I'll give you a quick bit of VBA to create and populate one of your own in your MS Access database.
As far as request times are concerned, they appear to be a limited number of fixed slots that are allocated to each day and are the same from day to day; there may be exceptions depending on the day of the week, but for the sake of simplicity we'll say that they are the same regardless of the weekday. If this is the case, then you might want to consider creating a small table with a few rows to hold these time slots. A Cartesian query (i.e. one with no join between the tables) with this time-slot table and the date dimension table should provide all possible combinations of time-slots on all of the dates in the date dimension. Depending on the number of rows in the date dimension table, your requirements - and also performance considerations - I'd recommend you filter it so the query only returns relevant dates (e.g. you may only want to deal with dates no later than 6 months in the future and not before 2008). Once you've created this query, you should then be able to write another query with an outer join between your Cartesian query and the assignments table to determine which time slots have not been assigned to anyone.
Cartesian joins are normally the spawn of all that is evil, and you should generally avoid them like the plague (the very word normally makes me hide behind the couch!), but I think this could be one of the incredibly few occasions when using one may be applicable. Do a search here on the word "Cartesian" if you're uncertain about what they are and what results they return.
When you get everything working the way you want it to, I'll make a few suggestions on "normalising" your two tables. There's no real need to worry about it until then, but normalisation is very important in databases, and if your little database is normalised properly you can definitely be proud of it as a self-contained project.
Oh, and don't apologise for asking for help - you're making a commendable effort yourself and I appreciate that (otherwise I wouldn't be offering my 10 cents / tuppence worth of advice!). If you can prove that you can do this task successfully, then who knows - the opportunity to do this kind of project may come up more often than once every couple of years. Although you may live to regret it if they come up every couple of weeks... 😀
Good luck!
RF
p.s. I'm glad I'm preaching to the converted when it comes to comments! As a VB programmer you wouldn't believe the unmaintainable monstrosities I've seen in my time... :w00t:
EDIT: Looking at your last couple of posts, I'm certain that the current code and the SQL won't handle limiting the number of times a person can be chosen on a day. But I am certain that apart from this you can do everything else, and once you understand exactly what you're doing and can explain it, then you can ask one of your SQL developers for their advice on how to do it with some confidence.
_____________________________________________________________
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 16, 2010 at 4:23 pm
There's always next weekend to look forward to I guess 🙂
Hey, I thank you for any time you've spent with someone else's dilema... it's more time spent doing something for free for someone else and you do it all day, so your time on my problem is most definitely appreciated.
I've been looking into recordsets to see how they function, from what I read it seems like that's what was necessary to gather all the criteria needed to insert. I recall modding your original cstrAppendSQL constant to the way you have it now... I thought I could shove two fields into a constant like this, but when I ran it I got the same output as without the reqtime field being in there. What I didn't do was add the second append for time. Then I though maybe two separate constants where necesary but couldn't figure out how to open the recordset to shove both fields in there.
Anyhoo... I'm getting a compile error now... I think I got the same thing before:
User-defined type not defined, highlighted at Dim rst As.ADODB.Recordset.
I'll mess around with it a while and see if I can understand the changes.
I have access to most of the tables in the DWH, I'll check to see if there's one holding all dates. The original link I made was to pull all the names & things rather than store them in a local table.
For the separate dates table locally, not a problem... I'm thinking you mean grabbing them from a full dates table to populate as otherwise I guess I could make it myself for this year.
Table structure at this point is entirely changeable to whatever works best. At work today I restarted the requests table with a single date, single emp_no and three fields, one for 14--, 1800 and 2000 and made them Y/N. That simplified data entry to check boxes for a single date and used the picker for the date itself. This limits actual operator typing to a minimum and would help reduce typing errors for dates, etc.
Table structure is loose and can be whatever it needs to be to get it done. With the cartesian thing, I've only really worked with tables from a reporting standpoint and a cartesian product was never useful and only caused massive traffic and repeat data.
For table size, I only planned on holding the requests for the upcoming months, every month the table would be wiped and we'd start over. We didn't plan on keeping request histories.
As I look at the current table data, there's a ton of redundant info in there, partly why I thought I reduce it's size and use 3 separate fields for y/n to reduce redundancy.
If there's a more amenable table structure that would make this routine easier to work I'm all ears.
I'll play around with the new code and see what's happening.
Thanks again, keep looking forward to next Friday!
*edit.... man am I not thinking today. There's no way to correctly report using y/n for each field.... I can't group the results then.... dammit.
March 16, 2010 at 6:00 pm
nvrwrkn2 (3/16/2010)
Anyhoo... I'm getting a compile error now... I think I got the same thing before:User-defined type not defined, highlighted at Dim rst As.ADODB.Recordset.
Just a quick one, but there's probably two possible reasons for this:
1) From the error you quoted, the most likely reason is that the reference to ADO isn't set. As I mentioned in an earlier post, check that "MS ActiveX Data Objects Library..." is referenced from the References option on the Tools menu. If you've copied everything into a blank database it won't have copied the references - I do this everytime and forget myself and wonder why things don't compile before kicking myself. This may explain why I have a permanently bruised ankle and shin!
2) The line of code you quoted has a syntax error. It may be the way you typed it, but there should be a space between "As" and "ADODB" and no full-stop. It should read:
Dim rst As ADODB.Recordset
Of course, it may be that I accidentally typed it wrongly myself when I posted the code... 😉
A quick tip is that when you make a few changes in VBA, always select Compile from the Debug menu - this will show you any obvious syntax errors before you execute your code. I personally do this instinctively every couple of minutes so I can resolve mistakes before I'm swamped with them.
On the Cartesian join point, if you restrict the number of records returned by the dates dimension table from the server - and you only have a few rows in your local time-slots table - then traffic shouldn't be bad and your repeat data in this case is actually the data you want anyway. Admittedly, the more that I think about it, I'm not 100% sure how exactly the processing and traffic works with a local MS Access table and an SQL Server table in a query like this, so I'll do a quick test tomorrow at work to check as I REALLY should know this!!! Maybe I'm just getting a bit excited as I can count the times I've had a justifiable excuse to use a Cartesian join on the fingers of one and a half hands, and I think this could be the eighth time... 😀
If traffic really was an issue, I think you'd be justified in having a local "dates" table as it's static and also relatively small, plus performance would be improved. You could even start implementing the IT-friendly "International Fixed Calendar" of 13 months a year consisting of 28 days each...
http://en.wikipedia.org/wiki/International_Fixed_Calendar
I love classical history, but Julius Caeser seriously screwed up in 46BC when he had the opportunity to to sort out the Roman calendar... 😀
I'll post on one of the other SQL forums here tomorrow to ask peoples' opinion, as it's so rare that Cartesian joins are deliberately used that I'm intrigued if this could be a valid case - I'm sure others will be interested too and I'd love to hear their opinions and advice.
RF
p.s. As this is a public forum and people with different skill levels may view posts, I always prefer to assume "less" instead of "more" knowledge when answering a question, but I'd hate to think in some cases I was stating the obvious to you or worse run the risk of being patronising! What's your background and your role/responsibilities? If you understand a Cartesian product and it's implications then I may have underestimated you from your intial couple of posts. I'd like to see this through to the end as it's a realy interesting problem that I can learn from myself (and I can see that I might one day have to face something similar), so if I know your skillset it'll mean I can tailor any replies accordingly and not dumb them down or make them over-complicated.
_____________________________________________________________
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 17, 2010 at 3:12 am
That's been a fantastic exchange. Congrats fellows!!:-)
P.S. how come, when you click the "More..." link to access additional smileys, you get the message:
The following error occurred...
Sorry the application encountered an unexpected error. Information about this error has been logged. If you continue to receive this message please contact the board administrator.
March 17, 2010 at 8:52 am
It's mostly fantastic one way 😉
I really don't have much to contribute except my mistakes or lack of knowledge!
But, I gotta admit, RK has the patience of an elementary school teacher when it comes to explaining his code! It's greatly appreciated, and since the main focus of this board seems more geared towards Sql server management I consider myself lucky to have caught the eye of someone willing to take a bit of time out of their day to work on a problem for a non sql server manager.
Just a bit more info that may not matter, the linked tables are oracle, the database hosts several Apex applications and there could be anywhere from 50 to 400 people hitting that same server and database for many different reasons. I didn't want to be the one bogging down their sessions with my demands. The folks that set up and maintain those servers likely haven't tuned this particular database for anything other than the use of their front ends.
No public dates table defined in the DW, went through every table under my login.
I'm working on all parts of this thing at one time, going back and forth. Working today on data input interface.
I'm also thinking I popped the code into a new test database I was using and likely didn't re-reference the libaries :w00t:
I'll run it again and post results.
March 17, 2010 at 5:12 pm
yup, failed to reference the library 😀
Code ran ok. Names repeat throughout the months tho, and it looks like the DOW 2 or 3 is affecting the number of people assigned to the start times. As I think about it, the DOW may not be necessary? Those days are the only ones where an entry is made for the third start time, so there shouldn't be a 20:00 start any other day, so maybe it's not necessary to check for the DOW as long as the code still only assigns two people to a single start time.
Most helpful book at the moment is VBA for dummies. I've got something from DVPress on .net and SQL but it's geared towards .net more.
Getting the hand of recordsets as well, just no practice using them as of yet.
I'm getting there! The month I told them I'd need may be enough to get this up and running.
My usual job duties are simple computer tasks around the office, projects like this put me under the gun to learn quick and come up with a product. It's my own big mouth that gets me into these things!
Glad I'm doing it anyway. Since I can't use Apex and oracle I may as well learn VBA and access.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply