Criteria for TerminateDate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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.
 
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):];
 
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?

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):];
Click to expand...
 
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);


Tandy said:
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):];
Click to expand...
 
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
 
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
 
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:]));
 
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
 
Back
Top