How to:Table indexing with recordsets

[NikosXP]

Hi to All...
I have the following Table(Not fixed length)

Table A:

Time Class
-------------
10,12 A
11,2 A
12 B
13 B
14,3 B
15 A
16 C
etc...
And want to produce a more synoptic table with the same info B...

Table B:

LowBound UpperBound Class
---------------------------------
10,12 12 A
12 15 B
15 16 A
16 16 C
etc...

I've tried to find an SQL query , but the prob is that I don't want to group all A's or B's or... together but only if they are continuous(sth like Run Length Encoding)
So I have planned an algorithm to perform the task But I don't know how to write it using recordsets and how can I send the data needed from one table to another...

I'm Sending you the Algorithm So If anyone has any Idea on how it can be transported in VB for Access or any suggestions at all I would be gratefull.

i=1
B[1].LowBound=A[1].Time
j=1
while i<TotalofRecords do
if A.class<>A[i+1].class then
B[j].UpperBound=A[i+1].Time
B[j+1].LowBound=A[i+1].Time
B[j].class=A.class
i=i+1
j=j+1
else
i=i+1
end if
Loop
B[j].UpperBound=A.Time 'To treat last value
B[j].class=A.class 'properly

Thanks In Advance
Nick
P.S.>I Think that any tip on how to index a table and how to move data between the two tables would be of great help..

 

[JimHorton]

Not to sound ignorant, but I'm not sure I understand your data:
What is 10,12 in field Time? Is 10, 12 a time value meaning 10:00 AM and 12:00 PM? Is this field a comma-delimited text field?

Also, I'm not easily seeing how you arrived at your output for table B. ie, Class B in table A I see the lowest value as 12 (Lowbound?), but Ubound is 15--but there is no value 15 for class B in table A. Maybe it's just early in the morning but I'm having a problems seeing any logical connection between what you show for table A and your results in Table B. If you meant us to assume there is somewhere a value 15 for class B somewhere in table A, it would be clearer if that value were shown in the example. Is this the case?

--Jim

 

[NikosXP]

Yes I was a bit umbiguous out there.
I mean 10.12(instead of 10,12) and actually refering at a time point.
The Second Table Contains the (continuous) Time duration for each Class, while the classes can be repeated...
The end Value for each time period is the first of the next one (in maths notation [LowBound,UpperBound))...

I have implemented a solution with recordsets but its about 80 lines of code which I think is alot for such kind of prob...

So I'm waiting for suggestions....

 

[JimHorton]

Nikos,
A query would be impractical if not impossible. I'm assuming the Time field in the first table is the key. The recordset solution would be what I'd go with. 80 lines might be alot, but I'm not sure what you've got there. I'm not sure if I posted anything it would do anything other than what you've got, but it may just server to confuse or leave out a business rule I'm not seeing.

Basically if you just order the recordset by the Time field, step through it, saving a variable that tells you which Class you're currently on, then when that changes write the ubound of the new record to the last new result table you wrote to, you could and have an Addnew before the loop, leave the result table open for .Edit and have a 'Header' block in the loop that is run when the record from table1's Class value changes--in this loop you'd .Update the record, then .Addnew for the next one. At the end of the loop, do the .update for the last record, .
--Jim