Appending records with sub-records into single row

[Allie]

Hello all,

I am working with a database which has a main table, and then a few
sub-tables with a one-to-many relationship. As far as I've been able to
figure out, SQL isn't suited to grab *all* the sub-records associated
with a single record in the main table all in one query.

To illustrate my point, let's say in the main table I have a record with
mainID = 1. In my subtable, I have two records each pointing to the
main record with mainID = 1. A SQL join will return two records. What
I want is a single record, with both sub-records appended one after the
other. So I would end up with 'main table records' + 'sub table record
1' + 'sub table record 2'.

I assume that I will have to use VB script to do this, saving the
queries in an array or something like that, and then appending and
outputting from the code itself, not SQL per se. Are there any other
suggestions, or elegant solutions to this problem?

For those who are curious, I am doing this because I work with a
sociologist who needs the data in a flat format (there will be somewhere
around 3000 columns in the end - yuck).

Thanks,
Allie

 

[John Spencer]

To get that structure you are probably going to be forced to export the
records into a delimited text file using VBA.

Any query you do in Access is going to be limited to 255 fields and
either 4000 or 2000 bytes (depending on compression settings).

I've done something similar for 500 fields where I had two coordinated
queries open at the same time and using the primary key kept them in
synch while building a string to export the 500 plus fields into a CSV
(comma separated values) text file.

I don't have that code handy, but you appear to be competent to do this.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Bob Barrows [MVP]]

Hello all,

I am working with a database which has a main table, and then a few
sub-tables with a one-to-many relationship. As far as I've been able
to figure out, SQL isn't suited to grab *all* the sub-records
associated with a single record in the main table all in one query.

Very true. A crosstab sort of does this, but from reading what follows, I
don't think that's the solution you have in mind.

To illustrate my point, let's say in the main table I have a record
with mainID = 1. In my subtable, I have two records each pointing to
the main record with mainID = 1. A SQL join will return two records.
What I want is a single record, with both sub-records appended one
after the other. So I would end up with 'main table records' + 'sub
table record 1' + 'sub table record 2'.

I assume that I will have to use VB script to do this, saving the
queries in an array or something like that, and then appending and
outputting from the code itself, not SQL per se. Are there any other
suggestions, or elegant solutions to this problem?

For those who are curious, I am doing this because I work with a
sociologist who needs the data in a flat format (there will be
somewhere around 3000 columns in the end - yuck).

That certainly will not be possible without using a vba solution. Access has
a limit on the number of columns to be returned from a query.

What is he planning to use to read such a flat file? Excel won't even handle
more than 256 columns ...

 

[Marshall Barton]

I am working with a database which has a main table, and then a few
sub-tables with a one-to-many relationship. As far as I've been able to
figure out, SQL isn't suited to grab *all* the sub-records associated
with a single record in the main table all in one query.

To illustrate my point, let's say in the main table I have a record with
mainID = 1. In my subtable, I have two records each pointing to the
main record with mainID = 1. A SQL join will return two records. What
I want is a single record, with both sub-records appended one after the
other. So I would end up with 'main table records' + 'sub table record
1' + 'sub table record 2'.

I assume that I will have to use VB script to do this, saving the
queries in an array or something like that, and then appending and
outputting from the code itself, not SQL per se. Are there any other
suggestions, or elegant solutions to this problem?

For those who are curious, I am doing this because I work with a
sociologist who needs the data in a flat format (there will be somewhere
around 3000 columns in the end - yuck).

I think you are attacking the problem the wrong way around.
Besides, it sounds like you are likely to run into at least
one limit somewhere along the road.

Instead, this sounds like a presentation formatting problem.
With this mindset, you should be able to create a report
that collects the information into a coherent presentation
either by just grouping on the main table data and putting
the child table fields in the detail section.

OTOH, if you really need several child table records on a
line, then you can use a multicolumn subreport and have the
flexibility of using more than one line when needed.

 

[Bob Barrows [MVP]]

I think you are attacking the problem the wrong way around.
Besides, it sounds like you are likely to run into at least
one limit somewhere along the road.

Instead, this sounds like a presentation formatting problem.
With this mindset, you should be able to create a report
that collects the information into a coherent presentation
either by just grouping on the main table data and putting
the child table fields in the detail section.

OTOH, if you really need several child table records on a
line, then you can use a multicolumn subreport and have the
flexibility of using more than one line when needed.

Hey Marshall, great Access solution, but i guess you failed to read his last
paragraph ...

 

[Marshall Barton]

Hey Marshall, great Access solution, but i guess you failed to read his last
paragraph ...

;-)

Actually, I did read it, but since it would be ridiculous
for the user to be looking at that kind of data in a query's
datasheet view, some other means of presenting the data is
surely implied ;-)

The only justifiable reason for flattening a data set in
such a strange way is for export. But since the OP did not
explain how the data would be used, I didn't want to go off
on a probably irrelevant tangent.

 

[Dominic Vella]

May I suggest that you probably want to have a VBA routine which will
collect the relevant recordsets, put them in order and then start to process
them by placing the data into a 'Comma Delimeted Text' (CSV) file. It's
simple, but time consuming to create. Then you can have as many columns at
your hearts content.

I hope that helps

Dom

 

[Allie]

Thanks to everyone for your replies. Yes - it is a bit ridiculous to
have 3000 columns in one file, but try telling that to my sociologist.
;-) SPSS, R, and other statistical and programming languages will deal
with that kind of data layout, as ugly as it may look to those of us who
subscribe to the logic of relational databases.

I've pretty much solved the problem by dumping ADO queries into arrays,
and then concatenating the arrays into a csv file. Ugly, and there are
still bugs that I have to work out, but i think it should be fine in the
end. If anyone is interested in my code, gimmie a shout and i'd be
happy to send it along.

Thanks again for your help.

Best,
allie