 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2007"floating" date query
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. 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 '==================================================== > I have a tblPersonalInfo containing information including dtmHiredDate and > strJobTitle. I have another tblPromotions containing info including [quoted text clipped - 11 lines] > > Any help is greatly appreciated. Thank you. 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. > Fix the date and table names as appropriate > [quoted text clipped - 45 lines] >> >> Any help is greatly appreciated. Thank you. 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 '==================================================== > Great ! Thank you John ! > [quoted text clipped - 59 lines] >>> >>> Any help is greatly appreciated. Thank you. 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 ! > Non-equi joins (joins where something is = to something are equi joins) > cannot be represented in the design view (query grid); [quoted text clipped - 76 lines] >>>> >>>> Any help is greatly appreciated. Thank you. 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.  SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > 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 [quoted text clipped - 110 lines] >>>>> >>>>> Any help is greatly appreciated. Thank you. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.