Concatenate Function

M

Mike Diamond

I have a query where i am trying combine multiple records of a particular
field into a single record.

eg. instead of;

Record 1
AB

Record 2
CD

Record 3
EF

I want to have;

Record 1
AB, CD, EF

The tables i am using within the query are;

tblStaffMembers;
StaffID
StaffTitle
StaffFirstName
StaffSurname
Abbreviation

tblTeamInternal
IntTeamID
SubmissionID
StaffID

Depending on the values of [tblTeamInternal].[StaffID], i would like to
combine the associated Abbreviation values in tblStaffMembers using a query.

Currently i have all associated fields in the query and have created an
additonal field which uses the expression - Team: Concatenate("SELECT
Abbreviation FROM tblStaffMembers WHERE StaffID =" & [tblTeamInt].[StaffID]).

This is not working for me first of all because i am getting an undefined
function dialog popping up and am not sure whether i have the correct
expression.

Can anyone assist at all?

Regards
Mike
 
K

Ken Snell \(MVP\)

Do you have a user-defined function named Concatenate in a regular module in
your database?
 
M

Mike Diamond

Hi Ken, no i dont. How could i create one?

Ken Snell (MVP) said:
Do you have a user-defined function named Concatenate in a regular module in
your database?

--

Ken Snell
<MS ACCESS MVP>


Mike Diamond said:
I have a query where i am trying combine multiple records of a particular
field into a single record.

eg. instead of;

Record 1
AB

Record 2
CD

Record 3
EF

I want to have;

Record 1
AB, CD, EF

The tables i am using within the query are;

tblStaffMembers;
StaffID
StaffTitle
StaffFirstName
StaffSurname
Abbreviation

tblTeamInternal
IntTeamID
SubmissionID
StaffID

Depending on the values of [tblTeamInternal].[StaffID], i would like to
combine the associated Abbreviation values in tblStaffMembers using a
query.

Currently i have all associated fields in the query and have created an
additonal field which uses the expression - Team: Concatenate("SELECT
Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt].[StaffID]).

This is not working for me first of all because i am getting an undefined
function dialog popping up and am not sure whether i have the correct
expression.

Can anyone assist at all?

Regards
Mike
Click to expand...
Click to expand...
 
M

Mike Diamond

Ok i have managed to copy and paste a module from an example database, but my
query is receiving a compilation error. Can you see any problem with the way
i have created the belwo expression? i have double checked the name of all
fields and they are typed correctly.

Team: Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID ="
& [StaffID])

Mike Diamond said:
Hi Ken, no i dont. How could i create one?

Ken Snell (MVP) said:
Do you have a user-defined function named Concatenate in a regular module in
your database?

--

Ken Snell
<MS ACCESS MVP>


Mike Diamond said:
I have a query where i am trying combine multiple records of a particular
field into a single record.

eg. instead of;

Record 1
AB

Record 2
CD

Record 3
EF

I want to have;

Record 1
AB, CD, EF

The tables i am using within the query are;

tblStaffMembers;
StaffID
StaffTitle
StaffFirstName
StaffSurname
Abbreviation

tblTeamInternal
IntTeamID
SubmissionID
StaffID

Depending on the values of [tblTeamInternal].[StaffID], i would like to
combine the associated Abbreviation values in tblStaffMembers using a
query.

Currently i have all associated fields in the query and have created an
additonal field which uses the expression - Team: Concatenate("SELECT
Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt].[StaffID]).

This is not working for me first of all because i am getting an undefined
function dialog popping up and am not sure whether i have the correct
expression.

Can anyone assist at all?

Regards
Mike
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

What function code did you use? Where did you put it? Post that code so we
can see -- a compilation error most likely is a problem with the code, not
the query.

--

Ken Snell
<MS ACCESS MVP>


Mike Diamond said:
Ok i have managed to copy and paste a module from an example database, but
my
query is receiving a compilation error. Can you see any problem with the
way
i have created the belwo expression? i have double checked the name of all
fields and they are typed correctly.

Team: Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
="
& [StaffID])

Mike Diamond said:
Hi Ken, no i dont. How could i create one?

Ken Snell (MVP) said:
Do you have a user-defined function named Concatenate in a regular
module in
your database?

--

Ken Snell
<MS ACCESS MVP>


I have a query where i am trying combine multiple records of a
particular
field into a single record.

eg. instead of;

Record 1
AB

Record 2
CD

Record 3
EF

I want to have;

Record 1
AB, CD, EF

The tables i am using within the query are;

tblStaffMembers;
StaffID
StaffTitle
StaffFirstName
StaffSurname
Abbreviation

