Query joining two tables with different dates

[bscott]

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!

 

[KenSheridan via AccessMonster.com]

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 via AccessMonster.com]

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.

 

[bscott via AccessMonster.com]

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!

 

[KenSheridan via AccessMonster.com]

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 via AccessMonster.com]

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 via AccessMonster.com]

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.
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]

 

[bscott via AccessMonster.com]

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
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]

 

[bscott via AccessMonster.com]

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.
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]

 

[KenSheridan via AccessMonster.com]

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

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]

 

[bscott via AccessMonster.com]

I'm working on giving that a try right now Ken. While I'm working on this, I
thought that I would double check something with you. When I try to run the
JOIN qry it says that there is a syntax error at "FROM". When I remove the
comma from the end of "EndDate" the error goes away. Was this the correct
thing to do without messing up the qry?

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

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

[quoted text clipped - 6 lines]

 

[KenSheridan via AccessMonster.com]

Mea culpa! That was just a typo on my part when adapting the SQL statement
from a query on one of my own tables.

Ken Sheridan
Stafford, England

I'm working on giving that a try right now Ken. While I'm working on this, I
thought that I would double check something with you. When I try to run the
JOIN qry it says that there is a syntax error at "FROM". When I remove the
comma from the end of "EndDate" the error goes away. Was this the correct
thing to do without messing up the qry?

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

[quoted text clipped - 24 lines]

 

[John W. Vinson]

I'm working on giving that a try right now Ken. While I'm working on this, I
thought that I would double check something with you. When I try to run the
JOIN qry it says that there is a syntax error at "FROM". When I remove the
comma from the end of "EndDate" the error goes away. Was this the correct
thing to do without messing up the qry?

PMFJI but... yes, removing the comma was the right thing to do, and will not
mess up your query.