Prev: Mathematical Calculations in a form linked back to a table
Next: Conditional Format problem
From: pemt on 29 Mar 2010 13:49
Hi Jerry,

Sorry for the mistake in last reply.
Actaully I only can index Value. How to index "Rank" without running the 1st
query first or how to index "Rank" with just running the 2nd query?
Thanks,

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
First  |  Prev  | 
Pages: 1 2
Prev: Mathematical Calculations in a form linked back to a table
Next: Conditional Format problem