tblTeamInternal
IntTeamID
SubmissionID
StaffID

Depending on the values of [tblTeamInternal].[StaffID], i would like
to
combine the associated Abbreviation values in tblStaffMembers using a
query.

Currently i have all associated fields in the query and have created
an
additonal field which uses the expression - Team: Concatenate("SELECT
Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt].[StaffID]).

This is not working for me first of all because i am getting an
undefined
function dialog popping up and am not sure whether i have the correct
expression.

Can anyone assist at all?

Regards
Mike
Click to expand...
Click to expand...
Click to expand...
 
M

Mike Diamond

Thanks for your assistance Ken, this is a bit above my knowledge. The code
was taken from Duane Hookams databse example..

Option Compare Database

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection,
'adOpenKeyset , adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

======================================================




Ken Snell (MVP) said:
What function code did you use? Where did you put it? Post that code so we
can see -- a compilation error most likely is a problem with the code, not
the query.

--

Ken Snell
<MS ACCESS MVP>


Mike Diamond said:
Ok i have managed to copy and paste a module from an example database, but
my
query is receiving a compilation error. Can you see any problem with the
way
i have created the belwo expression? i have double checked the name of all
fields and they are typed correctly.

Team: Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
="
& [StaffID])

Mike Diamond said:
Hi Ken, no i dont. How could i create one?

:

Do you have a user-defined function named Concatenate in a regular
module in
your database?

--

Ken Snell
<MS ACCESS MVP>


I have a query where i am trying combine multiple records of a
particular
field into a single record.

eg. instead of;

Record 1
AB

Record 2
CD

Record 3
EF

I want to have;

Record 1
AB, CD, EF

The tables i am using within the query are;

tblStaffMembers;
StaffID
StaffTitle
StaffFirstName
StaffSurname
Abbreviation

tblTeamInternal
IntTeamID
SubmissionID
StaffID

Depending on the values of [tblTeamInternal].[StaffID], i would like
to
combine the associated Abbreviation values in tblStaffMembers using a
query.

Currently i have all associated fields in the query and have created
an
additonal field which uses the expression - Team: Concatenate("SELECT
Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt].[StaffID]).

This is not working for me first of all because i am getting an
undefined
function dialog popping up and am not sure whether i have the correct
expression.

Can anyone assist at all?

Regards
Mike
Click to expand...
Click to expand...
Click to expand...
 
M

Mike Diamond

Ken, I have managed to get the query showing the information in the way that
i need to except for one thing. The result is shown in the format of the ID
rather than Staff Names. The problem is that i need to refer to another table
(tblStaffMembers) which is not part of the direct relationships of the below.
How coul i ammend the SQL statement below to reference a field (Abbreviation)
in the StaffMembers table?

StaffID: Concatenate("SELECT StaffID FROM tblTeamInt WHERE SubmissionID =" &
[SubmissionID])


========================
 
K

Ken Snell \(MVP\)

Try this:

