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 are:
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" <
[email protected]> - Find messages by this
author
Date: Sun, 5 Feb 2006 01:42:31 -0600
Local: Sat, Feb 4 2006 11:42 pm
Subject: Re: 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
- 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 are:
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" <
[email protected]> - Find messages by this
author
Date: Sun, 5 Feb 2006 01:51:08 -0600
Local: Sat, Feb 4 2006 11:51 pm
Subject: Re: 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
- Hide quoted text -
- Show quoted text -
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:
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 said:
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 are:
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.
Reply
4. circuit slave
Feb 5, 1:50 am show options
Newsgroups: microsoft.public.access.queries
From: "circuit slave" <
[email protected]> - Find messages by this
author
Date: 5 Feb 2006 01:50:25 -0800
Local: Sun, Feb 5 2006 1:50 am
Subject: Re: 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" <
[email protected]> - Find messages by this
author
Date: Sun, 5 Feb 2006 10:40:22 -0600
Local: Sun, Feb 5 2006 8:40 am
Subject: Re: 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.
