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
|
|||
|
|||
Query joining two tables with different dates
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! |
#2
|
|||
|
|||
Query joining two tables with different dates
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/For...arted/201005/1 |
#3
|
|||
|
|||
Query joining two tables with different dates
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 |
#4
|
|||
|
|||
Query joining two tables with different dates
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/For...arted/201005/1 |
#5
|
|||
|
|||
Query joining two tables with different dates
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/For...arted/201005/1 |
#6
|
|||
|
|||
Query joining two tables with different dates
I was able to filter the unmatched data into its own table and narrow down
the data from over 100,000 records to about 200 so it should go faster now. Now that I know that every record in the monitor data will not match the scorecard data and will need to roll up to the nearest date can the SQL be updated as such? KenSheridan wrote: 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 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/For...arted/201005/1 |
#7
|
|||
|
|||
Query joining two tables with different dates
The only other way I can think of handling this would be to first create a
query which returns the row_date and the latest unmatched date after that for each row in tbl_ScorecardStep1: SELECT row_date, (SELECT NZ(MIN(row_date),#2099-01-01#)-1 FROM tbl_ScorecardStep1 AS SS2 WHERE SS2.RID =SS1.RID AND SS2.row-date SS1.row_date) AS EndDate, Agent, RID FROM tbl_ScorecardStep1 AS SS1; Save the above query as Q1 say. The join it to tbl_Monitors_ScorecardStep3 like so: SELECT Q1.rowdate, Q1.agent, SUM(SumOfTPE) As TotalTPE, SUM(SumOfTPP) As TotalTPP, FROM Q1, tbl_Monitors_ScorecardStep3 WHERE tbl_Monitors_ScorecardStep3.Evaldate BETWEEN Q1.row_date AND Q1.EndDate GROUP BY Q1.rowdate, Q1.agent; Whether this will work or not I wouldn't like to say without testing it for myself, which I obviously can't do, but I think the logic is sound. Even then I've no idea how it might perform. Ken Sheridan Stafford, England bscott wrote: I was able to filter the unmatched data into its own table and narrow down the data from over 100,000 records to about 200 so it should go faster now. Now that I know that every record in the monitor data will not match the scorecard data and will need to roll up to the nearest date can the SQL be updated as such? 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 [quoted text clipped - 6 lines] 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/For...arted/201005/1 |
#8
|
|||
|
|||
Query joining two tables with different dates
Because of the amount of rows I'm dealing with I think that the second
solution will be less feasible. The first solution that you gave me seems to be on the right track, but of the 230 unmatched records it only matches half of them, 115. The reason why I asked if the SQL could be updated is now that I know all of the records will be unmatched it no longer needs to check for matches, only roll all records up to the previous date worked. However, if this fact will not simplify the SQL at all then disregard. But, would you have any idea why it doesn't match all of the records? I can send you samples of the tables or any other data that might be of use in resolving this. KenSheridan wrote: The only other way I can think of handling this would be to first create a query which returns the row_date and the latest unmatched date after that for each row in tbl_ScorecardStep1: SELECT row_date, (SELECT NZ(MIN(row_date),#2099-01-01#)-1 FROM tbl_ScorecardStep1 AS SS2 WHERE SS2.RID =SS1.RID AND SS2.row-date SS1.row_date) AS EndDate, Agent, RID FROM tbl_ScorecardStep1 AS SS1; Save the above query as Q1 say. The join it to tbl_Monitors_ScorecardStep3 like so: SELECT Q1.rowdate, Q1.agent, SUM(SumOfTPE) As TotalTPE, SUM(SumOfTPP) As TotalTPP, FROM Q1, tbl_Monitors_ScorecardStep3 WHERE tbl_Monitors_ScorecardStep3.Evaldate BETWEEN Q1.row_date AND Q1.EndDate GROUP BY Q1.rowdate, Q1.agent; Whether this will work or not I wouldn't like to say without testing it for myself, which I obviously can't do, but I think the logic is sound. Even then I've no idea how it might perform. Ken Sheridan Stafford, England I was able to filter the unmatched data into its own table and narrow down the data from over 100,000 records to about 200 so it should go faster now. [quoted text clipped - 7 lines] 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/For...arted/201005/1 |
#9
|
|||
|
|||
Query joining two tables with different dates
I figured out why it wasn't matching all of them and it was on my end. So
looks like it works as should! Thanks! If you are aware of a way to further simplify the SQL because of what I previously brought up that would be great, but otherwise, looks like I'm all set! bscott wrote: Because of the amount of rows I'm dealing with I think that the second solution will be less feasible. The first solution that you gave me seems to be on the right track, but of the 230 unmatched records it only matches half of them, 115. The reason why I asked if the SQL could be updated is now that I know all of the records will be unmatched it no longer needs to check for matches, only roll all records up to the previous date worked. However, if this fact will not simplify the SQL at all then disregard. But, would you have any idea why it doesn't match all of the records? I can send you samples of the tables or any other data that might be of use in resolving this. The only other way I can think of handling this would be to first create a query which returns the row_date and the latest unmatched date after that for [quoted text clipped - 31 lines] 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/For...arted/201005/1 |
#10
|
|||
|
|||
Query joining two tables with different dates
I think its also worth giving the second method a try. You'll probably find
it considerably faster than the first with the large number of rows you are dealing with. Have you tried it? Even better would be to use a JOIN in Q1 rather than a subquery: SELECT SS1.row_date, NZ(MIN(SS2.row_date),#2099-01-01#)-1 As EndDate, FROM tbl_ScorecardStep1 AS SS1 LEFT JOIN tbl_ScorecardStep1 AS SS2 ON SS1.rowdate SS2.row_date GROUP BY SS1.row_date; BTW if you open this query independently you may well find that the EndDate column is returned as a number, not a date. Don't worry, it's just the underlying number as which the date/time data type is implemented in Access. Normally this query would not be opened of course as its only purpose is to return a result table to be joined to tbl_Monitors_ScorecardStep3 in the final query. Ken Sheridan Stafford, England bscott wrote: I figured out why it wasn't matching all of them and it was on my end. So looks like it works as should! Thanks! If you are aware of a way to further simplify the SQL because of what I previously brought up that would be great, but otherwise, looks like I'm all set! Because of the amount of rows I'm dealing with I think that the second solution will be less feasible. [quoted text clipped - 13 lines] 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/For...arted/201005/1 |
|
Thread Tools | |
Display Modes | |
|
|