Extracting from Long Binary field

[deko]

I'm wondering if anyone has come across something like this before and could
offer some suggestions.

I need to extract all the Single values packed in a Long Binary field (4k
chunks) and then export to Excel.

The challenge is arranging the values in columns and rows so they can be
exported and land in Excel properly for charting. The way the data is
packed in the LB field can be illustrated like this:

1 3 5 2 4 6 7 9 11 8 10 12

I have to get this series of values into 2 tables looking like this:

1 | 3 | 5
7 | 9 | 11

2 | 4 | 6
8 | 10 | 12

In reality it's more complicated, but the idea is separating the series
based on every 3rd (or Nth) value in the series, and stacking them up in
columns (N is variable and has to be inferred by other parameters). After I
get the values into a table the way I want them, I can easily dump it out to
Excel.

What I'm experimenting with now looks something like this:

Const CHUNKSZ = 4
Do While Not rstTd.EOF
lngOffset = 0
Set colTd = New Collection
lngBsize = LenB(rstTd!MeasData)
ReDim abytB(lngBsize)
abytB = rstTd!MeasData.GetChunk(0, lngBsize)
lngMid = rstTd!MeasurementId
Do While lngOffset < lngBsize
CopyMemory sngBval, abytB(lngOffset), 4
colTd.Add sngBval
lngOffset = lngOffset + CHUNKSZ
Loop
'insert values into table here
Set colTd = Nothing
rstTd.MoveNext
Loop

Once the values are extraced into colTd, I pass the collection to another
function that massages the data into a table based on whatever the layout
requirements are.

But how to split the series of values on every Nth item in the collection?
Is this something that could be done with SQL based on an Autonumber ID in
the table? Other suggestions?

Thanks in advance.

[jimfortune]

The letters are an abstraction.

tblPackedBinary:
AutoID Data
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O
16 P
17 Q
....

qrySieve:
SELECT tblPackedBinary.AutoID, 4 AS n, IIf((AutoID + [n] - 1) Mod
2*[n]>= [n],"OddGroup" & CStr((AutoID - 1) \ (2 * [n]) + 1),"EvenGroup"
& CStr((AutoID - 1) \ (2 * [n])) + 1) AS Category, tblPackedBinary.Data
FROM tblPackedBinary;

! qrySieve:
AutoID n Category Data
1 4 OddGroup1 A
2 4 OddGroup1 B
3 4 OddGroup1 C
4 4 OddGroup1 D
5 4 EvenGroup1 E
6 4 EvenGroup1 F
7 4 EvenGroup1 G
8 4 EvenGroup1 H
9 4 OddGroup2 I
10 4 OddGroup2 J
11 4 OddGroup2 K
12 4 OddGroup2 L
13 4 EvenGroup2 M
14 4 EvenGroup2 N
15 4 EvenGroup2 O
16 4 EvenGroup2 P
17 4 OddGroup2 Q
....

If '3 As n' is used instead of '4 As n':
! qrySieve:
AutoID n Category Data
1 3 OddGroup1 A
2 3 OddGroup1 B
3 3 OddGroup1 C
4 3 EvenGroup1 D
5 3 EvenGroup1 E
6 3 EvenGroup1 F
7 3 OddGroup2 G
8 3 OddGroup2 H
9 3 OddGroup2 I
10 3 EvenGroup2 J
11 3 EvenGroup2 K
12 3 EvenGroup2 L
13 3 OddGroup3 M
14 3 OddGroup3 N
15 3 OddGroup3 O
16 3 EvenGroup3 P
17 3 EvenGroup3 Q
....

Maybe qrySieve can help reduce the problem you have into a simpler one.

James A. Fortune

[deko]

Thanks for the reply. I ended up doing something similar.

tblPackedBinary:
AutoID Data
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O
16 P
17 Q
...

qrySieve:
SELECT tblPackedBinary.AutoID, 4 AS n, IIf((AutoID + [n] - 1) Mod
2*[n]>= [n],"OddGroup" & CStr((AutoID - 1) \ (2 * [n]) + 1),"EvenGroup"
& CStr((AutoID - 1) \ (2 * [n])) + 1) AS Category, tblPackedBinary.Data
FROM tblPackedBinary;

! qrySieve:
AutoID n Category Data
1 4 OddGroup1 A
2 4 OddGroup1 B
3 4 OddGroup1 C
4 4 OddGroup1 D
5 4 EvenGroup1 E
6 4 EvenGroup1 F
7 4 EvenGroup1 G
8 4 EvenGroup1 H
9 4 OddGroup2 I
10 4 OddGroup2 J
11 4 OddGroup2 K
12 4 OddGroup2 L
13 4 EvenGroup2 M
14 4 EvenGroup2 N
15 4 EvenGroup2 O
16 4 EvenGroup2 P
17 4 OddGroup2 Q
...

If '3 As n' is used instead of '4 As n':
! qrySieve:
AutoID n Category Data
1 3 OddGroup1 A
2 3 OddGroup1 B
3 3 OddGroup1 C
4 3 EvenGroup1 D
5 3 EvenGroup1 E
6 3 EvenGroup1 F
7 3 OddGroup2 G
8 3 OddGroup2 H
9 3 OddGroup2 I
10 3 EvenGroup2 J
11 3 EvenGroup2 K
12 3 EvenGroup2 L
13 3 OddGroup3 M
14 3 OddGroup3 N
15 3 OddGroup3 O
16 3 EvenGroup3 P
17 3 EvenGroup3 Q
...

Maybe qrySieve can help reduce the problem you have into a simpler one.

James A. Fortune