Help needed creating a more compact report

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200707/1

Anders_S via AccessMonster.com wrote:

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/Otherdownload.asp?SampleName='Generic%20Function%2 0To%20Concatenate%20Child%20Records'
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

--
Marsh
MVP [MS Access]

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

Marshall Barton wrote:
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/Otherdownload.asp?SampleName='Generic%20Function%2 0To%20Concatenate%20Child%20Records'
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200707/1