Probably an easy answer on formatting a (sub)report:

[sfreeland01]

Hi there,

I've recently offered to design a database for a small company. Not
having touched SQL databases for years and years since college, it's
been quite a steep learning curve. One problem has eluded my attempts
to track down a solution on the Internet...

Some background on the database:

I have a Table "ContractIDAndCountry" with a Primary key autonumbered
by Access, and with ContractID and CountryName as fields. These are
populated elsewhere in a data entry Form.

The table for example:

ID ContractID CountryName

1 12 Denmark
2 12 Nepal
3 20 Canada
etc etc

I need to create a CONTRACT AGREEMENT and within it list all the
Countries that a specific contract has assigned to it. I have had
success using both a Query & a Sub-Report to do this, however I need
help formatting the resulting data...

What I'd like to do is instead of having all the countries returned in
a single column, have them left-justified with commas inbetween...

So:

England
Japan
Canada
United States

becomes:

England, Japan, Canada, United States

Ideally the country listing will be contained in a box within the
CONTRACT AGREEMENT.

Again, as stated above I'm a couple newbie in terms of using Access, so
the simpler the better - that being said, I've learned how to follow
instructions very well whilst working on this...

Thanks in advance for any help you can provide.

ps: The closest I have come to solving this was selecting the "page
setup" in the report, and selecting more than one column - this however
isn't ideal as I'd like the commas and the formatting tends to get a
little 'wonky'.

 

[Rick Brandt]

Hi there,

I've recently offered to design a database for a small company. Not
having touched SQL databases for years and years since college, it's
been quite a steep learning curve. One problem has eluded my attempts
to track down a solution on the Internet...

Some background on the database:

I have a Table "ContractIDAndCountry" with a Primary key autonumbered
by Access, and with ContractID and CountryName as fields. These are
populated elsewhere in a data entry Form.

The table for example:

ID ContractID CountryName

1 12 Denmark
2 12 Nepal
3 20 Canada
etc etc

I need to create a CONTRACT AGREEMENT and within it list all the
Countries that a specific contract has assigned to it. I have had
success using both a Query & a Sub-Report to do this, however I need
help formatting the resulting data...

What I'd like to do is instead of having all the countries returned in
a single column, have them left-justified with commas inbetween...

So:

England
Japan
Canada
United States

becomes:

England, Japan, Canada, United States

Ideally the country listing will be contained in a box within the
CONTRACT AGREEMENT.

Again, as stated above I'm a couple newbie in terms of using Access,
so the simpler the better - that being said, I've learned how to
follow instructions very well whilst working on this...

Thanks in advance for any help you can provide.

ps: The closest I have come to solving this was selecting the "page
setup" in the report, and selecting more than one column - this
however isn't ideal as I'd like the commas and the formatting tends
to get a little 'wonky'.

You would need a custom function that returns all countries for a given
contract in a RecordSet which would then be looped through in the function's
code to append each record into a string variable (separated with commas).
The function would then return the final string value.

With basic queries and reports the multi-column sub-report is as close as
you are going to get.

 

[Rick Brandt]

You would need a custom function that returns all countries for a
given contract in a RecordSet which would then be looped through in
the function's code to append each record into a string variable
(separated with commas). The function would then return the final
string value.
With basic queries and reports the multi-column sub-report is as
close as you are going to get.

Here is example of above (courtesy Duane Hookom).

http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

 

[sfreeland01]

Thanks alot - appreciate the prompt reply. I'll give this a shot
tonight & hopefully get it sorted out... You're a life/time saver

 

[sfreeland01]

Excellent! Thanks for the help Rick... Worked perfectly.