StaffID: Concatenate("SELECT tblStaffMembers.StaffName FROM tblStaffMembers
WHERE tblStaffMembers.StaffID = (SELECT TOP 1 T.StaffID FROM tblTeamInt AS T
WHERE T.SubmissionID =" & [SubmissionID])

--

Ken Snell
<MS ACCESS MVP>



Mike Diamond said:
Ken, I have managed to get the query showing the information in the way
that
i need to except for one thing. The result is shown in the format of the
ID
rather than Staff Names. The problem is that i need to refer to another
table
(tblStaffMembers) which is not part of the direct relationships of the
below.
How coul i ammend the SQL statement below to reference a field
(Abbreviation)
in the StaffMembers table?

StaffID: Concatenate("SELECT StaffID FROM tblTeamInt WHERE SubmissionID ="
&
[SubmissionID])


========================

Mike Diamond said:
I have a query where i am trying combine multiple records of a particular
field into a single record.

eg. instead of;

Record 1
AB

Record 2
CD

Record 3
EF

I want to have;

Record 1
AB, CD, EF

The tables i am using within the query are;

tblStaffMembers;
StaffID
StaffTitle
StaffFirstName
StaffSurname
Abbreviation

tblTeamInternal
IntTeamID
SubmissionID
StaffID

Depending on the values of [tblTeamInternal].[StaffID], i would like to
combine the associated Abbreviation values in tblStaffMembers using a
query.

Currently i have all associated fields in the query and have created an
additonal field which uses the expression - Team: Concatenate("SELECT
Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt].[StaffID]).

This is not working for me first of all because i am getting an undefined
function dialog popping up and am not sure whether i have the correct
expression.

Can anyone assist at all?

Regards
Mike
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Sorry, left out a closing parenthesis in the post I just made. Here is
corrected info:

Try this:

StaffID: Concatenate("SELECT tblStaffMembers.StaffName FROM tblStaffMembers
WHERE tblStaffMembers.StaffID = (SELECT TOP 1 T.StaffID FROM tblTeamInt AS T
WHERE T.SubmissionID =" & [SubmissionID] & ")")

--

Ken Snell
<MS ACCESS MVP>


Mike Diamond said:
Ken, I have managed to get the query showing the information in the way
that
i need to except for one thing. The result is shown in the format of the
ID
rather than Staff Names. The problem is that i need to refer to another
table
(tblStaffMembers) which is not part of the direct relationships of the
below.
How coul i ammend the SQL statement below to reference a field
(Abbreviation)
in the StaffMembers table?

StaffID: Concatenate("SELECT StaffID FROM tblTeamInt WHERE SubmissionID ="
&
[SubmissionID])


========================

Mike Diamond said:
I have a query where i am trying combine multiple records of a particular
field into a single record.

eg. instead of;

Record 1
AB

Record 2
CD

Record 3
EF

I want to have;

Record 1
AB, CD, EF

The tables i am using within the query are;

tblStaffMembers;
StaffID
StaffTitle
StaffFirstName
StaffSurname
Abbreviation

tblTeamInternal
IntTeamID
SubmissionID
StaffID

Depending on the values of [tblTeamInternal].[StaffID], i would like to
combine the associated Abbreviation values in tblStaffMembers using a
query.

Currently i have all associated fields in the query and have created an
additonal field which uses the expression - Team: Concatenate("SELECT
Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt].[StaffID]).

This is not working for me first of all because i am getting an undefined
function dialog popping up and am not sure whether i have the correct
expression.

Can anyone assist at all?

Regards
Mike
Click to expand...
Click to expand...
 
M

Mike Diamond

Hi Ken

I tried that and it is selecting the correct field, but at the moment is
only showing the first record in the group. ie. AB instead of AB,CD,EF.


Ken Snell (MVP) said:
Sorry, left out a closing parenthesis in the post I just made. Here is
corrected info:

Try this:

StaffID: Concatenate("SELECT tblStaffMembers.StaffName FROM tblStaffMembers
WHERE tblStaffMembers.StaffID = (SELECT TOP 1 T.StaffID FROM tblTeamInt AS T
WHERE T.SubmissionID =" & [SubmissionID] & ")")

--

Ken Snell
<MS ACCESS MVP>


Mike Diamond said:
Ken, I have managed to get the query showing the information in the way
that
i need to except for one thing. The result is shown in the format of the
ID
rather than Staff Names. The problem is that i need to refer to another
table
(tblStaffMembers) which is not part of the direct relationships of the
below.
How coul i ammend the SQL statement below to reference a field
(Abbreviation)
in the StaffMembers table?

StaffID: Concatenate("SELECT StaffID FROM tblTeamInt WHERE SubmissionID ="
&
[SubmissionID])


========================

Mike Diamond said:
I have a query where i am trying combine multiple records of a particular
field into a single record.

eg. instead of;

Record 1
AB

Record 2
CD

Record 3
EF

I want to have;

Record 1
AB, CD, EF

The tables i am using within the query are;

tblStaffMembers;
StaffID
StaffTitle
StaffFirstName
StaffSurname
Abbreviation

tblTeamInternal
IntTeamID
SubmissionID
StaffID

Depending on the values of [tblTeamInternal].[StaffID], i would like to
combine the associated Abbreviation values in tblStaffMembers using a
query.

Currently i have all associated fields in the query and have created an
additonal field which uses the expression - Team: Concatenate("SELECT
Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt].[StaffID]).

This is not working for me first of all because i am getting an undefined
function dialog popping up and am not sure whether i have the correct
expression.

Can anyone assist at all?

Regards
Mike
Click to expand...
Click to expand...
Click to expand...
 
M

Mike Diamond

If it helps at all, i have noted below the statement that is showing the
relevant records versus that which isnt..

StaffID: Concatenate("SELECT StaffID FROM tblTeamInt WHERE SubmissionID =" &
[SubmissionID])

StaffName: Concatenate("SELECT tblStaffMembers.Abbreviation FROM
tblStaffMembers WHERE tblStaffMembers.StaffID = (SELECT TOP 1 T.StaffID FROM
tblTeamInt AS T WHERE T.SubmissionID =" & [SubmissionID] & ")")

===========================

Ken Snell (MVP) said:
Sorry, left out a closing parenthesis in the post I just made. Here is
corrected info:

Try this:

StaffID: Concatenate("SELECT tblStaffMembers.StaffName FROM tblStaffMembers
WHERE tblStaffMembers.StaffID = (SELECT TOP 1 T.StaffID FROM tblTeamInt AS T
WHERE T.SubmissionID =" & [SubmissionID] & ")")

--

Ken Snell
<MS ACCESS MVP>


Mike Diamond said:
Ken, I have managed to get the query showing the information in the way
that
i need to except for one thing. The result is shown in the format of the
ID
rather than Staff Names. The problem is that i need to refer to another
table
(tblStaffMembers) which is not part of the direct relationships of the
below.
How coul i ammend the SQL statement below to reference a field
(Abbreviation)
in the StaffMembers table?

StaffID: Concatenate("SELECT StaffID FROM tblTeamInt WHERE SubmissionID ="
&
[SubmissionID])


========================

Mike Diamond said:
I have a query where i am trying combine multiple records of a particular
field into a single record.

eg. instead of;

Record 1
AB

Record 2
CD

Record 3
EF

I want to have;

Record 1
AB, CD, EF

The tables i am using within the query are;

tblStaffMembers;
StaffID
StaffTitle
StaffFirstName
StaffSurname
Abbreviation

tblTeamInternal
IntTeamID
SubmissionID
StaffID

Depending on the values of [tblTeamInternal].[StaffID], i would like to
combine the associated Abbreviation values in tblStaffMembers using a
query.

Currently i have all associated fields in the query and have created an
additonal field which uses the expression - Team: Concatenate("SELECT
Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt].[StaffID]).

This is not working for me first of all because i am getting an undefined
function dialog popping up and am not sure whether i have the correct
expression.

Can anyone assist at all?

Regards
Mike
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Have you gone back and tried
Team: Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID ="
& [tblTeamInt].[StaffID])
This should work if StaffID is numeric.

--
Duane Hookom
Microsoft Access MVP


Mike Diamond said:
If it helps at all, i have noted below the statement that is showing the
relevant records versus that which isnt..

StaffID: Concatenate("SELECT StaffID FROM tblTeamInt WHERE SubmissionID =" &
[SubmissionID])

StaffName: Concatenate("SELECT tblStaffMembers.Abbreviation FROM
tblStaffMembers WHERE tblStaffMembers.StaffID = (SELECT TOP 1 T.StaffID FROM
tblTeamInt AS T WHERE T.SubmissionID =" & [SubmissionID] & ")")

===========================

Ken Snell (MVP) said:
Sorry, left out a closing parenthesis in the post I just made. Here is
corrected info:

Try this:

StaffID: Concatenate("SELECT tblStaffMembers.StaffName FROM tblStaffMembers
WHERE tblStaffMembers.StaffID = (SELECT TOP 1 T.StaffID FROM tblTeamInt AS T
WHERE T.SubmissionID =" & [SubmissionID] & ")")

--

Ken Snell
<MS ACCESS MVP>


Mike Diamond said:
Ken, I have managed to get the query showing the information in the way
that
i need to except for one thing. The result is shown in the format of the
ID
rather than Staff Names. The problem is that i need to refer to another
table
(tblStaffMembers) which is not part of the direct relationships of the
below.
How coul i ammend the SQL statement below to reference a field
(Abbreviation)
in the StaffMembers table?

StaffID: Concatenate("SELECT StaffID FROM tblTeamInt WHERE SubmissionID ="
&
[SubmissionID])


========================

:

I have a query where i am trying combine multiple records of a particular
field into a single record.

eg. instead of;

Record 1
AB

Record 2
CD

Record 3
EF

I want to have;

Record 1
AB, CD, EF

The tables i am using within the query are;

tblStaffMembers;
StaffID
StaffTitle
StaffFirstName
StaffSurname
Abbreviation

tblTeamInternal
IntTeamID
SubmissionID
StaffID

Depending on the values of [tblTeamInternal].[StaffID], i would like to
combine the associated Abbreviation values in tblStaffMembers using a
query.

Currently i have all associated fields in the query and have created an
additonal field which uses the expression - Team: Concatenate("SELECT
Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt].[StaffID]).

This is not working for me first of all because i am getting an undefined
function dialog popping up and am not sure whether i have the correct
expression.

Can anyone assist at all?

Regards
Mike
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Concatenate Question 6
Populating Cells with Table Data 1
Append Query question - Is this scenario possible? 1
Access 2007 - Equivalent to Excel's indirect function 3
A difficult one.. 2
Concatenate function from a Query, not a table 5
Problem with Concatenate Function 5
Concatenate multiple records in query 2

Top