Query to organize nested values

[Seth Mannheim]

Dear Forum people,

This may be easy for an old hand, but I'm stumped.

I have a table with the following structure:
F1 F2
1 2
1 4
1 7
2 4
2 7
3 7
4 7
5 6
5 7
6 7
7

(The table also has and ID field with a unique value for each combination, but for clarity I did not include it here).

I want it to be restructured appear as follows:
F1 F2 F3 F4


1 2 4 7
2 4 7
3 7
4 7
5 6 7
6 7
7

I know I can write VB procedures that will inspect the fields over multiple loops and record each value in fields in the record, but
is their a way to structure a query or series of nested queries to do this?

Thanks.

[June7]

Review: http://datapigtechnologies.com/flashfiles/crosstab.html

However, that won't get 7 in first column when it is the only value associated with F1 ID.

How many possible different values in F2?

[Seth Mannheim]

Oh, no.....about 1200 unique values on a small value (possibly up to a million).....but only about 4-5 nesting levels.

So there may be values 1-1200, and let's say 700 values within the 1-1200 set will be paired and nested within the other values.

5 paired with (nested in) 7, 200, 587
7 nested in 200
200 nested in 587

with 587 being the "terminal nest."

I had myself built a pivot table, but realized this wouldn't work for hundreds of nested values.

[Seth Mannheim]

Getting the 7 included is not the big problem (I think I can use a union query for that eventually) it is getting all those nested values in a finite (1-8) number of columns.

There final table will then have 200-1200 rows (depending on how many nesting levels I leave in) and 3-8 columns.

I can write a procedure to read the values into a variable and then update columns 2, 4, 5...etc. but wondered if there was any way to do this more efficiently with query language.

[June7]

I think the only recourse is VBA. Options:

1. write data to temp table

2. concatenate values to a single string, review: http://allenbrowne.com/func-concat.html

[Seth Mannheim]

The ConcatRelated() looks very interesting, and then parse the concatenated value into individual fields in another table, it would seem, yes?

[June7]

Never tried that. A query that calls the ConcatRelated function has been known to run very slow.

Sounds like you need the temp table route. Perhaps with the ConcatRelated code called from the UpdateTo row in an INSERT action. Or code that uses the concatenated string to write record, something like:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT DISTINCT F1 FROM sourcetablename;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
While not rs.EOF
CurrentDb.Execute "INSERT INTO destinationtablename(F1, F2) VALUES(" & rs!F1 & ", '" & ConcatRelated("F2", "sourcetablename", "F1=" & rs!F1) & "'")
rs.MoveNext
Wend

[Seth Mannheim]

The INSERT looks very promising (I'm going to need to try this after a rest, maybe tonight, maybe tomorrow morning).

I am working in DAO....hope I can handle the translation from ADO.....

In any case many thanks....

[June7]

Procedure could write the individual values directly to individual fields. This is something I do. The variability in the number of fields can be dealt with by setting up table with enough fields to handle max possible fields or have code to modify table and add fields as needed (this will be a consideration even if go with parsing string). Would not involve intermmediate step of outputting concatenated string. Always more than one way ...

[Seth Mannheim]

This looks excellent, but again, my poor newbie brain needs a rest first.

Thanks again!

[Seth Mannheim]

After reflection, closer examination of the structure of the Concatrelated function and a night's sleep half spent chasing nested loops, I came to the conclusion that ConcatRelated would be an unproductive path path to follow. My code to generate the original table already takes a lot of time processing nested loops, and while there will be less loops because the dataset smaller with each nesting level, there is a lot of processing and programming time wasted concatating and unconcatating, because I definitely want the values in separate fields, not concatated (actually the "values" are not real values, but themselves IDs records in other tables). I will take the time to really understand the function, though, because it may help me in reaching my actual goal.

I now think an insert query into a new table with the ID field is the way to go:
Original Table
ID | F1 F2 F3 F4 F5 F6 F7
ID1 | 1 2
ID2 | 1 4
ID3 | 1 7


New Table (version 1 - nested values)
ID | N1 N2 N3
1 | 2 4 7

or perhaps it would provide more flexibility to store the combination ids rather than the actual values

New Table (version 2 - nested combination IDs)
New ID
NID1 | ID1 ID2 ID3

One or more nested levels of update queries, or nested VB loops with one or more update queries seems best. Like this:

INSERT INTO NewTable(ID, N1, N2, N3...Nn) VALUES(nestedquery1, nestedquery2, nestedquery3,...nested queryN)

Of course the portion in red will require me to think a bit more.

I feel that it will be easier to experiment with queries using nested Access named queries. If I can build one level then the others will follow.

I definitely know I can do this building loops in VB with DAO updates of recordsets defined by named Access queries. I'll spend some time trying to upgrade my SQL abilities to have transparent SQL in a more portable function, but this could take me a quite a bit of time. It seems the SQL INSERT INTO would be quicker than the following approach I am current using:
.edit
rst!F1 = Variable
.update

I'll spend some time working on the SQL, but will revert to the more clumsy VB/DAO/Access query approach if I get lost....I am only building a functional prototype now and can re-engineer it for elegance and speed later as necessary.

Hope this sheds some light if I've not been too obscure.

If anyone is has more ideas, they'd be most welcome.

[Seth Mannheim]

After further study, it seems the limitation is the WHERE parameter in the RelateConcated, which is single value, or would require a very difficult to understand "complex function in complex function" nesting. In any case it would definitely seem best to avoid any concatation and work on field updating directly.

Seems like series of nested loops (similar to what is used in the concatateRelated function itself) or perhaps better sequenced updates for each nesting level seem more appropriate.