Help Needed:Query which can't be written in SQL/tricky problem

[NikosXP]

Hi, to all members
This is my first appeal to the forum...but I think its a tricky problem...
I have a table of the following form

Time DataClass
1.2 A
1.3 A
1.4 B
1.5 C
1.6 D
1.7 D
1.8 A
1.9 A
2.0 A
etc
I want to produce the following Table

Min Max DataClass
1.2 1.4 A
1.4 1.5 B
1.5 1.6 C
1.6 1.8 D
1.8 2.0 A
etc...
Actually I want to group together only continuous data in the DataClass Domain and not All A's together , which would be easy with a groub by statement.
It seems to me like a run length encoding(rle)on the first table , but I have no previous experience of handling recordsets through the Access vba interface so...
Also another prob I'm facing is that the initial table could have more or less than the four classes (A,B,C,D) so I don't want to write in if's containing A,B,C,D....
Any Ideas Are welcomed, because I'm a bit of stuck here(well ok Actually lots of stuck!!!)
Thanks In Advance
Nick

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

'Tricky' is not the term I would use (at least not for the DATA / results posted here).

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[NikosXP]

Ok I just not have any previous experience in recordsets , and thats the reason I need a push...
Nick

 

[MichaelRed]

Consider what you are asking for. It is inconsistient,

Min Max DataClass
1.2 1.4 A[tab][tab]BUT there is NO A ~ 1.4
1.4 1.5 B
1.5 1.6 C
1.6 1.8 D
1.8 2.0 A[tab][tab]Here, there would be (if the pattern follows) a (Max) of 2.1)

Also, what's a body to do if there are gaps in the sequence?

How should 'other' values be treated (e.g. c = 1.57; or d = 1.72; or f = 1.58)?

What you want is NOT going to fall out of some simplistic aggregate query. My guess is that when (or IF) you start to really implement this transform, you will be using more code than data manipulation (SQL) and end with more exception processing and rule bending than actual data organization. While this is common in Ms. A. and VB apps, your process appers to be a really extreme instance. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[cmmrfrds]

It may be easier to make 2 passes on the data in your table. The first pass you determine the time brackets for the min and max, then store the results in a temporary table, which would have three fields the dataclass bracket and time. Next you do a select on the temporary table and sort and group as needed. All this can be done in a stored procedure and the final select would return your resultset for use in your application.