Criteria for TerminateDate

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tandy - 29 Dec 2005 17:30 GMT

Hey everyone! OK, I'm a little stuck. I have an append query with 3 fields,
EmployeeID, Premium & Premium Month, that append to field of the same name in
a table. The 4th table is UNUMTerminateDate. When I run the append I type in
a date to be entered under PremiumMonth. This what I would like to happen:

After entering a date for the PremiumMonth I would like for the query to
look & see if there are any employees with a Termination Date & if the
Termination is less than the Premium Month to not append that employee.

Can anyone help? It would be much appreciated!

Thanks,
Tandy

Reply to this Message

[MVP] S.Clark - 29 Dec 2005 18:01 GMT

The following query finds all emps with Premium less than Termination, or
where the Termination is blank

Select EmpID from tablename
WHERE PremiumMonth < Termination OR Termination Is Null

Signature

Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

> Hey everyone! OK, I'm a little stuck. I have an append query with 3
> fields,
[quoted text clipped - 12 lines]
> Thanks,
> Tandy

Reply to this Message

Tandy - 29 Dec 2005 18:16 GMT

Hey S. Clark! I don't think you understand what I am asking. The append query
I'm using is pulling from another query pulling from different tables. It
pulls the EmployeeID, Premium & TerminationDate from the query. Then I, in
the append query, add a field, PremiumMonth. Then when I run the append query
it append EmployeeID, Premium & a Premium Month I enter as I run it. I just
don't want it to append employees & their premium if they have a termination
date less than the Premium Month I'm entering. I hope that makes more sense!
Thanks,
Tandy

> The following query finds all emps with Premium less than Termination, or
> where the Termination is blank
[quoted text clipped - 18 lines]
> > Thanks,
> > Tandy

Reply to this Message

KARL DEWEY - 29 Dec 2005 18:59 GMT

What is the tables/queries and field names that this append query will pull
data from?
I can build it if I know more information.

> Hey S. Clark! I don't think you understand what I am asking. The append query
> I'm using is pulling from another query pulling from different tables. It
[quoted text clipped - 28 lines]
> > > Thanks,
> > > Tandy

Reply to this Message

Tandy - 29 Dec 2005 19:22 GMT

Hi Karl! Oh Wow! Where to start?

Table: tblEmployees
Fields: EmployeeID, FirstName, MiddleInitial, LastName, UNUM,
UNUMTerminationDate

Table: tblAnnualSalaries
Fields: SalaryYear, AnnualSalary

Table: tblPremiumFactors
Fields: PremiumFactorYear (2005), EELifePremiumFactor (.00014),
EEAD&DPremiumFactor (.00002)

Query: qryPremiumFactors (pulls from tblPremiumFactors)
Fields: Same as tblPremiumFactors

Query: qryExpectedCoverage (Pulls from tblEmployees, tblAnnualSalaries &
qryPremiumFactors)
Fields: SalaryYear, EmployeeID, Name:[LastName] & ", " & [FirstName] & " " &
[MiddleInitial], AnnualSalary, EEBase:-(Int(-[AnnualSalary]*1.5/1000))*1000,
EELifePremiumFactor, EELifePremium: [EEBase]*[EELifePremiumFactor],
EEAD&DPremiumFactor, EEAD&DPremium:[EEBase]*[EEAD&DPremiumFactor],
Premium:[EELifePremium]+[EEAD&DPremium], UNUM (Criteria "Yes"),
UNUMTerminationDate

Append Query: qryExpectedMonthlyPremiumsAppend (Pulls from
qryExpectedCoverage)
Fields: EmployeeID, Premium, PremiumMonth:[Type Month to Be Entered
(99/0000):], UNUMTerminationDate

EmployeeID, Premium & PremiumMonth append to fields of the same name in a
table called tblUNUMMonthlyPremiums.

Also the SQLs for the queries are below (in case that's easier for you to
use). I hope I included everything! Feel free if you need additional
information or clarification! Thank you so much for your help!

Tandy

