If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Please help me fix this flawed query formula
Okay, first let me say, I didn't create this query, but I'm trying to
fix it or write it better. Now here's the deal, and then I'll tell you the problem. We have an attendance bonus program at work based on points. You know, if your tardy, absent, you get points. Well, based on four quarters for the year, you have the ability to get a bonus for each quarter of perfect attendance. For perfect attendance each quarter it's $100.00 or possibly more. So say, You have zero points (perfect attendance) the first quarter, you get $100 then the second quarter with zero points it's $200, then $400 and if you continue to have perfect attendence by the end of the year (final quarter) you can receive $800. Well, the problem is, those who do have perfect attendance and receive $800, when it starts all over, they are still showing $800 as opposed to $100, which they should have. I need to figure out a way to reset those with $800.00. What we have been doing is for those with perfect attendance four consequtive quarters is putting in the smallest amount of pointage: .001 for them so the query will show $100, if they continue with perfect attendance. The dates for each quarter a 1st (12/1 to 2/28) 2nd (3/1 to 5/31) 3rd (6/1 8/31) 4th (9/1 to 11/30) Here are the formulas (I know the year needs to be changed to '05 , but the date is the same) : Modifier: IIf([Hire Date]#6/1/2004# And [Bonus1]=800,-800,IIf([Hire Date] Between #3/1/2004# And #5/31/2004# And [Bonus1]=800,-700,IIf([Hire Date] Between #12/1/2003# And #2/29/2004# And [Bonus1]=800,-600,IIf([Hire Date] Between #9/1/2003# And #12/31/2003# And [Bonus1]=800,-400,0)))) Bonus1: IIf([SumOf1st Quarter]=0 And [SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,800,IIf([SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,400,IIf([SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,200,IIf([SumOf4th Quarter]=0,100,0)))) Maybe I should rewrite the whole thing from scratch because whomever wrote it, started it backwards, with $800 and then deducted by taking into account when they were hired because they are only eligible the first quarter starting after their 90 day probation. Thanks for any responses |
#2
|
|||
|
|||
Please help me fix this flawed query formula
Dear Slave:
What I'd need to know is this. Do you have a table where you record absences or tardiness? Is this a table like below: Employee Date Points If you also have a table of the calendar: StartDate QuarterNumber FiscalYear 12/01/2005 1 2006 03/01/2006 2 2006 06/01/2006 3 2006 09/01/2006 4 2006 then we can use that in the query, too. The above is probably all that is required. But without knowing if you have these table, or what you do have, it is not possible to proceed to help you. Tom Ellison "circuit slave" wrote in message oups.com... Okay, first let me say, I didn't create this query, but I'm trying to fix it or write it better. Now here's the deal, and then I'll tell you the problem. We have an attendance bonus program at work based on points. You know, if your tardy, absent, you get points. Well, based on four quarters for the year, you have the ability to get a bonus for each quarter of perfect attendance. For perfect attendance each quarter it's $100.00 or possibly more. So say, You have zero points (perfect attendance) the first quarter, you get $100 then the second quarter with zero points it's $200, then $400 and if you continue to have perfect attendence by the end of the year (final quarter) you can receive $800. Well, the problem is, those who do have perfect attendance and receive $800, when it starts all over, they are still showing $800 as opposed to $100, which they should have. I need to figure out a way to reset those with $800.00. What we have been doing is for those with perfect attendance four consequtive quarters is putting in the smallest amount of pointage: .001 for them so the query will show $100, if they continue with perfect attendance. The dates for each quarter a 1st (12/1 to 2/28) 2nd (3/1 to 5/31) 3rd (6/1 8/31) 4th (9/1 to 11/30) Here are the formulas (I know the year needs to be changed to '05 , but the date is the same) : Modifier: IIf([Hire Date]#6/1/2004# And [Bonus1]=800,-800,IIf([Hire Date] Between #3/1/2004# And #5/31/2004# And [Bonus1]=800,-700,IIf([Hire Date] Between #12/1/2003# And #2/29/2004# And [Bonus1]=800,-600,IIf([Hire Date] Between #9/1/2003# And #12/31/2003# And [Bonus1]=800,-400,0)))) Bonus1: IIf([SumOf1st Quarter]=0 And [SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,800,IIf([SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,400,IIf([SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,200,IIf([SumOf4th Quarter]=0,100,0)))) Maybe I should rewrite the whole thing from scratch because whomever wrote it, started it backwards, with $800 and then deducted by taking into account when they were hired because they are only eligible the first quarter starting after their 90 day probation. Thanks for any responses |
#3
|
|||
|
|||
Please help me fix this flawed query formula
Dear Slave:
I misspoke somewhat. We will also need a table of all employees. An employee who is never tardy or abesnt would have no records in the Points table I proposed, so we must know that he exists in spite of this, by looking at a table of all employees. All we nee is that column (or columns) that uniquely identify an employee. Tom Ellison "Tom Ellison" wrote in message ... Dear Slave: What I'd need to know is this. Do you have a table where you record absences or tardiness? Is this a table like below: Employee Date Points If you also have a table of the calendar: StartDate QuarterNumber FiscalYear 12/01/2005 1 2006 03/01/2006 2 2006 06/01/2006 3 2006 09/01/2006 4 2006 then we can use that in the query, too. The above is probably all that is required. But without knowing if you have these table, or what you do have, it is not possible to proceed to help you. Tom Ellison "circuit slave" wrote in message oups.com... Okay, first let me say, I didn't create this query, but I'm trying to fix it or write it better. Now here's the deal, and then I'll tell you the problem. We have an attendance bonus program at work based on points. You know, if your tardy, absent, you get points. Well, based on four quarters for the year, you have the ability to get a bonus for each quarter of perfect attendance. For perfect attendance each quarter it's $100.00 or possibly more. So say, You have zero points (perfect attendance) the first quarter, you get $100 then the second quarter with zero points it's $200, then $400 and if you continue to have perfect attendence by the end of the year (final quarter) you can receive $800. Well, the problem is, those who do have perfect attendance and receive $800, when it starts all over, they are still showing $800 as opposed to $100, which they should have. I need to figure out a way to reset those with $800.00. What we have been doing is for those with perfect attendance four consequtive quarters is putting in the smallest amount of pointage: .001 for them so the query will show $100, if they continue with perfect attendance. The dates for each quarter a 1st (12/1 to 2/28) 2nd (3/1 to 5/31) 3rd (6/1 8/31) 4th (9/1 to 11/30) Here are the formulas (I know the year needs to be changed to '05 , but the date is the same) : Modifier: IIf([Hire Date]#6/1/2004# And [Bonus1]=800,-800,IIf([Hire Date] Between #3/1/2004# And #5/31/2004# And [Bonus1]=800,-700,IIf([Hire Date] Between #12/1/2003# And #2/29/2004# And [Bonus1]=800,-600,IIf([Hire Date] Between #9/1/2003# And #12/31/2003# And [Bonus1]=800,-400,0)))) Bonus1: IIf([SumOf1st Quarter]=0 And [SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,800,IIf([SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,400,IIf([SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,200,IIf([SumOf4th Quarter]=0,100,0)))) Maybe I should rewrite the whole thing from scratch because whomever wrote it, started it backwards, with $800 and then deducted by taking into account when they were hired because they are only eligible the first quarter starting after their 90 day probation. Thanks for any responses |
#4
|
|||
|
|||
Please help me fix this flawed query formula
Thanks for helping, Tom. Actually, yes. There are two tables.
One is the Employee Table that has all the info on the employee, i.e., employee ID, hire date, address, DOB, etc. Then there is the Employee Events table, which includes all the attendance, points if any, vac time, PTO, etc. The report which is run gets the info from these two tables I assume. Also the Employee Events table has a column called "date" which is the date the info is entered. Is that a problem? having a column called "date" ? thanks for the help |
#5
|
|||
|
|||
Please help me fix this flawed query formula
Dear Slave:
In future, please do not delete all the history from your post. It's easier to follow what is happening by referring to what has occurred before. Also, if you would, top post to it, so the whole thing reads in reverse chronological order. That helps me. I answer a few hundred of these a year, and must refer back to see where each thread is. Thanks. That helps me greatly. Now, on to the task at hand. Of course, it is expected there will be other columns. We need only reference those that affect the mechanism we're creating. Are you willing and able to create the calendar table I requested. Otherwise, you'll be modifying the query every time a new year rolls around, and it can't be made to work for previous years after you've modified it. This is not such a good programming practice, in my opinion. I expect this will be a bit of an endeavor on my part to put something together. I'm reasonably confident I can do it, but it isn't fully formed yet. I'd rather not do the additional, duplicate work needed to create tables and enter data. Would you be willing to put together a reduced database for me with just the relevant tables and email that. I would like to use that as a basis to craft a solution. Just these tables now, no forms or reports. When you make a separate database like this, just copy objects from your existing database (import is a function available on the database menu). When you have finished, use Windows Explorer to rename this file, changing the file extension from MDB to XXX. Then zip the file and attach that to the email. Having a column named "date" is a small problem, as it makes coding ambiguous. There's a function named date. It would have been better to name the column EventDate, for example. Sometimes there is more than one "date" needed in a table, and reducing ambiguity for yourself and for Access is a good practice. It isn't essential however. You must always place square brackets around "date" when it is a column name to avoid ambiguity, or you may get a very nasty surprise some day, when it is misinterpreted and gives very mysterious results. By "some day" I do not mean that your programming will suddenly malfunction because Access has changed how it interprets this. Rather, it will happen in some new work you are doing. But it will be very nasty, because it will be unexplainable, very hard to find. Not what you want to spend your time doing, trying to track this down so you can fix it! Please let me know if you want to proceed on this basis. Tom Ellison "circuit slave" wrote in message oups.com... Thanks for helping, Tom. Actually, yes. There are two tables. One is the Employee Table that has all the info on the employee, i.e., employee ID, hire date, address, DOB, etc. Then there is the Employee Events table, which includes all the attendance, points if any, vac time, PTO, etc. The report which is run gets the info from these two tables I assume. Also the Employee Events table has a column called "date" which is the date the info is entered. Is that a problem? having a column called "date" ? thanks for the help |
#6
|
|||
|
|||
Please help me fix this flawed query formula
Okay, first let me say, I didn't create this query, but I'm trying to
fix it or write it better. Now here's the deal, and then I'll tell you the problem. We have an attendance bonus program at work based on points. You know, if your tardy, absent, you get points. Well, based on four quarters for the year, you have the ability to get a bonus for each quarter of perfect attendance. For perfect attendance each quarter it's $100.00 or possibly more. So say, You have zero points (perfect attendance) the first quarter, you get $100 then the second quarter with zero points it's $200, then $400 and if you continue to have perfect attendence by the end of the year (final quarter) you can receive $800. Well, the problem is, those who do have perfect attendance and receive $800, when it starts all over, they are still showing $800 as opposed to $100, which they should have. I need to figure out a way to reset those with $800.00. What we have been doing is for those with perfect attendance four consequtive quarters is putting in the smallest amount of pointage: .001 for them so the query will show $100, if they continue with perfect attendance. The dates for each quarter a 1st (12/1 to 2/28) 2nd (3/1 to 5/31) 3rd (6/1 8/31) 4th (9/1 to 11/30) Here are the formulas (I know the year needs to be changed to '05 , but the date is the same) : Modifier: IIf([Hire Date]#6/1/2004# And [Bonus1]=800,-800,IIf([Hire Date] Between #3/1/2004# And #5/31/2004# And [Bonus1]=800,-700,IIf([Hire Date] Between #12/1/2003# And #2/29/2004# And [Bonus1]=800,-600,IIf([Hire Date] Between #9/1/2003# And #12/31/2003# And [Bonus1]=800,-400,0)))) Bonus1: IIf([SumOf1st Quarter]=0 And [SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,800,IIf([SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,400,IIf([SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,200,IIf([SumOf4th Quarter]=0,100,0)))) Maybe I should rewrite the whole thing from scratch because whomever wrote it, started it backwards, with $800 and then deducted by taking into account when they were hired because they are only eligible the first quarter starting after their 90 day probation. Thanks for any responses Reply 2. Tom Ellison Feb 4, 11:42 pm show options Newsgroups: microsoft.public.access.queries From: "Tom Ellison" - Find messages by this author Date: Sun, 5 Feb 2006 01:42:31 -0600 Local: Sat, Feb 4 2006 11:42 pm Subject: Please help me fix this flawed query formula Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse Dear Slave: What I'd need to know is this. Do you have a table where you record absences or tardiness? Is this a table like below: Employee Date Points If you also have a table of the calendar: StartDate QuarterNumber FiscalYear 12/01/2005 1 2006 03/01/2006 2 2006 06/01/2006 3 2006 09/01/2006 4 2006 then we can use that in the query, too. The above is probably all that is required. But without knowing if you have these table, or what you do have, it is not possible to proceed to help you. Tom Ellison "circuit slave" wrote in message oups.com... - Hide quoted text - - Show quoted text - Okay, first let me say, I didn't create this query, but I'm trying to fix it or write it better. Now here's the deal, and then I'll tell you the problem. We have an attendance bonus program at work based on points. You know, if your tardy, absent, you get points. Well, based on four quarters for the year, you have the ability to get a bonus for each quarter of perfect attendance. For perfect attendance each quarter it's $100.00 or possibly more. So say, You have zero points (perfect attendance) the first quarter, you get $100 then the second quarter with zero points it's $200, then $400 and if you continue to have perfect attendence by the end of the year (final quarter) you can receive $800. Well, the problem is, those who do have perfect attendance and receive $800, when it starts all over, they are still showing $800 as opposed to $100, which they should have. I need to figure out a way to reset those with $800.00. What we have been doing is for those with perfect attendance four consequtive quarters is putting in the smallest amount of pointage: .001 for them so the query will show $100, if they continue with perfect attendance. The dates for each quarter a 1st (12/1 to 2/28) 2nd (3/1 to 5/31) 3rd (6/1 8/31) 4th (9/1 to 11/30) Here are the formulas (I know the year needs to be changed to '05 , but the date is the same) : Modifier: IIf([Hire Date]#6/1/2004# And [Bonus1]=800,-800,IIf([Hire Date] Between #3/1/2004# And #5/31/2004# And [Bonus1]=800,-700,IIf([Hire Date] Between #12/1/2003# And #2/29/2004# And [Bonus1]=800,-600,IIf([Hire Date] Between #9/1/2003# And #12/31/2003# And [Bonus1]=800,-400,0)))) Bonus1: IIf([SumOf1st Quarter]=0 And [SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,800,IIf([SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,400,IIf([SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,200,IIf([SumOf4th Quarter]=0,100,0)))) Maybe I should rewrite the whole thing from scratch because whomever wrote it, started it backwards, with $800 and then deducted by taking into account when they were hired because they are only eligible the first quarter starting after their 90 day probation. Thanks for any responses Reply 3. Tom Ellison Feb 4, 11:51 pm show options Newsgroups: microsoft.public.access.queries From: "Tom Ellison" - Find messages by this author Date: Sun, 5 Feb 2006 01:51:08 -0600 Local: Sat, Feb 4 2006 11:51 pm Subject: Please help me fix this flawed query formula Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse Dear Slave: I misspoke somewhat. We will also need a table of all employees. An employee who is never tardy or abesnt would have no records in the Points table I proposed, so we must know that he exists in spite of this, by looking at a table of all employees. All we nee is that column (or columns) that uniquely identify an employee. Tom Ellison "Tom Ellison" wrote in message ... - Hide quoted text - - Show quoted text - Dear Slave: What I'd need to know is this. Do you have a table where you record absences or tardiness? Is this a table like below: Employee Date Points If you also have a table of the calendar: StartDate QuarterNumber FiscalYear 12/01/2005 1 2006 03/01/2006 2 2006 06/01/2006 3 2006 09/01/2006 4 2006 then we can use that in the query, too. The above is probably all that is required. But without knowing if you have these table, or what you do have, it is not possible to proceed to help you. Tom Ellison "circuit slave" wrote in message oups.com... Okay, first let me say, I didn't create this query, but I'm trying to fix it or write it better. Now here's the deal, and then I'll tell you the problem. We have an attendance bonus program at work based on points. You know, if your tardy, absent, you get points. Well, based on four quarters for the year, you have the ability to get a bonus for each quarter of perfect attendance. For perfect attendance each quarter it's $100.00 or possibly more. So say, You have zero points (perfect attendance) the first quarter, you get $100 then the second quarter with zero points it's $200, then $400 and if you continue to have perfect attendence by the end of the year (final quarter) you can receive $800. Well, the problem is, those who do have perfect attendance and receive $800, when it starts all over, they are still showing $800 as opposed to $100, which they should have. I need to figure out a way to reset those with $800.00. What we have been doing is for those with perfect attendance four consequtive quarters is putting in the smallest amount of pointage: .001 for them so the query will show $100, if they continue with perfect attendance. The dates for each quarter a 1st (12/1 to 2/28) 2nd (3/1 to 5/31) 3rd (6/1 8/31) 4th (9/1 to 11/30) Here are the formulas (I know the year needs to be changed to '05 , but the date is the same) : Modifier: IIf([Hire Date]#6/1/2004# And [Bonus1]=800,-800,IIf([Hire Date] Between #3/1/2004# And #5/31/2004# And [Bonus1]=800,-700,IIf([Hire Date] Between #12/1/2003# And #2/29/2004# And [Bonus1]=800,-600,IIf([Hire Date] Between #9/1/2003# And #12/31/2003# And [Bonus1]=800,-400,0)))) Bonus1: IIf([SumOf1st Quarter]=0 And [SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,800,IIf([SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,400,IIf([SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,200,IIf([SumOf4th Quarter]=0,100,0)))) Maybe I should rewrite the whole thing from scratch because whomever wrote it, started it backwards, with $800 and then deducted by taking into account when they were hired because they are only eligible the first quarter starting after their 90 day probation. Thanks for any responses Reply 4. circuit slave Feb 5, 1:50 am show options Newsgroups: microsoft.public.access.queries From: "circuit slave" - Find messages by this author Date: 5 Feb 2006 01:50:25 -0800 Local: Sun, Feb 5 2006 1:50 am Subject: Please help me fix this flawed query formula Reply | Reply to Author | Forward | Print | Individual Message | Show original | Remove | Report Abuse Thanks for helping, Tom. Actually, yes. There are two tables. One is the Employee Table that has all the info on the employee, i.e., employee ID, hire date, address, DOB, etc. Then there is the Employee Events table, which includes all the attendance, points if any, vac time, PTO, etc. The report which is run gets the info from these two tables I assume. Also the Employee Events table has a column called "date" which is the date the info is entered. Is that a problem? having a column called "date" ? thanks for the help Reply 5. Tom Ellison Feb 5, 8:40 am show options Newsgroups: microsoft.public.access.queries From: "Tom Ellison" - Find messages by this author Date: Sun, 5 Feb 2006 10:40:22 -0600 Local: Sun, Feb 5 2006 8:40 am Subject: Please help me fix this flawed query formula Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse Dear Slave: In future, please do not delete all the history from your post. It's easier to follow what is happening by referring to what has occurred before. Also, if you would, top post to it, so the whole thing reads in reverse chronological order. That helps me. I answer a few hundred of these a year, and must refer back to see where each thread is. Thanks. That helps me greatly. Now, on to the task at hand. Of course, it is expected there will be other columns. We need only reference those that affect the mechanism we're creating. Are you willing and able to create the calendar table I requested. Otherwise, you'll be modifying the query every time a new year rolls around, and it can't be made to work for previous years after you've modified it. This is not such a good programming practice, in my opinion. I expect this will be a bit of an endeavor on my part to put something together. I'm reasonably confident I can do it, but it isn't fully formed yet. I'd rather not do the additional, duplicate work needed to create tables and enter data. Would you be willing to put together a reduced database for me with just the relevant tables and email that. I would like to use that as a basis to craft a solution. Just these tables now, no forms or reports. When you make a separate database like this, just copy objects from your existing database (import is a function available on the database menu). When you have finished, use Windows Explorer to rename this file, changing the file extension from MDB to XXX. Then zip the file and attach that to the email. Having a column named "date" is a small problem, as it makes coding ambiguous. There's a function named date. It would have been better to name the column EventDate, for example. Sometimes there is more than one "date" needed in a table, and reducing ambiguity for yourself and for Access is a good practice. It isn't essential however. You must always place square brackets around "date" when it is a column name to avoid ambiguity, or you may get a very nasty surprise some day, when it is misinterpreted and gives very mysterious results. By "some day" I do not mean that your programming will suddenly malfunction because Access has changed how it interprets this. Rather, it will happen in some new work you are doing. But it will be very nasty, because it will be unexplainable, very hard to find. Not what you want to spend your time doing, trying to track this down so you can fix it! Please let me know if you want to proceed on this basis. Tom Ellison Thanks alot, Tom, and, yes, I can create that calendar table, and email you the database in reduced form. |
#7
|
|||
|
|||
Please help me fix this flawed query formula
Dear Slave:
If you are Elias, then I have received your email. Attempts to respond to it have bounced back. Be sure to use a valid return address or we cannot communicate. Email me again with a valid return, please. Tom Ellison "circuit slave" wrote in message oups.com... Okay, first let me say, I didn't create this query, but I'm trying to fix it or write it better. Now here's the deal, and then I'll tell you the problem. We have an attendance bonus program at work based on points. You know, if your tardy, absent, you get points. Well, based on four quarters for the year, you have the ability to get a bonus for each quarter of perfect attendance. For perfect attendance each quarter it's $100.00 or possibly more. So say, You have zero points (perfect attendance) the first quarter, you get $100 then the second quarter with zero points it's $200, then $400 and if you continue to have perfect attendence by the end of the year (final quarter) you can receive $800. Well, the problem is, those who do have perfect attendance and receive $800, when it starts all over, they are still showing $800 as opposed to $100, which they should have. I need to figure out a way to reset those with $800.00. What we have been doing is for those with perfect attendance four consequtive quarters is putting in the smallest amount of pointage: .001 for them so the query will show $100, if they continue with perfect attendance. The dates for each quarter a 1st (12/1 to 2/28) 2nd (3/1 to 5/31) 3rd (6/1 8/31) 4th (9/1 to 11/30) Here are the formulas (I know the year needs to be changed to '05 , but the date is the same) : Modifier: IIf([Hire Date]#6/1/2004# And [Bonus1]=800,-800,IIf([Hire Date] Between #3/1/2004# And #5/31/2004# And [Bonus1]=800,-700,IIf([Hire Date] Between #12/1/2003# And #2/29/2004# And [Bonus1]=800,-600,IIf([Hire Date] Between #9/1/2003# And #12/31/2003# And [Bonus1]=800,-400,0)))) Bonus1: IIf([SumOf1st Quarter]=0 And [SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,800,IIf([SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,400,IIf([SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,200,IIf([SumOf4th Quarter]=0,100,0)))) Maybe I should rewrite the whole thing from scratch because whomever wrote it, started it backwards, with $800 and then deducted by taking into account when they were hired because they are only eligible the first quarter starting after their 90 day probation. Thanks for any responses Reply 2. Tom Ellison Feb 4, 11:42 pm show options Newsgroups: microsoft.public.access.queries From: "Tom Ellison" - Find messages by this author Date: Sun, 5 Feb 2006 01:42:31 -0600 Local: Sat, Feb 4 2006 11:42 pm Subject: Please help me fix this flawed query formula Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse Dear Slave: What I'd need to know is this. Do you have a table where you record absences or tardiness? Is this a table like below: Employee Date Points If you also have a table of the calendar: StartDate QuarterNumber FiscalYear 12/01/2005 1 2006 03/01/2006 2 2006 06/01/2006 3 2006 09/01/2006 4 2006 then we can use that in the query, too. The above is probably all that is required. But without knowing if you have these table, or what you do have, it is not possible to proceed to help you. Tom Ellison "circuit slave" wrote in message oups.com... - Hide quoted text - - Show quoted text - Okay, first let me say, I didn't create this query, but I'm trying to fix it or write it better. Now here's the deal, and then I'll tell you the problem. We have an attendance bonus program at work based on points. You know, if your tardy, absent, you get points. Well, based on four quarters for the year, you have the ability to get a bonus for each quarter of perfect attendance. For perfect attendance each quarter it's $100.00 or possibly more. So say, You have zero points (perfect attendance) the first quarter, you get $100 then the second quarter with zero points it's $200, then $400 and if you continue to have perfect attendence by the end of the year (final quarter) you can receive $800. Well, the problem is, those who do have perfect attendance and receive $800, when it starts all over, they are still showing $800 as opposed to $100, which they should have. I need to figure out a way to reset those with $800.00. What we have been doing is for those with perfect attendance four consequtive quarters is putting in the smallest amount of pointage: .001 for them so the query will show $100, if they continue with perfect attendance. The dates for each quarter a 1st (12/1 to 2/28) 2nd (3/1 to 5/31) 3rd (6/1 8/31) 4th (9/1 to 11/30) Here are the formulas (I know the year needs to be changed to '05 , but the date is the same) : Modifier: IIf([Hire Date]#6/1/2004# And [Bonus1]=800,-800,IIf([Hire Date] Between #3/1/2004# And #5/31/2004# And [Bonus1]=800,-700,IIf([Hire Date] Between #12/1/2003# And #2/29/2004# And [Bonus1]=800,-600,IIf([Hire Date] Between #9/1/2003# And #12/31/2003# And [Bonus1]=800,-400,0)))) Bonus1: IIf([SumOf1st Quarter]=0 And [SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,800,IIf([SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,400,IIf([SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,200,IIf([SumOf4th Quarter]=0,100,0)))) Maybe I should rewrite the whole thing from scratch because whomever wrote it, started it backwards, with $800 and then deducted by taking into account when they were hired because they are only eligible the first quarter starting after their 90 day probation. Thanks for any responses Reply 3. Tom Ellison Feb 4, 11:51 pm show options Newsgroups: microsoft.public.access.queries From: "Tom Ellison" - Find messages by this author Date: Sun, 5 Feb 2006 01:51:08 -0600 Local: Sat, Feb 4 2006 11:51 pm Subject: Please help me fix this flawed query formula Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse Dear Slave: I misspoke somewhat. We will also need a table of all employees. An employee who is never tardy or abesnt would have no records in the Points table I proposed, so we must know that he exists in spite of this, by looking at a table of all employees. All we nee is that column (or columns) that uniquely identify an employee. Tom Ellison "Tom Ellison" wrote in message ... - Hide quoted text - - Show quoted text - Dear Slave: What I'd need to know is this. Do you have a table where you record absences or tardiness? Is this a table like below: Employee Date Points If you also have a table of the calendar: StartDate QuarterNumber FiscalYear 12/01/2005 1 2006 03/01/2006 2 2006 06/01/2006 3 2006 09/01/2006 4 2006 then we can use that in the query, too. The above is probably all that is required. But without knowing if you have these table, or what you do have, it is not possible to proceed to help you. Tom Ellison "circuit slave" wrote in message oups.com... Okay, first let me say, I didn't create this query, but I'm trying to fix it or write it better. Now here's the deal, and then I'll tell you the problem. We have an attendance bonus program at work based on points. You know, if your tardy, absent, you get points. Well, based on four quarters for the year, you have the ability to get a bonus for each quarter of perfect attendance. For perfect attendance each quarter it's $100.00 or possibly more. So say, You have zero points (perfect attendance) the first quarter, you get $100 then the second quarter with zero points it's $200, then $400 and if you continue to have perfect attendence by the end of the year (final quarter) you can receive $800. Well, the problem is, those who do have perfect attendance and receive $800, when it starts all over, they are still showing $800 as opposed to $100, which they should have. I need to figure out a way to reset those with $800.00. What we have been doing is for those with perfect attendance four consequtive quarters is putting in the smallest amount of pointage: .001 for them so the query will show $100, if they continue with perfect attendance. The dates for each quarter a 1st (12/1 to 2/28) 2nd (3/1 to 5/31) 3rd (6/1 8/31) 4th (9/1 to 11/30) Here are the formulas (I know the year needs to be changed to '05 , but the date is the same) : Modifier: IIf([Hire Date]#6/1/2004# And [Bonus1]=800,-800,IIf([Hire Date] Between #3/1/2004# And #5/31/2004# And [Bonus1]=800,-700,IIf([Hire Date] Between #12/1/2003# And #2/29/2004# And [Bonus1]=800,-600,IIf([Hire Date] Between #9/1/2003# And #12/31/2003# And [Bonus1]=800,-400,0)))) Bonus1: IIf([SumOf1st Quarter]=0 And [SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,800,IIf([SumOf2nd Quarter]=0 And [SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,400,IIf([SumOf3rd Quarter]=0 And [SumOf4th Quarter]=0,200,IIf([SumOf4th Quarter]=0,100,0)))) Maybe I should rewrite the whole thing from scratch because whomever wrote it, started it backwards, with $800 and then deducted by taking into account when they were hired because they are only eligible the first quarter starting after their 90 day probation. Thanks for any responses Reply 4. circuit slave Feb 5, 1:50 am show options Newsgroups: microsoft.public.access.queries From: "circuit slave" - Find messages by this author Date: 5 Feb 2006 01:50:25 -0800 Local: Sun, Feb 5 2006 1:50 am Subject: Please help me fix this flawed query formula Reply | Reply to Author | Forward | Print | Individual Message | Show original | Remove | Report Abuse Thanks for helping, Tom. Actually, yes. There are two tables. One is the Employee Table that has all the info on the employee, i.e., employee ID, hire date, address, DOB, etc. Then there is the Employee Events table, which includes all the attendance, points if any, vac time, PTO, etc. The report which is run gets the info from these two tables I assume. Also the Employee Events table has a column called "date" which is the date the info is entered. Is that a problem? having a column called "date" ? thanks for the help Reply 5. Tom Ellison Feb 5, 8:40 am show options Newsgroups: microsoft.public.access.queries From: "Tom Ellison" - Find messages by this author Date: Sun, 5 Feb 2006 10:40:22 -0600 Local: Sun, Feb 5 2006 8:40 am Subject: Please help me fix this flawed query formula Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse Dear Slave: In future, please do not delete all the history from your post. It's easier to follow what is happening by referring to what has occurred before. Also, if you would, top post to it, so the whole thing reads in reverse chronological order. That helps me. I answer a few hundred of these a year, and must refer back to see where each thread is. Thanks. That helps me greatly. Now, on to the task at hand. Of course, it is expected there will be other columns. We need only reference those that affect the mechanism we're creating. Are you willing and able to create the calendar table I requested. Otherwise, you'll be modifying the query every time a new year rolls around, and it can't be made to work for previous years after you've modified it. This is not such a good programming practice, in my opinion. I expect this will be a bit of an endeavor on my part to put something together. I'm reasonably confident I can do it, but it isn't fully formed yet. I'd rather not do the additional, duplicate work needed to create tables and enter data. Would you be willing to put together a reduced database for me with just the relevant tables and email that. I would like to use that as a basis to craft a solution. Just these tables now, no forms or reports. When you make a separate database like this, just copy objects from your existing database (import is a function available on the database menu). When you have finished, use Windows Explorer to rename this file, changing the file extension from MDB to XXX. Then zip the file and attach that to the email. Having a column named "date" is a small problem, as it makes coding ambiguous. There's a function named date. It would have been better to name the column EventDate, for example. Sometimes there is more than one "date" needed in a table, and reducing ambiguity for yourself and for Access is a good practice. It isn't essential however. You must always place square brackets around "date" when it is a column name to avoid ambiguity, or you may get a very nasty surprise some day, when it is misinterpreted and gives very mysterious results. By "some day" I do not mean that your programming will suddenly malfunction because Access has changed how it interprets this. Rather, it will happen in some new work you are doing. But it will be very nasty, because it will be unexplainable, very hard to find. Not what you want to spend your time doing, trying to track this down so you can fix it! Please let me know if you want to proceed on this basis. Tom Ellison Thanks alot, Tom, and, yes, I can create that calendar table, and email you the database in reduced form. |
#8
|
|||
|
|||
Please help me fix this flawed query formula
Will do, thanks!
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cross tab query construction with Subqueries | Steven Cheng | Running & Setting Up Queries | 7 | February 13th, 2006 06:52 PM |
AHHHH-Get Data from Multiple Excel workbooks | JAA149 | General Discussion | 5 | October 30th, 2005 05:19 PM |
Formula checking multiple worksheets | sonic-the-mouse | Worksheet Functions | 11 | June 6th, 2005 06:37 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |