Advanced Ranking Query Options…
I have been developing a website where users enter details about positions that they would like to fill.
Other users enter details of positions that are available.
I currently run a ranking query for users that matches from the options they enter which positions best match their profile in a ranked order.
I'm using an Access Database and ASP.
The query is taking a very long time to run.
This is how I have the query built at the moment.
Code:strSQL = "SELECT qryApplicant.* FROM qryApplicant WHERE (((qryApplicant. ApplicantRID) & ''='" & applicantRef & "'));" call getFromDatabase(strSQL, objTempRS, currentPage) if not objTempRS.EOF then applicantWants = True applicantGender = objTempRS.Fields("ApplicantGender") applicantAge = objTempRS.Fields("ApplicantAge") applicantBSMinDuration= objTempRS.Fields("ApplicantBSMinDuration") applicantBSMaxDuration= objTempRS.Fields("ApplicantBSMaxDuration") applicantBSEarlyDate= objTempRS.Fields("ApplicantBSEarlyDate") applicantBSLateDate= objTempRS.Fields("ApplicantBSLateDate") applicantLanguage= objTempRS.Fields("ApplicantLanguage") applicantPosition= objTempRS.Fields("ApplicantPosition") applicantPositionType= objTempRS.Fields("ApplicantPositionType") applicantNationality= objTempRS.Fields("ApplicantNationality") end if if applicantWants then applicantPoss = 0 lineTemp = "" strSQL = "" strSQL = strSQL & "SELECT " & applicantRef & " AS ApplicantRef" strSQL = strSQL & ", qryEmployer.EmployerRID" ' ****************************** Check Gender ****************************** if applicantGender & "" <> "" then lineTemp = lineTemp & "(IIf(([EmployerCSGender]='any'),50,(IIf(([EmployerCSGender]='" & applicantGender & "'),50,1))))" else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 50 ' ****************************** Check CheckAge ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if isNumeric(applicantAge) then lineTemp = lineTemp & "IIf(([EmployerCSMinAge]<=" & applicantAge & "),IIf(([EmployerCSMaxAge]>=" & applicantAge & "),20,0),0)" else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 ' ****************************** Check CheckMinMaxDuration ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if isNumeric(applicantBSMinDuration) then if isNumeric(applicantBSMaxDuration) then lineTemp = lineTemp & "IIf(([EmployerCSMinDuration]<=" & applicantBSMaxDuration & "),IIf(([EmployerCSMaxDuration]>=" & applicantBSMinDuration & "),20,0),0)" else lineTemp = lineTemp & "IIf(([EmployerCSMaxDuration]>=" & applicantBSMinDuration & "),20,0" end if else if isNumeric(applicantBSMaxDuration) then lineTemp = lineTemp & "IIf(([EmployerCSMinDuration]<=" & applicantBSMaxDuration & "),20,0" else lineTemp = lineTemp & "(20)" end if end if applicantPoss = applicantPoss + 20 ' ****************************** Check CheckEarlyLateDuration ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if (applicantBSEarlyDate & "" <> "") then if (applicantBSLateDate & "" <> "") then lineTemp = lineTemp & "(IIf([EmployerCSLateDate]&''<>'',(IIf(([EmployerCSLateDate])>=" & DATE_DELIMITER & AusDate(applicantBSEarlyDate) & DATE_DELIMITER & ",(IIf([EmployerCSEarlyDate]&''<>'',(IIf(([EmployerCSEarlyDate])<=" & DATE_DELIMITER & AusDate(applicantBSLateDate) & DATE_DELIMITER & ",20,0)),0)),0)),0))" else lineTemp = lineTemp & "(IIf([EmployerCSEarlyDate]&''<>'',(IIf(([EmployerCSEarlyDate])<=" & DATE_DELIMITER & AusDate(applicantBSLateDate) & DATE_DELIMITER & ",20,0)),0))" end if else if (applicantBSLateDate & "" <> "") then lineTemp = lineTemp & "(IIf([EmployerCSLateDate]&''<>'',(IIf(([EmployerCSLateDate])>=" & DATE_DELIMITER & AusDate(applicantBSEarlyDate) & DATE_DELIMITER & ",20,0)),0))" else lineTemp = lineTemp & "(20)" end if end if applicantPoss = applicantPoss + 20 ' ****************************** Check Language ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if applicantLanguage & "" <> "" then getMultiValues applicantLanguage, ":", arrSplit01, arrSplit01Max itemTemp = "0" for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) <> "" then itemTemp = "(IIf(([EmployerLanguage] Like '%" & Left(arrSplit01(intCounter01),3) & "_%'),20," & itemTemp & "))" end if next lineTemp = lineTemp & itemTemp 'Response.Write itemTemp & "<br />" & " " else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 ' ****************************** Check Nationality ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if applicantNationality & "" <> "" then getMultiValues applicantNationality, ":", arrSplit01, arrSplit01Max itemTemp = "0" for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) <> "" then itemTemp = "(IIf(([EmployerNationality] Like '%" & arrSplit01(intCounter01) & ":%'),20," & itemTemp & "))" end if next lineTemp = lineTemp & itemTemp 'Response.Write itemTemp & "<br />" & " " else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 ' ****************************** Check Position ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if applicantPosition & "" <> "" then itemTemp = "" getMultiValues applicantPosition, ":", arrSplit01, arrSplit01Max itemTemp = "0" if instr(applicantPosition,"ANY") then itemTemp = "(10)" for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) <> "" then if arrSplit01(intCounter01) <> "ANY" then itemTemp = "(IIf(([EmployerPosition] Like '%" & Left(arrSplit01(intCounter01),3) & "_%'),20," & itemTemp & "))" end if end if next lineTemp = lineTemp & itemTemp 'Response.Write itemTemp & "<br />" & " " else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 ' ****************************** Check PositionType ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if applicantPositionType & "" <> "" then getMultiValues applicantPositionType, ":", arrSplit01, arrSplit01Max itemTemp = "0" strType1 = "" for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) = "ANY" then strType1 = "(10)" next if strType1 <> "" then itemTemp = strType1 for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) = "rea" then strType1 = "(IIf(([EmployerPositionType] Like '%r??:%'),15," & itemTemp & "))" end if next if strType1 <> "" then itemTemp = strType1 for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) = "coa" then strType1 = "(IIf(([EmployerPositionType] Like '%c??:%'),15," & itemTemp & "))" end if next if strType1 <> "" then itemTemp = strType1 for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) <> "" then itemTemp = "(IIf(([EmployerPositionType] Like '%" & arrSplit01(intCounter01) & ":%'),20," & itemTemp & "))" end if next lineTemp = lineTemp & itemTemp 'Response.Write itemTemp & "<br />" & " " else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 strSQL = strSQL & ", (" & lineTemp & ") AS Total, qryEmployer.EmployerActive, '" & applicantPoss & "' AS Poss, qryEmployer.* FROM qryEmployer " strSQL = strSQL & "WHERE (((qryEmployer.EmployerActive)=True) AND ((qryEmployer.EmployerLastLoginDate)>" & DATE_DELIMITER & dateAdd("d",(optionDaysListed * -1),now()) & DATE_DELIMITER & ")) " strSQL = strSQL & "ORDER BY (" & lineTemp & ") DESC, qryEmployer.EmployerLastLoginDate DESC;"
Does anyone know any other ways to build this type of Query to show result in a ranked order.
View Complete Forum Thread with Replies
Related Forum Messages:
Ranking Query
I've found a few similar posts to this, but nothing exactly answers what I need. I'm using the following query to get a list of the top five salesmen, based on the number of customers per salesman, per month. I've added a rankings field, based on a query I found here on the forum SELECT TOP 5 [SalesResults_1].CurrMonthClients, (SELECT count(*) FROM [SalesResults] WHERE [SalesResults].[CurrMonthClients] >= [SalesResults_1].CurrMonthClients;) AS Rank, [SalesResults_1].SPName FROM [SalesResults] AS [SalesResults_1] ORDER BY [SalesResults_1].CurrMonthClients DESC;" This works perfectly, with one exception. If two salesmen have the same number of clients and both are the joint highest, their rank is displayed as 2, rather than the desired 1. Can anyone spot some simple change to the query that would take this eventuality into account? Thanks in advance.
View Replies !
How To Run A Subtotal And Ranking Query
Have a table containing a column called "BOC" (Budget Object Code) that contains approximately 85 different 4 digit codes, with one selected for each row. (See sample below). Table has 30,000 rows of data. What I need to do is run a query that will give me a subtotal dollar amount for each unique BOC, and then display the results in descending order for both Obligations and Expenditures. Any ideas?? Sample Data: COST CENTER BOC OBLIGATIONS EXPENDITURES 119900 2670 1000.00 500.00 119901 2685 2000.00 1000.00 119999 2670 5000.00 2000.00 119920 3130 5000.00 5000.00
View Replies !
Qualification Ranking Query???
I am trying to build a report that will rank employees, based on their qualifications for a promotion to a specific position. I need to build one that will work for every position in the organization, based on the requirements for the specific position. I know this is vague, but before I go into a long explanation, can anyone tell me if this is even possible in Access?? Thank you!! KellyJo
View Replies !
Advanced Query Help
The below Stored Procedure was refined to incorporate a UDF. My problem now is that I need it to be even more dynamic. I ideally need the user to have the option to select which fields are incorporated into the query. I have the front end framework although not sure currently how to make field selection dynamic... CREATE PROCEDURE dbo.sp_ClientQuery ( @inputRegion varchar(500), @inputPub varchar(500), @inputCountry varchar(500) ) AS SELECT dbo.tblClient.Country, dbo.tblClient.cName, dbo.tblClient.clientID, dbo.tblClient.Wholename, dbo.tblClient.TelNumberG, dbo.tblClient.TelNumberD, dbo.tblClient.City, dbo.tblPublication.pName FROM dbo.tblOrders INNER JOIN dbo.tblClient ON dbo.tblOrders.clientID = dbo.tblClient.clientID INNER JOIN dbo.tblPublication ON dbo.tblOrders.id = dbo.tblPublication.id WHERE (dbo.tblClient.Region IN (select value from fnSplit(@inputRegion,',') )) AND (dbo.tblPublication.pName IN (select value from fnSplit(@inputPub,',') )) AND (dbo.tblClient.Country IN (select value from fnSplit(@inputCountry,',') )) GO This is becoming pretty complicated so any help appreciated. Phil
View Replies !
Advanced Sql Query
Hi Guys I am having a bit of a delimma and am wondering if there is someone out there that could suggest how i could write a SQL statement that would alow me to return data in a certain way, I have data as follows... FIrst problemi is the Date in the LogDate COlumn is of Text Type not Date.. Second problem is i need to take this data and transform it to look like the table below RCDIDEmployeeIDLogDateLogTimeTerminalIDInOut 411 07/23/200620:45:02iGuard# IN 421 07/23/200620:46:17iGuard# OUT 431 07/23/200620:48:08iGuard# IN 441 07/23/200620:48:18iGuard# OUT 451 07/23/200620:48:24iGuard# IN 461 07/23/200620:48:30iGuard# OUT 471 07/23/200620:48:36iGuard# IN 481 07/23/200620:48:41iGuard# OUT 501 07/23/200620:49:57iGuard# IN 511 07/23/200620:50:14iGuard# OUT 521 07/23/200620:59:34iGuard# IN 531 07/23/200620:59:40iGuard# OUT Employee IDDateInOutIn OutTotalTimeIn 123/07/200620:3520:3620:3820:3900:02 Basicaly i need to transpose it.. If anyone has the sql or knows the sql on how to do this i would be very greatful
View Replies !
Problem With Advanced Append Query
Hello all nice to see you again, I am having a problem creating an append query to populate multiple records from input on a form. The field on the form is a date field. To sum it up, the user will type in the date and click the commit button that runs the query. The query will take that date and create a record for each task in a list (separate table). I created an 3 expressions that point to the fields on the form to append to the key fields and date field in the completed tasks table. PARAMETERS Forms![NewJobSetup]![JDE Number] Long, Forms![NewJobSetup]![JDE Division Code] Text ( 255 ), Forms![NewJobSetup]![DateCommitted] DateTime; INSERT INTO CompletionLog ( [JDE Number], [JDE Division Code], TaskName, DateComplete ) SELECT [Forms]![NewJobSetup]![JDE Number] AS Expr1, [Forms]![NewJobSetup]![JDE Division Code] AS Expr2, [MasterTaskList]![ID] AS Expr3, [Forms]![NewJobSetup]![DateCommitted] AS Expr4 FROM MasterTaskList INNER JOIN (JobTable INNER JOIN CompletionLog ON (JobTable.[JDE Number] = CompletionLog.[JDE Number]) AND (JobTable.[JDE Division Code] = CompletionLog.[JDE Division Code])) ON MasterTaskList.ID = CompletionLog.TaskName; Please feel free to take a look at the DB I have attached to see what I am trying to do. Any leads on where to check for problems would be appreciated. Thanks Rob
View Replies !
Query With Options Into A Form
I would like to put a query into my form so that I can look up a part number, order number, or both, and then open the form for editing. I set up the queries but can't get them into the form. How would I go about doing this? Thanks for helping out
View Replies !
Query With Options Into A Form
I would like to put a query into a form so that I can look up a part number, order number, or both, and then open the form for editing. I set up the queries but can't get them into the form. How would I go about doing this? Thanks for helping.
View Replies !
Selecting Options In A Query
Sure its simple to do, I just don't know! lol. I want to create a query, where the user can choose from 3 products to choose from (from the same table, under the same field name) that they can search for to find which customers r buying it. Ideas....?
View Replies !
How To Add A "ranking" To A Query
Hi guys, Simple Question perhaps, but it has me stumped. I have a rather complex crosstab query which is used on a website (ASP) that shows a ranking for a competition. Now I need to add the rank itself, so say, just numbers from 1 to 10 which simply correspond with the row # since its ordered properly (descending by score). Sounds simple enough, but I have NO idea how to do that.. Anyone got an idea ? Thx, Vertigo
View Replies !
Ranking
I've got a football table ranked by Points and Goal Difference: e.g. Team Pts Goal Diff C 4 +2 A 4 +1 B 0 -3 and I want to introduce another field automatically (in a query) which gives the position of each team: Pos Team Pts Goal Diff 1 C 4 +2 2 A 4 +1 3 B 0 -3 Can anyone help? Thanks a lot Jimbob
View Replies !
Ranking
Could any one tell me how to change the ranking of a field from 1,2,3,4,5,6,....1000 to 1st,2nd,3rd,4th.5th.6th........1000th. Nested if could only give upto 15, and another method that is i used worked but made the query to run too slow. tHANKS
View Replies !
Ranking
I have a simple query which lists names and next to each name is a sum of sales. I have sorted this sum field decending and now I need to add a third column in the query which ranks these. Process Name: Sum of Variation: Rank Process A 100 1 Process B 98 2 Process C 65 3 Process D 52 4 I am a beginner in access and am unsure how to automatically create the rank field.
View Replies !
Ranking Data
Is there a way to write a formula in an Access query that would stack rank a group of numbers or dates in a field? I have a set of order dates and i want to rank by the most recent one. Being the recent orders in the top 20%. If orderdate is in the top 20%, return a 5. If Orderdate is in the next 20%, return a 4. If Orderdate is in the next 20%, return a 3. If orderdate is in the next 20%, return a 2. Then the remaining numbers, return a 1
View Replies !
Ranking In A Listbox
I have a list box that stores the project names in a Form. The user wants to have the ability to order the project in a way they want. And the report need to present this order in the same way. In other word, user wants to move the proejcts around in the listbox. Could anyone give a hand? Thank you.
View Replies !
Record Ranking By RecordNumber? Please Help
Is there a way to update a field in a sorted query with its recordnumber value, as if to assign a rank to it? Example Table contains Name, Grade, Rank sorted by Grade ASC Record 1: John, A, *Update here* Record 2: Tony, C, *Update here* Record 3: Eric, D, *Update here* The *Update here* is the bit that I need to update with each records recordnumber, so that the end result would be as follows: Record 1: John, A, 1 Record 2: Tony, C, 2 Record 3: Eric, D, 3 Can anyone please help? Thank, Steve Johnson
View Replies !
Question On Ranking And Autonumbers (I Think)
Hello All Firstly I have been reading this forum for a while picking up hints and tips and am very grateful so big grats and thanks to all you guys for the help The problem I am stuck on at the moment will hopfully be quite simple, but I should point out I have absolutely no skill or knowlede in Modules and VB language I most of my "stuff" via statements within the Design view. Ok so here we go. I have a query which brings back the top 50 performing sales items, which is done by sorting them in descending order and using that filter thing at the top of the design view, which is set to 50. What I would like to do is insert a field in the query which gives the rank, so the best seller is given number 1, the second best 2 and so on and so forth. Sorry if there is a topic already, I have tried to search, but couldnt find what I was looking for. Ta in advance for any help Mav
View Replies !
Ranking / Top 10 Under Multiple Categories
In a table I have: - Category (A, B, C) - Family (A1, A2, A3, B1, B2, etc) - Item - Sales I'm looking for a way to get the top 10 items in each category + family based on sales (ie: top 10 in A-A1, then A-A2, then A-A3, etc). When I try using the top function, it doesn't treat each category + family combination as unique. Any ideas?
View Replies !
Priority Ranking Adjustments
I found this from different fourm by searching google. This is exactly what I need to do and the answer was given; however, I can't figure it out. Can someone please post a step by step instruction on how to complete the following. Thank you! Question I have a datbase full of projects that must be ranked in priority order. This was simple enough but when I add a project I will likely need to change the priority rank of all the "lesser" projects. With over 500 projects adding a new project with a rank of 50 means changing all the ranks 50 and above. I want to be able to add a project and when I determine its rank I insert the rank number and every rank equal to the project just added is the indexed by 1 digit. No two project can be ranks the same, one is always more important than another. Answer Create an update query that sets the priority field to: [Priority] + 1 then set the criteria to >= new number Where new number is the rank of the new number. This will increment by one each priority. Hope this helps, Scott<>
View Replies !
Anybody Know A Good User Ranking System
This one is a doosy.. db is a change tracking system Ok, There are multiple 'departments'. Multiple 'roles' per department. some people cover multiple departments. When a change is submitted, it has to be approved before it can progress. Who can approve the change depends on the 'Risk' of the change (determined in the change form) It is possible for a change initiator to have rights to approve his/her own change. Does anybody know a system which would work for this situation? just say if you need me to clarify anything
View Replies !
Ranking Asp Search Results In Terms Of Relevence
Hi, we have an access database which we are querying with asp. There are plenty on columns in the database but one of the columns is "keywords". What we want is the rank the results in terms on relevence for that records that match the most search terms appear first. ie. if someone is searching for dog and cat then the access records with dog AND cat are ranked higher than those containing just dog or just cat. Is this possible? Does anyone know of an online tutorial that deals with this? Thanks in advance James
View Replies !
Advanced Customization
This should be a piece of cake for someone with a few experience years on me. I have a client for which I am building a custom database. They use this for various reasons. One main reason is to track man hours, cost, billing information on a per job/customer basis. Each of their customers has a unique contract. Which specifies the information required in order to bill the customer for a job. For example: Customer "Big Factory" requires PO# and Job#. The contract also specifies the billing rate for each piece of equipment and each type of employee. For example: For customer "Big Factory" again, we charge $10 a day for a pickup truck and $20 an hour for a welder. Customer "Little Factory" has a different contract with different rates and required fields. *Now I have all of the above under control (in my opinion)* Following is part is where I struggle. Each contract also specifies which hours (on a daily basis) are standard work hours and which are premium work hours, as well as, at what point (# of weekly hours) to start billing only premium hours during the remaining part of the week. For example: "Big Factory" Standard hours are from 07:00 to 17:00, Premium are from 17:00 to 07:00, Weekly premium start once an employee has worked 40 hours for that week regardless if the employee has worked some hours for another client(*Exceptions do exist where the employee must work 40 hours for the client before the weekly premium kicks in*). I need this to automatically calculate for each employee. It must update each time sheet that the employee exists on. Currently I have it working in a rather unorthodox way, but it isn't exactly scalable to work for each customer's unique contract. I can post how I have it, but it will be quite a long post. It has to do with a running sum query then a make table query then an update query. One other caveat: My client pays their employees premium pay on a 40 hour work week (>40 per week is premium time) regardless of which hours they work. (Seems like they are ripping the employees off I know). I posted this once before, but had received only a negative post chewing me out for not being fair. Anyway... If presented with this how would you do it? Any help is greatly appreciated. Thanks, Eric
View Replies !
Advanced Search Box
I want to make something similar to the Google search box, although not quite as complicated. I would like to be able to type more than one word in a search box. I am only searching on 1 field [Title]. The query would show all the records with the first search word in the Title, plus all the records with the second search word in the Title. I have a query now that has the below in the Title field criteria: Like "*" & [forms]![Searchform]![searchcontrol] & "*" This searches for the word typed into the searchcontrol no matter where the word is in the title. It works great, but I can only type in one word at a time.
View Replies !
Help With Advanced Problem
Hi there - I'm trying to produce an application in Access to populate a CD information data base. I have signed up for (and downloaded) the GraceNote SDK (they're the folks who operate CDDB these days) and it seems to work - at least early tests show that the VBScript sample works to a point with their ActiveX component. My problem is that of retrieving the TOC from the cd. Now, I'm not a professional developer though I have written code before (C for an OS/2 application - no, don't laugh) and some stuff in REXX. However, I am totally baffled by this one. Their SDK indicates that I need to call their function and pointing it at the CD device by using the MCIDeviceID. Now, try as I might, I can find no reference which is helping me to identify what this is and how it is formatted. As I understand it, I should be able to do this from within a procedure behind a control on a form - as I say the other bits seem to work ok. Can anyone help me please?? It's probably a very simple thing if you know it - unfortunately, I don't :( Thanks in advance, regards, Chris B
View Replies !
Advanced Sort/filter
I am using a handheld scanner to scan in barcodes. However the part numbers contain more data than I need. Is there a way to get ride of the extra data I scan in in Access. Say I have part ABCD- 7G675 .. I only need 7g675 to be in the field. Is there a way to do this besides a find replace, that would take way to long.
View Replies !
Advanced QBF And Importing Into Access
Hi everybody! :) After a week of hair pulling problems with no solution, I've decided to ask my question here, knowing someone will be able to help me... To the point: I'm to use a QBF with more then one criteria, (I'm using: like "*" & forms![f_name]![TB_first_name] & "*" or is null), I'm having a problem with this because when I leave a text box blank, it retrieves everything (because is null = true), what I'm trying to say is: I have a QBF when using, lets say, 3 text boxes, unless I fill all of the text boxes with text, the query retrieves all of the records, can it be fixed ? I've tried to use IIF but with no success... can anyone help ? :confused: and another problem: while trying to import data which is not in English, from a Visual FoxPro data base to access, it turns out unreadable, what can I do to transform ASCII to ANSI or Unicode? :confused: p.s. - Sorry for my English TIA WildCat
View Replies !
Advanced Employee Schedule
Hello Everyone I need to make an employee schedule in access. I am not sure if it is possible, but here are my constraints: 20 employees 5-6 different truck locations (sm1, sm2, sm3, sm4, sm5, sm6) These locations are scattered within our service area. 2 employees must meet at the same truck, (each truck has 2 employees working on it) some employees can only meet at certain locations (ex. paul can meet at either sm2 or sm3) all employees are PT so they can only work on certain days or a certain amount of days per week. (ex. paul can work up to 4 days a week and jim can work only sat and fri) I would like to make a program in access that will take all these factors into consideration. EX. if i try to schedule paul on sm4 the program will not let me do it or it will give a warning or if i try to schedule paul for 5 days it will give me a warning. It would also be great to have a counter that will show how many days each employee has left to be scheduled for, EX: if i scheduled paul for 2 days it will show that he can actually work 2 days more if i need him to. I know this might be a lot, but i also know there are so many talented people here so i am sure it is feasible. Please let me know if you know how to do this or if you could guide me through it. or if you know about a different program that can solve this please let me know Thanks in advance
View Replies !
Advanced Querie Problem
Hello gurus I have a d/base (Access 2000) and within this I need to filter out some records insurnace related so I have a location code of ten venues coded 2-11 in a table and I need to extract out informaiton on all of these in one table I date a date of event and a sum insured , what I need is date difference between date to be enter on a form (default Now()) and the date of the event - this is the easy bit Now i need to split these out in batches of 30 days into columns ie 1-30 days 31-60 days 61-90 etc until >240 days the sums insured need to be in the right date column example event 1 sum insured @ 25,000 is 45 days away from now so it should appear in the 31-60 column and if there more than 2 events at that venue one being 45 days and the other being 55 and with sums insured @ 25,000 and 35,000 I need them to either be on a line each or combined to give a total of 60,000 in the 31-60 date column There could be up to 20,000 - to 100,000 of which the code will extract the venue codes of 2-11 and these could be high hundreds records to check and this will be a regularly run question any thoughts on this would be welcome - each appraoch I take seems to weird and getting nowhere - i thought about putting a date difference field on my table but it doesn't seem liek the right thing to do as I would have to update this consantly or rather remember to run this query before I run my report - and this just seems wrong any thoughts
View Replies !
|
TRACKER
