Help needed creating a more compact report

[Anders_S via AccessMonster.com]

In Microsoft Access 2000, I have a multi-column report that currently prints
like this:

Abel St
1705
1719
Agate St
200
319 Apt 15
319 Apt 208
405
...

I want to create a report that prints in a much more compact format, like
this:
Abel St: 1705, 1719
Agate St: 200, 319 (#15, #208), 405
...

What do I need to do? And if this question has been answered before, what
would I search on to find the answer? I looked around but couldn't find it.

Thanks,
Anders Schneiderman

 

[Marshall Barton]

In Microsoft Access 2000, I have a multi-column report that currently prints
like this:

Abel St
1705
1719
Agate St
200
319 Apt 15
319 Apt 208
405
..

I want to create a report that prints in a much more compact format, like
this:
Abel St: 1705, 1719
Agate St: 200, 319 (#15, #208), 405

You need to use a finction like:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'
to construct the number list.

Assuming all the data is in one table, the report's record
source query could look like:

SELECT street, Concatenate("Select number From table Where
street = """ & Street &"""")
FROM table
GROUP BY street

 

[Anders_S via AccessMonster.com]

Thanks, Marshall! Now all I need is a little help getting with the last step.


In addition to combining house numbers
Agate St: 200, 319, 405

I also want to combine apartment numbers:
Agate St: 200, 319 (#15, #208), 405

I can't figure out how to do that using the function. Given that I'm nesting
twice -- GROUPing both by street number and apartment unmber -- do I need to
switch to using VBA to write the report? If so, where can I find an example
of something similar? If I don't need to, how do I pull this off?

Thanks,
Anders

In Microsoft Access 2000, I have a multi-column report that currently prints
like this:

[quoted text clipped - 13 lines]

Abel St: 1705, 1719
Agate St: 200, 319 (#15, #208), 405

You need to use a finction like:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'
to construct the number list.

Assuming all the data is in one table, the report's record
source query could look like:

SELECT street, Concatenate("Select number From table Where
street = """ & Street &"""")
FROM table
GROUP BY street