OK. Thanks. I was able to overcome this one problem. But now a similar
one with the same tables is the following:
What I am trying to do is determine the Adjusted Base Promotion Date to
calculate when the next pay raise is due. The AdjBasePromoDate is based
on a floating date dependent on job attendance and performance. If
Attendance&Performance for the work week = "yes" then Base Promotion Date
= Hire Date or Last Promotion Date whichever is greatest and is not really
a floating date. However, if Attendance&Performance = "no" then Base
Promotion Date = this Work Ending Date until the next time when
Attendance&Performance = "no". At this time, this would become the new
Base Promotion Date thus a floating date. The actual date of promotion is
dependent on job title defined as days till next promotion. The SQL I
have so far is:
SELECT [tblWeeklyHours&Attendance].strFirstName,
[tblWeeklyHours&Attendance].strLastName,
[tblWeeklyHours&Attendance].dtmWorkEndingDate,
[tblWeeklyHours&Attendance].ysnAttendancePerformance,
[qryCurrentJobTitle&BasePromotionDate].dtmBasePromDate
FROM (([tblWeeklyHours&Attendance] INNER JOIN (tblPersonalInfo INNER JOIN
[qryCurrentJobTitle&BasePromotionDate] ON (tblPersonalInfo.strLastName =
[qryCurrentJobTitle&BasePromotionDate].strLastName) AND
(tblPersonalInfo.strFirstName =
[qryCurrentJobTitle&BasePromotionDate].strFirstName)) ON
([tblWeeklyHours&Attendance].strLastName =
[qryCurrentJobTitle&BasePromotionDate].strLastName) AND
([tblWeeklyHours&Attendance].strFirstName =
[qryCurrentJobTitle&BasePromotionDate].strFirstName)) LEFT JOIN
tblJobPromotions ON (tblPersonalInfo.strJobTitle =
tblJobPromotions.strJobTitle) AND (tblPersonalInfo.strLastName =
tblJobPromotions.strLastName) AND (tblPersonalInfo.strFirstName =
tblJobPromotions.strFirstName)) INNER JOIN [tblJobTitle&PayRates] ON
tblPersonalInfo.strJobTitle = [tblJobTitle&PayRates].strJobTitle
GROUP BY [tblWeeklyHours&Attendance].strFirstName,
[tblWeeklyHours&Attendance].strLastName,
[tblWeeklyHours&Attendance].dtmWorkEndingDate,
[tblWeeklyHours&Attendance].ysnAttendancePerformance,
[qryCurrentJobTitle&BasePromotionDate].dtmBasePromDate;
Thank you !
Jerry Whittle said:
There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it
here.
Information on primary keys and relationships would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Rick Stahl said:
Sorry for not being clear. I actually have dates in the table not
abc's.
As for the SQL statement, Inner Join, and Left Join I don't understand.
What is happening though is if an employee's dtmHiredDate is less than
all
dtmPromDate, then the largest dtmPromoDate is being returned independent
of
who's Promotion it actually was. Thanks !
message
OK first things first: Do you have actual dates stored in Date/Time
fields
or
the abc's per your example?
Next you need to provide the entire SQL statement for the query.
Something
like joining on the wrong fields or an Inner Join where you need a
Left
Join
could cause problems. For example if there isn't a dtmPromoDate, that
could
cause problems.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
I have 2 tables and 1 query:
tblPersonalInfo containing: dtmHiredDate for Employees (a for Bill, b
for
Sam, c for Tom)
tblJobPromotions containing: dtmPromoDate for Employees (d for Bill,
e
for
Bill)
Given: promotions are based on either Hired Date or most recent
Promotion
Date
Given: a < d < c < e < b
Question: I would like to determine the Date that will be used as the
Base
Date for Next Promotion for each employee.
My query is the following:
dtmBasePromDate: IIf
(tblJobPromotions![dtmPromoDate]>tblPersonalInfo![dtmHiredDate],tblJobPromotions![dtmPromoDate],tblPersonalInfo![dtmHiredDate])
This does not work properly because I get the following:
e for Bill (correct)
b for Sam (correct)
e for Tom (incorrect) should be c for Tom
For some reason since Bill's PromoDate > Tom's HireDate it is being
returned. Why ? ? ? Any help is greatly appreciated !
