Query - Concatenating Fields

[crook]

I need to concatenate fields in a query.

Here's a sample of the table data I have now:

Key Course_ID Instructor
1 SOC 202 John Doe
2 SOC 202 Dr. Frankenstein

What I need is an output like this:
Course_ID Instructor
SOC 202 John Doe, Dr. Frankenstein

Can this be done without using VB to write the query? I'd like to do something simple using the Access Query Design View if possible.

Thanks,
crook

 

[jeremie_ingram]

Technically you would be useing VBA to concatinate the fields, but you can use the builder to do it. Basing off of you example data, in your query design view, add the tbl that contains the data you need. In the FIELD (top line) of the query columns, type in the name you want to call this concatinated field (CoID_Inst). Place a : after the name, then RIGHT CLICK on the cell and select BUILD from the list. You can use the builder to create the expression you need to do what you want.

From your example the expression would be....

CoID_Inst: [tbl1]![Course_ID] & " " & [tbl1]![Instructor]

when the query is run, the results will appear in 1 coulumn with a spcae between them.

Replace tbl1 in my expression with the appropriate name of your table.

 

[crook]

Thanks - your answer is right on, but I can see I was not very clear with my question...

What I'm trying to do is take 2 rows that have identical data in each field except for 1 - for example:

row 1: course field = SOC 202, instructor field = John Doe
row 2: course field = SOC 202, instructor field = Dr. Frankenstein

and turn it into one row - for example:

row 1: course field = SOC 202, instructor field = John Doe, Dr. Frankenstein.

If the instructor field contained numbers, for example, then I would just make an aggregate query and sum them. But with text I'm not sure how to do it.

Anyway, hope this makes better sense.
Thanks,
- crook

 

[AncientOne]

What do you want to do with the row when it's processed? Store it or use it in a report or form? Is it always the instructor field that contains the varying data?

 

[crook]

My department has a new website, and wants to give a listing of all of the classes we are offering. It's will be based on a static table that will be updated periodically through append & delete queries. What I'm trying to do now is write an append query that will copy one record per course into this static table.

If this were an Access report, I would just use the sorting/grouping feature to customize how the output is displayed. But the web work is being done using ASPx by an external group who have specified that they can only work with one record per course.

Anyway, I'm not sure this is something that can be done in the query builder window. Just thought I'd check before I spend too much time trying to create my own function. I was hoping someone would know off-hand if there is some built-in function available through the query builder that could do this.

Re-reading your question just now...
Yes - it's always the instructor field that contains this varying data.


Thanks,
- crook

 

[WayneRyan]

crook,

From one of Pat Hartman's posts:

http://support.microsoft.com/default.aspx?scid=kb;en-us;141624

Wayne

 

[catcoota]

hullo there !
um having the same problem, & I saw the last reply which works very well, but unforunately i need it to be contantenated into a table or a query not a report!
is there anyway that i can convert the report into a table or a query?

thanks

 

[catcoota]

Hullo

Thanks a lot for ur reply
I have applied the lesson explained in the link you have given to me, but, I don't know why I get the column that is supposed to contain contanenated fields, i get it empty!! although i applied the lesson to the same file which is northwind!

did you follow the lesson and apply it on NWIND file and did it work with?

Hope u can help me !