Concatenate Single Field (1 Viewer)

[AmySmith]

Not sure if this is best to do in a query or can be done in my report...my table have the following FIELDS and records

TERRITORY STATE
North PA
North NJ
North NY
South NC
South SC

I'd like the report to list in the detail as follows:

North PA, NJ, NY
South NC, SC

I know little SQL/code...just enough to be dangerous. PLEASE HELP!!

 

[Robert Saye]

Hi Amy,

I'm not sure if this is what you want, but if you want your report to have the names like this:
North NJ
North PA
South NC
etc...
Then build it in a query. Where it asks for field name type: WhateverYouWantToNameYourField: [Territory] &" "& [State] (You need the quotes to give you a space between them or to add text like a comma ", ")
Make sure the table name is the table the two fields are in and that should do it.

HTH
Robert

 

[AmySmith]

Robert -- THANKS for the try. I'm actually trying to show the many records of the field State in the same text box besdie the territory name. A territory can have many states. Rather than grouping on Territory, I'd like the report detail (or query results) to show as follows:

Territory: North States: NJ, PA, NY, MA
Territory: South States: NC, SC, GA, FL

Any fixes for this that a novice user can do?

 

[jfi]

see the posting in Queries