Prev: Filtering Records
Next: Is it worth it for me to learn Access?
From: bscott via AccessMonster.com on 26 May 2010 17:36
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/Forums.aspx/access-gettingstarted/201005/1

From: KenSheridan via AccessMonster.com on 26 May 2010 18:36
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/Forums.aspx/access-gettingstarted/201005/1

From: bscott via AccessMonster.com on 27 May 2010 13:21
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/Forums.aspx/access-gettingstarted/201005/1

From: bscott via AccessMonster.com on 27 May 2010 15:22
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/Forums.aspx/access-gettingstarted/201005/1

From: KenSheridan via AccessMonster.com on 27 May 2010 17:20
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/Forums.aspx/access-gettingstarted/201005/1

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Filtering Records
Next: Is it worth it for me to learn Access?