Prev: Mathematical Calculations in a form linked back to a table
Next: Conditional Format problem
From: pemt on 26 Mar 2010 15:55
Dear all,

Karl Dewey helped me to write a nice code to deal with combining records
into range (below), however, it run really slow when there are over 1000
ranges. Is it possible to run every 20 or 100 ranges and combine all ranges
finally to raise the speed? If yes, how to do that?

Thank you very much for your help.

pemt


Use these two queries --
pemt_1 --
SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1
WHERE Q1.[Day] > Q.[Day]
AND Q1.[Value] <> Q.[Value] )+1 AS Rank
FROM pemt AS Q
ORDER BY Q.Day;

SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank)
AND (pemt_1.Value = pemt_1_1.Value)
GROUP BY pemt_1.Value, pemt_1.Rank
ORDER BY Min(pemt_1.Day);

--
Build a little, test a little.


"pemt" wrote:

> how to combine each record into a range?
> Table1
> Day Value
> 1 0
> 2 0
> 3 0
> 4 1
> 5 1
> 6 1
> 7 0
> 8 0
> 9 0
> 10 0
> 11 1
> 12 1
> 13 0
> 14 1
> 15 0
> 16 0
> 17 1
> 18 1
> 19 0
> 20 1
> . .
> . .
> . .
> 1000 1
>
> how to covert the above table into:
> DayStart DayEnd Value
> 1 3 0
> 4 6 1
> 7 10 0
> 11 12 1
> 13 13 0
> 14 14 1
> 15 16 0
> 17 18 1
> 19 19 0
> ...
>
> thanks,
>
> pemt
From: Jerry Whittle on 26 Mar 2010 17:04
Make sure that the Rank and Value fields are indexed.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"pemt" wrote:

> Dear all,
>
> Karl Dewey helped me to write a nice code to deal with combining records
> into range (below), however, it run really slow when there are over 1000
> ranges. Is it possible to run every 20 or 100 ranges and combine all ranges
> finally to raise the speed? If yes, how to do that?
>
> Thank you very much for your help.
>
> pemt
>
>
> Use these two queries --
> pemt_1 --
> SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1
> WHERE Q1.[Day] > Q.[Day]
> AND Q1.[Value] <> Q.[Value] )+1 AS Rank
> FROM pemt AS Q
> ORDER BY Q.Day;
>
> SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
> FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank)
> AND (pemt_1.Value = pemt_1_1.Value)
> GROUP BY pemt_1.Value, pemt_1.Rank
> ORDER BY Min(pemt_1.Day);
>
> --
> Build a little, test a little.
>
>
> "pemt" wrote:
>
> > how to combine each record into a range?
> > Table1
> > Day Value
> > 1 0
> > 2 0
> > 3 0
> > 4 1
> > 5 1
> > 6 1
> > 7 0
> > 8 0
> > 9 0
> > 10 0
> > 11 1
> > 12 1
> > 13 0
> > 14 1
> > 15 0
> > 16 0
> > 17 1
> > 18 1
> > 19 0
> > 20 1
> > . .
> > . .
> > . .
> > 1000 1
> >
> > how to covert the above table into:
> > DayStart DayEnd Value
> > 1 3 0
> > 4 6 1
> > 7 10 0
> > 11 12 1
> > 13 13 0
> > 14 14 1
> > 15 16 0
> > 17 18 1
> > 19 19 0
> > ...
> >
> > thanks,
> >
> > pemt
From: pemt on 26 Mar 2010 18:18
Jerry,

Thanks. They are indexed. Is it possible to set up certain number of ranges
as one bin and only calculate in each bin, and finally combine results from
all bins? how?

pemt

"Jerry Whittle" wrote:

> Make sure that the Rank and Value fields are indexed.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "pemt" wrote:
>
> > Dear all,
> >
> > Karl Dewey helped me to write a nice code to deal with combining records
> > into range (below), however, it run really slow when there are over 1000
> > ranges. Is it possible to run every 20 or 100 ranges and combine all ranges
> > finally to raise the speed? If yes, how to do that?
> >
> > Thank you very much for your help.
> >
> > pemt
> >
> >
> > Use these two queries --
> > pemt_1 --
> > SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1
> > WHERE Q1.[Day] > Q.[Day]
> > AND Q1.[Value] <> Q.[Value] )+1 AS Rank
> > FROM pemt AS Q
> > ORDER BY Q.Day;
> >
> > SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
> > FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank)
> > AND (pemt_1.Value = pemt_1_1.Value)
> > GROUP BY pemt_1.Value, pemt_1.Rank
> > ORDER BY Min(pemt_1.Day);
> >
> > --
> > Build a little, test a little.
> >
> >
> > "pemt" wrote:
> >
> > > how to combine each record into a range?
> > > Table1
> > > Day Value
> > > 1 0
> > > 2 0
> > > 3 0
> > > 4 1
> > > 5 1
> > > 6 1
> > > 7 0
> > > 8 0
> > > 9 0
> > > 10 0
> > > 11 1
> > > 12 1
> > > 13 0
> > > 14 1
> > > 15 0
> > > 16 0
> > > 17 1
> > > 18 1
> > > 19 0
> > > 20 1
> > > . .
> > > . .
> > > . .
> > > 1000 1
> > >
> > > how to covert the above table into:
> > > DayStart DayEnd Value
> > > 1 3 0
> > > 4 6 1
> > > 7 10 0
> > > 11 12 1
> > > 13 13 0
> > > 14 14 1
> > > 15 16 0
> > > 17 18 1
> > > 19 19 0
> > > ...
> > >
> > > thanks,
> > >
> > > pemt
From: Steve on 26 Mar 2010 18:50
Hello Pemt,

The two queries are very complex and it is not surprising that they run
slow. Karl may not have done you any favor recommending those queries.
Please describe your real data and what you want to do and perhaps a
different solution is possible that will be much faster. If you posted that
somewhere in the past, where and when did you post it?

Steve
santus(a)penn.com


"pemt" <pemt(a)discussions.microsoft.com> wrote in message
news:B9EF9F8E-B832-44AA-8962-34DDF6CB2D29(a)microsoft.com...
> Dear all,
>
> Karl Dewey helped me to write a nice code to deal with combining records
> into range (below), however, it run really slow when there are over 1000
> ranges. Is it possible to run every 20 or 100 ranges and combine all
> ranges
> finally to raise the speed? If yes, how to do that?
>
> Thank you very much for your help.
>
> pemt
>
>
> Use these two queries --
> pemt_1 --
> SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1
> WHERE Q1.[Day] > Q.[Day]
> AND Q1.[Value] <> Q.[Value] )+1 AS Rank
> FROM pemt AS Q
> ORDER BY Q.Day;
>
> SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd,
> pemt_1.Value
> FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank)
> AND (pemt_1.Value = pemt_1_1.Value)
> GROUP BY pemt_1.Value, pemt_1.Rank
> ORDER BY Min(pemt_1.Day);
>
> --
> Build a little, test a little.
>
>
> "pemt" wrote:
>
>> how to combine each record into a range?
>> Table1
>> Day Value
>> 1 0
>> 2 0
>> 3 0
>> 4 1
>> 5 1
>> 6 1
>> 7 0
>> 8 0
>> 9 0
>> 10 0
>> 11 1
>> 12 1
>> 13 0
>> 14 1
>> 15 0
>> 16 0
>> 17 1
>> 18 1
>> 19 0
>> 20 1
>> . .
>> . .
>> . .
>> 1000 1
>>
>> how to covert the above table into:
>> DayStart DayEnd Value
>> 1 3 0
>> 4 6 1
>> 7 10 0
>> 11 12 1
>> 13 13 0
>> 14 14 1
>> 15 16 0
>> 17 18 1
>> 19 19 0
>> ...
>>
>> thanks,
>>
>> pemt


From: John... Visio MVP on 26 Mar 2010 19:12
"Steve" <notmyemail(a)address.com> wrote in message
news:ugaUxaTzKHA.5936(a)TK2MSFTNGP04.phx.gbl...
> Hello Pemt,
>
> The two queries are very complex and it is not surprising that they run
> slow. Karl may not have done you any favor recommending those queries.
> Please describe your real data and what you want to do and perhaps a
> different solution is possible that will be much faster. If you posted
> that somewhere in the past, where and when did you post it?
>
> Steve


You forgot your pimping line.

John...

 |  Next  |  Last
Pages: 1 2
Prev: Mathematical Calculations in a form linked back to a table
Next: Conditional Format problem