If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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. |
#2
|
|||
|
|||
"floating" date query
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 wrote: 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. |
#3
|
|||
|
|||
"floating" date query
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" wrote in message ... 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 wrote: 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. |
#4
|
|||
|
|||
"floating" date query
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 wrote: 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" wrote in message ... 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 wrote: 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. |
#5
|
|||
|
|||
"floating" date query
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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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. |
#6
|
|||
|
|||
"floating" date query
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 .. "Rick Stahl" wrote in message ... 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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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. |
Thread Tools | |
Display Modes | |
|
|