SQLs:
Query: qryPremiumFactors
SELECT tblPremiumFactors.PremiumFactorYear,
tblPremiumFactors.EELifePremiumFactor, tblPremiumFactors.[EEAD&DPremiumFactor]
FROM tblPremiumFactors
WHERE (((tblPremiumFactors.PremiumFactorYear)=[Type Premium Factors Year:]));

Query: qryExpectedCoverage
SELECT tblAnnualSalaries.SalaryYear, tblEmployees.EmployeeID, [LastName] &
", " & [FirstName] & " " & [MiddleInitial] AS Name,
tblAnnualSalaries.AnnualSalary, -(Int(-[AnnualSalary]*1.5/1000))*1000 AS
EEBase, qryPremiumFactors_1.EELifePremiumFactor,
[EEBase]*[EELifePremiumFactor] AS EELifePremium,
qryPremiumFactors_1.[EEAD&DPremiumFactor], [EEBase]*[EEAD&DPremiumFactor] AS
[EEAD&DPremium], [EELifePremium]+[EEAD&DPremium] AS Premium,
tblEmployees.LastName, tblEmployees.UNUMTerminationDate, tblEmployees.UNUM
FROM qryPremiumFactors AS qryPremiumFactors_1, tblAnnualSalaries INNER JOIN
tblEmployees ON tblAnnualSalaries.EmployeeID = tblEmployees.EmployeeID
WHERE (((tblAnnualSalaries.SalaryYear)=[Type Salary Year:]) AND
((tblEmployees.UNUM)="Yes"))
ORDER BY tblEmployees.EmployeeID;

Append Query: qryExpectedMonthlyPremiumsAppend
INSERT INTO tblUNUMMonthlyPremiums ( EmployeeID, Premium, PremiumMonth )
SELECT qryExpectedCoverage.EmployeeID, qryExpectedCoverage.Premium, [Type
Month To Be Entered (99/0000):] AS PremiumMonth
FROM qryExpectedCoverage
ORDER BY [Type Month To Be Entered (99/0000):];

Reply to this Message

John Spencer - 29 Dec 2005 21:43 GMT

All you need to do is modify the last query, by adding criteria to it. I think
the following would work except for the problem of the premimum month being
stored as text.

Append Query: qryExpectedMonthlyPremiumsAppend
INSERT INTO tblUNUMMonthlyPremiums ( EmployeeID, Premium, PremiumMonth )
SELECT qryExpectedCoverage.EmployeeID, qryExpectedCoverage.Premium, [Type
Month To Be Entered (99/0000):] AS PremiumMonth
FROM qryExpectedCoverage
WHERE PremiumMonth is Null Or PremiumMonth < [Type Month To Be Entered (99/0000):];

I do see a problem with this. If premimum month is 09/2004 that is less than
12/2003. The comparison is being done on a string value (text), not a date
value. Dates have a day, a month, and a year.

You either need to change the format of Premium month to "yyyy/mm" or store an
actual date - arbritrarily using the 1st of the month would work.

If you are stuck and cannot change the data, you can try the following as the
where clause with your current setup.

WHERE PremiumMonth is Null OR
Right(PremiumMonth,4) & Left(PremiumMonth,2) < Right([Type Month To Be Entered
(99/0000):],4) & Left([Type Month To Be Entered (99/0000):],2)

That should change 09/2005 to "200509" in the WHERE clause of the query. That
will correctly compare with greater than and less than.

> Hi Karl! Oh Wow! Where to start?
>
[quoted text clipped - 63 lines]
> FROM qryExpectedCoverage
> ORDER BY [Type Month To Be Entered (99/0000):];

Reply to this Message

Tandy - 30 Dec 2005 17:46 GMT

Hey John!

The PremiumMonth isn't stored as a text in tblUNUMMonthlyPremiums, it is
stored as a date. When you type in 9/2005 it is stored as 9/1/2005. I did
this so when the user was entering the premiums the premiums would be entered
on 9/1/2005 not the actual date the user was entering, like 9/16/2005. But I
could change that is need be.

I tried what you suggested. At first it give me no results. So I looked at
it and thought mayble the "WHERE PremiumMonth Is Null..." was suppose to be
"WHERE UNUMTerminationDate..." Then when I tried it again it excluded anyone
who had a UNUMTerminationDate. This isn't what I was hoping for.

