"floating" date query

[Rick Stahl]

I have a tblPersonalInfo containing information including dtmHiredDate and
strJobTitle. I have another tblPromotions containing info including
dtmPromoDates and strPromoTitle. This table may contain duplicate
strFirstName and strLastName because of different dtmPromoDates and
strPromoTitles (because same person may have multiple promotions). Another
tblJobTitle_JobRates lists strJobTitles and curJobRates. A final
tblHoursWorked contains information including dtmPayEndDate.

I am having difficulty writing a query that would extract dates based on
HoursWorked with the appropriate JobTitle and JobRate for that date in time.
For a previous date, sometime I only get the most recent JobTitle.
Sometimes I get multiple listings because the same person may have multiple
promotions.

Any help is greatly appreciated. Thank you.

 

[John Spencer]

Fix the date and table names as appropriate

First query to get the correct PromoDate in a separate query

Select tblPromotions.PersonID
, HoursWorked.PayDate
, Max(dtmPromoDate) as EffectiveDate
FROM tblPromotions INNER JOIN tblHoursWorked
ON tblPromotions.PersonID = tblHoursWorked.PersonID
AND tblPromotions.PromoDate <= tblHoursWorked.PayDate
GROUP BY tblPromotions.PersonID
, HoursWorked.PayDate

You should be able to use that query as a source to get your remaining
information
SELECT *
FROM ((tblHoursWorked as W INNER JOIN qTheQuery as Q
ON W.PersonID = Q.PersonID and
W.PayDate = Q.EffectiveDate)
INNER JOIN tblPromotions as P
ON Q.PersonID = P.PersonID and
Q.EffectiveDate = P.dtmPromoDate)
INNER JOIN tblJobTitle_JobRates as J
ON J.strJobTitle = P.strJobTitle

You will probably want to add in tblPersonalInfo to this query.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Rick Stahl]

Great ! Thank you John !

I got the first query to select the correct PromoDate. One technical
problem I am having is an error when trying to display in Design view:
"Microsoft Access can't represent the join expression
tblPromotions.PromoDate<=tblHoursWorked.PayDate in Design view". I was
wondering if there was a way to fix this so I can visualize the
relationships between my tables. I think it would be easy for me to grasp
the concepts because SQL is something I am not familiar with. I was able to
modify SQL to your suggestions after I have made some manipulations in the
Design view. I believe the second part of your suggestion using this query
as a source may be quite challenging for me without the Design view.
Thanks.

 

[John Spencer]

Non-equi joins (joins where something is = to something are equi joins)
cannot be represented in the design view (query grid);

That should not be a problem for you in building the subsequent query.
A query that is included in another query is viewed as "just another
table" by Access.

Try it and see if it works.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Rick Stahl]

OK, great. I will give it a try. Before I do though I think I should
explain that attendance is also an important factor used to determine the
next promotion due date. If attendance has been continually acceptable
(noted every week when hours worked are entered) for every paydate then the
last promotion date (or hired date if no promotions yet) is the base
promotion date. If attendance is unacceptable for a given paydate then the
last paydate where attendance was acceptable becomes the new base promotion
date, not the last promotion date (or the hired date if no promotions yet).
Once the base promotion date has been determined, a pre-specified number of
days (dependent on job title) is added to it to determine the next promotion
date.

The info I have is tblPersonalInfo containing ID, HiredDate, HiredTitle
tblJobTitleRatesDays containing JobTitle, JobRate, DaysTillPromo
tblWeeklyHoursAttendance containing ID, PayDate, HoursWorked, Attendance
tblJobPromos containing ID, PromoDate, PromoTitle
qryTheQuery containing EffectiveDate

The logic is something like this: If tblWeeklyHoursAttendance.Attendance for
tblWeeklyHoursAttendance.PayDate equals yes, then BasePromoDate equals
either the tblPersonalInfo.HiredDate (no promotions yet) or the
qryTheQuery.dtmEffectiveDate (this is from the query you just helped with)
whichever is more recent, otherwise BasePromoDate equals the last
tblWeeklyHoursAttendance.PayDate where tblWeeklyHoursAttendance.Attendance
is yes. Then the NextPromoDate equals BasePromoDate +
tblJobTitleRatesDays.DaysTillPromo.

One thing that has been bothering me the whole time is whether the
tblJobPromos should be tied into the query you helped me with instead of
existing in a table. Why I am saying this is because sometimes the
HiredDate is the BasePromoDate (no promotions yet) while othertimes it is
the PromoDate (when there have been promotions). Any suggestions on this
matter are greatly appreciated. Thank you !

 

[John Spencer]

Sorry, I don't follow all that and I can't really spend all the time it
would take me to decipher it and develop a solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..