access+courses+in+london

access+courses+in+london - Concatenation

Resolved · Low Priority · Version Standard

Concatenation

Bob has attended:
Access Intermediate course

by - delegate Bob [2 posts] (2007 Sep 6 Thu, 17:39) Reply

Got a DB with 3 columns

Col 1 = ID (unique value)
Col 2 = Agent Name (Unique value)
Col 3 = Postcode (multiple values)

ie: 1 Postcode is covered by many agents...

How can I concatenate Col 1 & 2 into single Postcodes

ie:

001, 002, 003 Fred Smith, Jim Hall, Pat Jones TW8 8JA

Many thanks

Version	Date	Location	Places available	Book	Next place rate:
Access VBA 2 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2008 Dec 4 Thu + 5 Fri	Southwark	3	book now	£475	£495
2003	2009 Jan 8 Thu + 9 Fri	Southwark	6	book now	£450	£485
2003	2009 Feb 5 Thu + 6 Fri	Southwark	8	book now	£275	£399
2003	2009 Mar 5 Thu + 6 Fri	Southwark	7	book now	£345	£399
2003	2009 Apr 8 Wed + 9 Thu	Southwark	8	book now	£275	£399
2003	2009 May 7 Thu + 8 Fri	Southwark	8	book now	£275	£399
Full Schedule: See all 12 Access VBA course dates. Bookings currently available until 5th November 2009.

RE: Concatenation

by - trainer Carlos gold contributer [479 posts] (2007 Oct 1 Mon, 11:44) Reply

Hi Bob

Sorry for the delay.

Do you need to have the IDs, Agent names and postcode appear as a new value?

If not you could do a query that just concactenates the ID and Agent name for each record and then create a report that shows all agents grouped by Postcode

To Concactenate in a Query:

In a new field in the Query window

Open the Build window - Right click Select build

Enter the following code:

FullAgent: [ID field] + "," + [Agent name field]

Hope this helps.

Regards

Carlos