Deducting values that meet certain criteria or requirements

[van_slanzar]

I have a dilemma with the database I'm working on. Bear with me because I
deal with sensitive information but I'll try to make my problem clear as
possible.

Employees each have records on their file. They basically collect money and
are audited. At the end they are either short or over. I deal with shorts.
Each employee are allowed a one time opportunity to have their first short to
be reduced by 7 bucks. It's a one time thing during their employment with the
company. So I need a way to automate Access so that it automatically deducts
the 7 from their first short but not their subsequent future short. It only
happens once. Problem is is that the database I created only has records for
2005 and some have shorts dating back in previous years. The idea I had to
get around it was to just create a check box for each employee that says if
they are still eligible to get that dedcution meaning they were never short.
So I need a function or something that makes Access look at that checkbox to
see if it should deduct the 7 on their first short ever but not deduct it
from future shorts. The short also have dates on when it happened if that
makes it easier. I hope someone can help me because this is an important
project.

Thanks in advance. If you need more info I'll try my best to make it clearer.

 

[James A. Fortune]

I have a dilemma with the database I'm working on. Bear with me because I
deal with sensitive information but I'll try to make my problem clear as
possible.

Employees each have records on their file. They basically collect money and
are audited. At the end they are either short or over. I deal with shorts.
Each employee are allowed a one time opportunity to have their first short to
be reduced by 7 bucks. It's a one time thing during their employment with the
company. So I need a way to automate Access so that it automatically deducts
the 7 from their first short but not their subsequent future short. It only
happens once. Problem is is that the database I created only has records for
2005 and some have shorts dating back in previous years. The idea I had to
get around it was to just create a check box for each employee that says if
they are still eligible to get that dedcution meaning they were never short.
So I need a function or something that makes Access look at that checkbox to
see if it should deduct the 7 on their first short ever but not deduct it
from future shorts. The short also have dates on when it happened if that
makes it easier. I hope someone can help me because this is an important
project.

Thanks in advance. If you need more info I'll try my best to make it clearer.

Why not have a separate table called tblFirstShort that has the first
"short" for each employee (EmployeeID, ShortAmount, DateOfShort). Since
an employee can only show up once, the EmployeeID can be used as a
primary key. If the employee is not in the table then they've never had
a "short." The SQL for checking would be something like:

SELECT - Count(EmployeeID) AS Eligible FROM tblFirstShort WHERE
EmployeeID=3;

or even:

boolEligible = (DCount("EmployeeID", "tblFirstShort", "EmployeeID=3") = 0)

These would actually be implemented something like:

strSQL = "SELECT - Count(EmployeeID) AS Eligible FROM tblFirstShort
WHERE EmployeeID=" & Nz(cbxEmployeeID.Value) & ";"

boolEligible = (DCount("EmployeeID", "tblFirstShort", "EmployeeID=" &
Nz(cbxEmployeeID.Value)) = 0)

behind a form that also logs first shorts to tblFirstShort behind the
scenes. It doesn't matter how far back their first short happened.

James A. Fortune

 

[tina]

well, it's clear enough what you're trying to do, and using a checkbox
(Yes/No field) on each employee record to identify those employees with no
previous shorts (Yes = had a previous short) makes sense.

exactly when and how you use the value of the PriorShorts field depends on
what your process is. it's hard to suggest a specific solution without a
specific scenario. in general - in a query, form, or report, you could
probably use an IIf() function, as

IIf(PriorShort = True, ShortValue, ShortValue - 7)

or you might use a VBA function, as

Public Function isShort(ByVal dblValue As Double, _
ByVal blnPriors As Boolean ) As Double

' note that Currency might be a better choice of
' data type, rather than Double.

If blnPriors Then
isShort = dblValue
Else
isShort = dblValue - 7
End If

End Function

hth

I have a dilemma with the database I'm working on. Bear with me because I
deal with sensitive information but I'll try to make my problem clear as
possible.

Employees each have records on their file. They basically collect money and
are audited. At the end they are either short or over. I deal with shorts.
Each employee are allowed a one time opportunity to have their first short to
be reduced by 7 bucks. It's a one time thing during their employment with the
company. So I need a way to automate Access so that it automatically deducts
the 7 from their first short but not their subsequent future short. It only
happens once. Problem is is that the database I created only has records for
2005 and some have shorts dating back in previous years. The idea I had to
get around it was to just create a check box for each employee that says if
they are still eligible to get that dedcution meaning they were never short.
So I need a function or something that makes Access look at that checkbox to
see if it should deduct the 7 on their first short ever but not deduct it
from future shorts. The short also have dates on when it happened if that
makes it easier. I hope someone can help me because this is an important
project.

