Concatenation 1

[eao]

http://tek-tips.com/faqs.cfm?fid=4233

I'm attempting to use the function listed in the FAQ above to concatenate fields across two tables into a single field in an append query. I am not too sure of the syntax I need to use.

My tables look like this:

tblManualInstall:
Sequence
OldSerialNum
Description
Group
Subject

tblBatches:
BatchNum
BatchID
EmplID
OldSerialNum
NewSerialNum
Coordinator

I want to run an append query to update a table in another database, and I want the result to look like this:

Coordinator: <data>; OldSerialNum: <data>; NewSerialNum <data>; Description <data>.

Any ideas? Am I using the correct function?

 

[AlexCuse]

I think all you will need to do is something like

select 'Coordinator: ' & tblBatches.Coordinator
, 'OldSerialNum: ' & tblBatches.OldSerialNum
, 'NewSerialNum: ' & tblBatches.OldSerialNum
, 'Description : ' & tblManualInstall.Description
from tblBatches inner join tblManualInstall
on tblBatches.OldSerialNum = tblManualInstall.OldSerialNum

From what you've shown I can't see what you'd need a concatenation for. Unless I am misreading your desired result.

Hope it helps,

Alex


[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[lespaul]

that concatenation function does something like this:
[tt]
[Family]
FamilyID FamilyName Address
56 Blow 123 Main St

[FamilyMembers]
MemberID FamilyID MemberType MemberName
67 56 1 Joe
68 56 2 Jane
69 56 3 Bobby
70 56 4 Susie
[/tt]
So lets say this is your table structure and you want to return all the family member names in a single comma separated list. You would use that function and return:
[tt]
FamilyID Members
56 Joe, Jane, Bobby, Susie
[/tt]

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[eao]

Thanks folks.

Alex, you're answer got me started in the right direction. I was trying to over complicate things.