Please help me fix this flawed query formula

C

circuit slave

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
 
T

Tom Ellison

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 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.

Thanks for any responses
Click to expand...
 
T

Tom Ellison

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 said:
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 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.

Thanks for any responses
Click to expand...
Click to expand...
 
C

circuit slave

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
 
T

Tom Ellison

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
 
C

circuit slave

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.
Click to expand...
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
Click to expand...



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 -
Dear Slave:
Click to expand...
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
Click to expand...


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.
Click to expand...
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.
Click to expand...
The dates for each quarter are:
Click to expand...
1st (12/1 to 2/28)
2nd (3/1 to 5/31)
3rd (6/1 8/31)
4th (9/1 to 11/30)
Click to expand...
Here are the formulas (I know the year needs to be changed to '05 , but
the date is the same) :
Click to expand...
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))))
Click to expand...
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))))
Click to expand...
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.
Click to expand...
Thanks for any responses
Click to expand...
Click to expand...



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.
 
T

Tom Ellison

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 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.


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.
Click to expand...
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
Click to expand...



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 -
Dear Slave:
Click to expand...
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
Click to expand...


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.
Click to expand...
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.
Click to expand...
The dates for each quarter are:
Click to expand...
1st (12/1 to 2/28)
2nd (3/1 to 5/31)
3rd (6/1 8/31)
4th (9/1 to 11/30)
Click to expand...
Here are the formulas (I know the year needs to be changed to '05 , but
the date is the same) :
Click to expand...
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))))
Click to expand...
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))))
Click to expand...
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.
Click to expand...
Thanks for any responses
Click to expand...
Click to expand...



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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

URGENT HELP NEEDED WITH GETTING AVERAGES BY QUARTER 1
Parameter Query - Date IIF 7
Combining 3 Queries-Help me Please..cant think anymore 12
Hide query result from chart 2
Query Problem - Urgent! 2
Show query results by quarter even if blank 1
Sum $$ Order Totals Based on Date 3
Sales $$ Amt. by Quarter on Same Report 1

Top