Thanks in advance. If you need more info I'll try my best to make it

clearer.

 

[Gino]

Hi Van Slanzar,
the idea of having a check mark in the Employees table to indicate if the
employee is eligible for the deduction is practical and the function you
would have to create is rather straight forward.

For example, you could have a button on the form which could invoke an
update query. The query would globally compare the actual cash with the
expected for each cashier and apply the deduction only if the check box is
false (i.e. no deduction was ever applied before).
The same query would also automatically change the check box from false to
true so the deduction will not be applied again.
By running the query once every time the money is collected, you would
expose the entire database to the comparison and update.

As a little test, I created a table with these fields:
-Employee Name (text)
-Actual (Currency)
-Expected (Currency)
-DeductApplied (Yes/No)

and then applied the query below which automatically increases the Actual by
$7 (when the Expected is higher than the Actual) and sets the check box to
true.

UPDATE tblDeduct SET tblDeduct.actual = [actual]+7, tblDeduct.DeductApplied
= True
WHERE (([Expected]>[actual] And [deductapplied]=False));

On the form itself, you should lock the check box so that the status
cannot be changed accidentally.

Note:
I assume that you will actually have two related tables, one with the
Employees data and one with the Money Collection data. The basic update query
will have to bring in both tables but the concept is the same.

I hope this helps
Gino

 

[van_slanzar]

Thanks to everyone who responded to my dilemma.

After consulting with the higher ups and being given the full picture the
problem isn't as bad as I thought. I don't actually need the check marks
because each record has a "status" field value (text format). If the status
says "allowed" for that record then the value has been deducted already. The
problem now however is that prior to a certain "mm/dd/yyyy" date, employees
were eligible for a $5 deduction only. The old database deducted the $5
already. It has been increased however to $7 after said mm/dd/yyyy date. So
now what I need to do is to take all records with the status "allowed" and
before mm/dd/yyyy date and deduct $2 to complete the full $7 deduction which
is now in effect. And any record with the status "allowed" after the
mm/dd/yyyy date would be deducted $7 since they have never been deducted
before.

Also when entering new shorts, the database needs to have the ability to
recognized that if the short has the status allowed and it is after said
mm/dd/yyyy date that it has to deduct $7 out of the short automatically. The
problem seems real simple unfortunately I'm not adept at SQL or Visual Basic.

Thanks in advance again if anyone can help.

PS Is it common or possible for Access databases to be linked to highly
specialized databases outsourced and contracted to someone who built the
system from scratch? Unfortunately I can't give you specifics about the way
it was built because even the higher ups don't know it.

 

[tina]

well, first let's clear up just what the status value "allowed" really
means.

If the status says "allowed" for that record then the value has been
deducted already.

that sounds like the status value "allowed" means that a deduction has
already been given to that employee once. (forget dates and dollar amounts
here, we're talking strictly about the meaning of the term "allowed" in the
record.)

any record with the status "allowed" after the mm/dd/yyyy date would
be deducted $7 since they have never been deducted before.

that seems to say that the status value "allowed" means that no deduction
has ever been given to that employee.

please clarify, because the meaning of the status value is pivotal to a
solution.

 

[van_slanzar]

My apologies for not clearing it up. The status "allowed" means that the
short was the first time the employee was short and as a result received the
deduction benefit. However, prior to the mm/dd/yyyy date they were eligible
only for a $5 deduction. It has been increased to $7. The $5 has already been
deducted before but now an additional $2 has to be deducted in order to meet
the new requirement of $7. All records that are in the database prior to the
mm/dd/yyyy date with the allowed status has already been deducted $5. I have
to go back and deduct an additional $2 for the full $7. Any record after the
mm/dd/yyyy date or new record entered with the allowed status needs to be
decuted $7. I hope that clears it up. My apologies for not being clear.

 

[tina]

okay, then, once the deduction ($2 or $7) has been applied, what does the
status change to?

 

[Gino]

Hi Van Slanzar,
what I would suggest is as follows:

1) Create an update query which normalizes the situation for all shorts
before that date (say 1/1/2004) by adding $2 whenever the Status is
"Allowed". You would run this query ONLY once on the database.

