Query joining two tables with different dates
in [Getting Started]
|
From: bscott on 25 May 2010 13:25 I'm sure there is a simple solution to this, but being fairly new to Access I would appreciate some assistance... I have a query that joins two tables. One table has call details for each day that an agent works. The other table has details for each call that is graded for each agent. However, calls can be graded on days that the agent does not work. When joining the tables, the query will only display calls graded on dates that the agent works. This is what I would like to do to correct this: If there was a call graded on a day that the agent did not work, this data be rolled up to the last day that they did work. Current SQL: SELECT tbl_ScorecardStep1.row_date, tbl_ScorecardStep1.Agent, Count (tbl_Monitors_ScorecardStep3.SumOfTPE) AS CountOfSumOfTPE, Count (tbl_Monitors_ScorecardStep3.SumOfTPP) AS CountOfSumOfTPP FROM tbl_ScorecardStep1 LEFT JOIN tbl_Monitors_ScorecardStep3 ON (tbl_ScorecardStep1.RID = tbl_Monitors_ScorecardStep3.RID) AND (tbl_ScorecardStep1.row_date = tbl_Monitors_ScorecardStep3.EVALDATE) GROUP BY tbl_ScorecardStep1.row_date, tbl_ScorecardStep1.Agent; Example of tbl_ScorecardStep1: row_date Agent RID 5/3/2010 Frapples, Bob 26152 5/4/2010 Frapples, Bob 26152 5/13/2010 Frapples, Bob 26152 Example of tbl_Monitors_ScorecardStep3: EVALDATE RID SumOfTPE SumOfTPP 5/3/2010 26152 91 100 5/7/2010 26152 76 100 5/12/2010 26152 147 200 Current result: row_date Agent SumOfSumOfTPE SumOfSumOfTPP 5/3/2010 Frapples, Bob 91 100 5/4/2010 Frapples, Bob 5/13/2010 Frapples, Bob Desired result (5/1 and 5/12 monitor data is rolled up to the last day that the agent worked: row_date Agent SumOfSumOfTPE SumOfSumOfTPP 5/3/2010 Frapples, Bob 91 100 5/4/2010 Frapples, Bob 223 300 5/13/2010 Frapples, Bob Any help pointing me in the right direction is greatly appreciated!
From: KenSheridan via AccessMonster.com on 25 May 2010 18:48 Try this: SELECT row_date, Agent, (SELECT SUM(SumOfTPE) FROM tbl_Monitors_ScorecardStep3 WHERE tbl_Monitors_ScorecardStep3.RID = SS1.Rid AND tbl_Monitors_ScorecardStep3.EVALDATE >= SS1.row_date AND tbl_Monitors_ScorecardStep3.EVALDATE < (SELECT SELECT NZ(MIN(row_date),#2099-01-01#) FROM tbl_ScorecardStep1 AS SS2 WHERE SS2.RID = SS1.RID AND SS2.row_date > SS1.row_date)) AS TotalTPE, (SELECT SUM(SumOfTPP) FROM tbl_Monitors_ScorecardStep3 WHERE tbl_Monitors_ScorecardStep3.RID = SS1.Rid AND tbl_Monitors_ScorecardStep3.EVALDATE >= SS1.row_date AND tbl_Monitors_ScorecardStep3.EVALDATE < (SELECT NZ(MIN(row_date),#2099-01-01#) FROM tbl_ScorecardStep1 AS SS2 WHERE SS2.RID = SS1.RID AND SS2.row_date > SS1.row_date)) AS TotalTPP FROM tbl_ScorecardStep1 AS SS1 ORDER BY Agent, row_date; Calling the NZ function to return an artificially late date would not be necessary if the latest date per agent in tbl_Monitors_ScorecardStep3 cannot be later than the latest date per agent in tbl_ScorecardStep1. Ken Sheridan Stafford, England bscott wrote: >I'm sure there is a simple solution to this, but being fairly new to Access I >would appreciate some assistance... > >I have a query that joins two tables. One table has call details for each day >that an agent works. The other table has details for each call that is graded >for each agent. However, calls can be graded on days that the agent does not >work. When joining the tables, the query will only display calls graded on >dates that the agent works. > >This is what I would like to do to correct this: If there was a call graded >on a day that the agent did not work, this data be rolled up to the last day >that they did work. > >Current SQL: >SELECT tbl_ScorecardStep1.row_date, tbl_ScorecardStep1.Agent, Count >(tbl_Monitors_ScorecardStep3.SumOfTPE) AS CountOfSumOfTPE, Count >(tbl_Monitors_ScorecardStep3.SumOfTPP) AS CountOfSumOfTPP >FROM tbl_ScorecardStep1 LEFT JOIN tbl_Monitors_ScorecardStep3 ON >(tbl_ScorecardStep1.RID = tbl_Monitors_ScorecardStep3.RID) AND >(tbl_ScorecardStep1.row_date = tbl_Monitors_ScorecardStep3.EVALDATE) >GROUP BY tbl_ScorecardStep1.row_date, tbl_ScorecardStep1.Agent; > >Example of tbl_ScorecardStep1: >row_date Agent RID >5/3/2010 Frapples, Bob 26152 >5/4/2010 Frapples, Bob 26152 >5/13/2010 Frapples, Bob 26152 > >Example of tbl_Monitors_ScorecardStep3: >EVALDATE RID SumOfTPE SumOfTPP >5/3/2010 26152 91 100 >5/7/2010 26152 76 100 >5/12/2010 26152 147 200 > >Current result: >row_date Agent SumOfSumOfTPE SumOfSumOfTPP >5/3/2010 Frapples, Bob 91 100 >5/4/2010 Frapples, Bob >5/13/2010 Frapples, Bob > >Desired result (5/1 and 5/12 monitor data is rolled up to the last day that >the agent worked: >row_date Agent SumOfSumOfTPE SumOfSumOfTPP >5/3/2010 Frapples, Bob 91 100 >5/4/2010 Frapples, Bob 223 300 >5/13/2010 Frapples, Bob > >Any help pointing me in the right direction is greatly appreciated! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1
From: bscott via AccessMonster.com on 26 May 2010 09:38 Thanks Ken! When I tried this I received a dialogue saying "Syntax error in query expression '(SELECT SUM(SumOfTPE) FROM tbl_Monitors_ScorecardStep3 WHERE tbl_Monitors_ScorecardStep3.RID = SS1.Rid AND tbl_Monitors_ScorecardStep3.EVALDATE >= SS1.row_date AND tbl_Monitors_ScorecardStep3.EVALDATE < (SELECT SELECT NZ(MIN(r'. Am I doing something wrong? I just pasted your code in place of mine in the SQL view of the query. -- Message posted via http://www.accessmonster.com
From: bscott via AccessMonster.com on 26 May 2010 10:45 Actually, it looks like the error was due to two SELECTs in a row, I corrected that and am about 30 minutes into the query running. I will post how successful I am. Thanks again! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1
From: KenSheridan via AccessMonster.com on 26 May 2010 12:44
If it's taking that long its clearly not a practical solution. Make sure the RID, evaldate and row_date columns are all indexed; if they are not currently that might speed things up. Ken Sheridan Stafford, England bscott wrote: >Actually, it looks like the error was due to two SELECTs in a row, I >corrected that and am about 30 minutes into the query running. I will post >how successful I am. Thanks again! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1 |