I guess what I need is for the query to ask for the PremiumMonth I am
entering, say 9/2005, and then look to see who had UNUMTerminatioDates. If
the UNUMTerminationDate is before 9/2005, like 8/30/2005, they should not be
included. However if the UNUMTerminationDate is 9/30/2005 or 10/31/2005, they
still need to be included.

Thank you so much for you help!
Tandy

Reply to this Message

KARL DEWEY - 29 Dec 2005 22:06 GMT

Use this for your append query --

INSERT INTO tblUNUMMonthlyPremiums ( EmployeeID, Premium, PremiumMonth )
SELECT qryExpectedCoverage.EmployeeID, qryExpectedCoverage.Premium,
DateSerial(Right([Type Month To Be Entered (99/0000):],4),Left([Type Month To
Be Entered (99/0000):],2),1) AS PremiumMonth
FROM qryExpectedCoverage
WHERE
((([UNUMTerminationDate]-Day([UNUMTerminationDate])+1)>DateSerial(Right([Type
Month To Be Entered (99/0000):],4),Left([Type Month To Be Entered
(99/0000):],2),1) Or ([UNUMTerminationDate]-Day([UNUMTerminationDate])+1) Is
Null))
ORDER BY DateSerial(Right([Type Month To Be Entered
(99/0000):],4),Left([Type Month To Be Entered (99/0000):],2),1);

> Hi Karl! Oh Wow! Where to start?
>
[quoted text clipped - 63 lines]
> FROM qryExpectedCoverage
> ORDER BY [Type Month To Be Entered (99/0000):];

Reply to this Message

Tandy - 30 Dec 2005 17:48 GMT

Hey Karl!

I tried what you suggested and got a message saying "This is expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many compllicated elements. Try
simplifying the expression by assigning parts of the expression to variables."

Sorry! Thanks for you help!
Tandy

Reply to this Message

KARL DEWEY - 30 Dec 2005 22:07 GMT

I had to edit some of your queries. Rename your queries that I am posting
with a prefix of X- And try these.
qryExpectedCoverage---
SELECT tblAnnualSalaries.SalaryYear, tblEmployees.EmployeeID, [LastName] &
", " & [FirstName] & " " & [MiddleInitial] AS Name,
tblAnnualSalaries.AnnualSalary, -(Int(-[AnnualSalary]*1.5/1000))*1000 AS
EEBase, qryPremiumFactors_1.EELifePremiumFactor,
[EEBase]*[EELifePremiumFactor] AS EELifePremium,
qryPremiumFactors_1.[EEAD&DPremiumFactor], [EEBase]*[EEAD&DPremiumFactor] AS
[EEAD&DPremium], [EELifePremium]+[EEAD&DPremium] AS Premium,
tblEmployees.LastName, tblEmployees.UNUMTerminationDate, tblEmployees.UNUM
FROM qryPremiumFactors AS qryPremiumFactors_1, tblAnnualSalaries INNER JOIN
tblEmployees ON tblAnnualSalaries.EmployeeID = tblEmployees.EmployeeID
WHERE (((tblAnnualSalaries.SalaryYear)=[Type Salary Year:]) AND
((tblEmployees.UNUM)="Yes"))
ORDER BY tblEmployees.EmployeeID;

qryPremiumFactors---
SELECT tblPremiumFactors.PremiumFactorYear,
tblPremiumFactors.EELifePremiumFactor, tblPremiumFactors.[EEAD&DPremiumFactor]
FROM tblPremiumFactors
WHERE (((tblPremiumFactors.PremiumFactorYear)=[Type Premium Factors Year:]));

> Hey Karl!
>
[quoted text clipped - 5 lines]
> Sorry! Thanks for you help!
> Tandy

Reply to this Message

Tandy - 30 Dec 2005 22:24 GMT

Hi Karl! I tried what you suggested and received the same message. Both the
qryExpectedCoverage and qryPremiumFactors work, I just get that message when
I try to run the append query. Any other ideas?

Thank you for your help, I really appreciate it!
Tandy

Reply to this Message