The query would look someting like this:

UPDATE tblDeduct SET tblDeduct.actual = [Actual]+2
WHERE (((tblDeduct.Status)="Allowed") AND
((tblDeduct.DateAllowed)<#1/1/2004#));


After your database is updated, then you might consider using the suggestion
on my previous message. You would have to modify such Update Query slightly,
so that you can use the "status" field instead of the check mark.
You would run the query from your form by clicking on a button, (after money
collection) and would be similar to the one below:

UPDATE tblDeduct SET tblDeduct.actual = [actual]+7, tblDeduct.DateAllowed =
Date(), tblDeduct.Status = "Allowed"
WHERE (((tblDeduct.expected)>[actual]));

Please note that the query does not take into consideration the possibility
that the short might be for instance $5 instead of $7 or above, so the
adjustment is $7 no matter what the real shortage is.
If you wanted to check for different shortages, then additional conditions
would have to be applied.

I hope this will assist you in your project.
Gino

 

[Gino]

Van Slanzar,
a quick correction to my previous message:

The Update Query you should run form your form after money collection, must
apply only to records for which no short had occured before ("Status" is null
or <> from "Allowed") so it should be:

UPDATE tblDeduct SET tblDeduct.actual = [actual]+7, tblDeduct.DateAllowed =
Date(), tblDeduct.Status = "Allowed"
WHERE (((tblDeduct.Status) Is Null Or (tblDeduct.Status)<>"Allowed") AND
((tblDeduct.expected)>[actual]));

By the way, the query also enters the date when the deduction occurs.
--------------------
Regarding your P.S. in your message below, I am not quite sure I understand
what you mean. Are you asking if your current database could be maintained
and serviced by some entity other than the company originally created it?

Quoted from your message...
"PS Is it common or possible for Access databases to be linked to highly
specialized databases outsourced and contracted to someone who built the
system from scratch? Unfortunately I can't give you specifics about the way
it was built because even the higher ups don't know it."

_____________________________________________________________________

Hi Van Slanzar,
what I would suggest is as follows:

1) Create an update query which normalizes the situation for all shorts
before that date (say 1/1/2004) by adding $2 whenever the Status is
"Allowed". You would run this query ONLY once on the database.

The query would look someting like this:

UPDATE tblDeduct SET tblDeduct.actual = [Actual]+2
WHERE (((tblDeduct.Status)="Allowed") AND
((tblDeduct.DateAllowed)<#1/1/2004#));


2) After your database is updated, then you might consider using the suggestion
on my previous message. You would have to modify such Update Query slightly,
so that you can use the "status" field instead of the check mark.
You would run the query from your form by clicking on a button, (after money
collection) and would be similar to the one below:

UPDATE tblDeduct SET tblDeduct.actual = [actual]+7, tblDeduct.DateAllowed =
Date(), tblDeduct.Status = "Allowed"
WHERE (((tblDeduct.expected)>[actual]));

Please note that the query does not take into consideration the possibility
that the short might be for instance $5 instead of $7 or less, so the
adjustment is $7 no matter what the real shortage is.
If you wanted to check for different shortages, then additional conditions
would have to be applied.

I hope this will assist you in your project.
Gino

Thanks to everyone who responded to my dilemma.

After consulting with the higher ups and being given the full picture the
problem isn't as bad as I thought. I don't actually need the check marks
because each record has a "status" field value (text format). If the status
says "allowed" for that record then the value has been deducted already. The
problem now however is that prior to a certain "mm/dd/yyyy" date, employees
were eligible for a $5 deduction only. The old database deducted the $5
already. It has been increased however to $7 after said mm/dd/yyyy date. So
now what I need to do is to take all records with the status "allowed" and
before mm/dd/yyyy date and deduct $2 to complete the full $7 deduction which
is now in effect. And any record with the status "allowed" after the
mm/dd/yyyy date would be deducted $7 since they have never been deducted
before.

Also when entering new shorts, the database needs to have the ability to
recognized that if the short has the status allowed and it is after said
mm/dd/yyyy date that it has to deduct $7 out of the short automatically. The
problem seems real simple unfortunately I'm not adept at SQL or Visual Basic.

Thanks in advance again if anyone can help.

PS Is it common or possible for Access databases to be linked to highly
specialized databases outsourced and contracted to someone who built the
system from scratch? Unfortunately I can't give you specifics about the way
it was built because even the higher ups don't know it.