Difficulty with Concatenate Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having trouble and have been dfor some time with sorting out this issue
i am having with concatenating.

I have taken a look Duane Hookams example on this and found it helpful but
need to refer and concatenate a field in a seperate table. I have taken a
print screen image of the query window to show what i mean if i could send
this to anyone? Any help would be greatly appreciated.

Regards
Mike
 
If part of what you want to concatenate is two fields in related tables, you
would do it in a query and it would look something like:
Concatstring=Table1.FieldInTable1 & " " & "String1" & " " &
Table2.FieldInTable2 & " " & "String2"

If this is close to what you are looking for, use your own table names,
field names and strings for String1 and String2. Remove from the expression
what you don't need.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
I am having trouble and have been dfor some time with sorting out this issue
i am having with concatenating.

I have taken a look Duane Hookams example on this and found it helpful but
need to refer and concatenate a field in a seperate table. I have taken a
print screen image of the query window to show what i mean if i could send
this to anyone? Any help would be greatly appreciated.

Regards
Mike
Click to expand...

A screen print is far less useful and far larger than the SQL view of the
query. Just open the query in design view; select View... SQL; copy and paste
the SQL text here. That's the *real* query.


John W. Vinson [MVP]
 
Hi John

Good Point..as below..

What i would like to do is have the abbreviation field concatenated for all
related records carrying the same submissionid. The below is showing me three
related records unconcatenated.

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]) AS StaffID
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]);


Regards
Mike
 
Hi Steve

Thanks for the reply. I am looking to concatenate the same field for each
record that it is related. ie;

record 1
record 2
record 3

AS

record 1, record 2, record 3..
 
Hi John

Good Point..as below..

What i would like to do is have the abbreviation field concatenated for all
related records carrying the same submissionid. The below is showing me three
related records unconcatenated.

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]) AS StaffID
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]);
Click to expand...

You're concatenating all the records (all one of them!!!) for the given
StaffID - that's what is in your Concatenate statement! I think you need
SubmissionID instead; and to do that you'll need to join tblSubmissions inside
the concatenate function. Try

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID IN(SELECT
[tblTeamInt]![StaffID] FROM tblTeamInt WHERE tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ")") AS StaffNames
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

You might need to Group By the Concatenate expression (though you should be
able to use Expression as the totals operator).

John W. Vinson [MVP]
 
Top Man John, that worked a dream..!

Regards
Mike

John W. Vinson said:
Hi John

Good Point..as below..

What i would like to do is have the abbreviation field concatenated for all
related records carrying the same submissionid. The below is showing me three
related records unconcatenated.

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]) AS StaffID
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]);
Click to expand...

You're concatenating all the records (all one of them!!!) for the given
StaffID - that's what is in your Concatenate statement! I think you need
SubmissionID instead; and to do that you'll need to join tblSubmissions inside
the concatenate function. Try

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID IN(SELECT
[tblTeamInt]![StaffID] FROM tblTeamInt WHERE tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ")") AS StaffNames
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

You might need to Group By the Concatenate expression (though you should be
able to use Expression as the totals operator).

John W. Vinson [MVP]
Click to expand...
 
Hi John

Thanks again for your assistance earlier, that worked perfect for me except
for one other thing..(just to get blood from a stone..!)

I would really like for the records i am concatenating to be put in the
order that they were entered (ie sorted via the TeamIntID in tblTeamInt) any
ideas how i could do this?

Regards
Mike

John W. Vinson said:
Hi John

Good Point..as below..

What i would like to do is have the abbreviation field concatenated for all
related records carrying the same submissionid. The below is showing me three
related records unconcatenated.

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]) AS StaffID
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]);
Click to expand...

You're concatenating all the records (all one of them!!!) for the given
StaffID - that's what is in your Concatenate statement! I think you need
SubmissionID instead; and to do that you'll need to join tblSubmissions inside
the concatenate function. Try

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID IN(SELECT
[tblTeamInt]![StaffID] FROM tblTeamInt WHERE tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ")") AS StaffNames
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

You might need to Group By the Concatenate expression (though you should be
able to use Expression as the totals operator).

John W. Vinson [MVP]
Click to expand...
 
Try adding an order by clause to the query you are using in the
concatenate function

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,

Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
IN(SELECT [tblTeamInt]![StaffID] FROM tblTeamInt WHERE
tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ") ORDER BY TeamInt.Id") AS StaffNames

FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike said:
Hi John

Thanks again for your assistance earlier, that worked perfect for me except
for one other thing..(just to get blood from a stone..!)

I would really like for the records i am concatenating to be put in the
order that they were entered (ie sorted via the TeamIntID in tblTeamInt) any
ideas how i could do this?

Regards
Mike

John W. Vinson said:
Hi John

Good Point..as below..

What i would like to do is have the abbreviation field concatenated for all
related records carrying the same submissionid. The below is showing me three
related records unconcatenated.

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]) AS StaffID
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]);
Click to expand...
You're concatenating all the records (all one of them!!!) for the given
StaffID - that's what is in your Concatenate statement! I think you need
SubmissionID instead; and to do that you'll need to join tblSubmissions inside
the concatenate function. Try

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID IN(SELECT
[tblTeamInt]![StaffID] FROM tblTeamInt WHERE tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ")") AS StaffNames
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

You might need to Group By the Concatenate expression (though you should be
able to use Expression as the totals operator).

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
Hi John, thanks for the quick reply, that works except that if there is 1
record or less to be concatenated, an error is returned. Is there anyway to
get a single field shown?

John Spencer said:
Try adding an order by clause to the query you are using in the
concatenate function

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,

Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
IN(SELECT [tblTeamInt]![StaffID] FROM tblTeamInt WHERE
tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ") ORDER BY TeamInt.Id") AS StaffNames

FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike said:
Hi John

Thanks again for your assistance earlier, that worked perfect for me except
for one other thing..(just to get blood from a stone..!)

I would really like for the records i am concatenating to be put in the
order that they were entered (ie sorted via the TeamIntID in tblTeamInt) any
ideas how i could do this?

Regards
Mike

John W. Vinson said:
On Mon, 2 Jul 2007 11:28:02 -0700, Mike Diamond

Hi John

Good Point..as below..

What i would like to do is have the abbreviation field concatenated for all
related records carrying the same submissionid. The below is showing me three
related records unconcatenated.

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]) AS StaffID
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]);
You're concatenating all the records (all one of them!!!) for the given
StaffID - that's what is in your Concatenate statement! I think you need
SubmissionID instead; and to do that you'll need to join tblSubmissions inside
the concatenate function. Try

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID IN(SELECT
[tblTeamInt]![StaffID] FROM tblTeamInt WHERE tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ")") AS StaffNames
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

You might need to Group By the Concatenate expression (though you should be
able to use Expression as the totals operator).

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
Sorry John, i have had a look at this again and it seems that the concatenate
function in vba is what is putting this out. When i remove the ORDER BY
command the problem goes, but when it is left in a 'too few parameters,
expected 1' error message comes up and debugger points to the 'Set rs =
db.OpenRecordset(pstrSQL)' line of code within the DAO function..

Mike Diamond said:
Hi John, thanks for the quick reply, that works except that if there is 1
record or less to be concatenated, an error is returned. Is there anyway to
get a single field shown?

John Spencer said:
Try adding an order by clause to the query you are using in the
concatenate function

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,

Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
IN(SELECT [tblTeamInt]![StaffID] FROM tblTeamInt WHERE
tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ") ORDER BY TeamInt.Id") AS StaffNames

FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike said:
Hi John

Thanks again for your assistance earlier, that worked perfect for me except
for one other thing..(just to get blood from a stone..!)

I would really like for the records i am concatenating to be put in the
order that they were entered (ie sorted via the TeamIntID in tblTeamInt) any
ideas how i could do this?

Regards
Mike

:

On Mon, 2 Jul 2007 11:28:02 -0700, Mike Diamond

Hi John

Good Point..as below..

What i would like to do is have the abbreviation field concatenated for all
related records carrying the same submissionid. The below is showing me three
related records unconcatenated.

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]) AS StaffID
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]);
You're concatenating all the records (all one of them!!!) for the given
StaffID - that's what is in your Concatenate statement! I think you need
SubmissionID instead; and to do that you'll need to join tblSubmissions inside
the concatenate function. Try

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID IN(SELECT
[tblTeamInt]![StaffID] FROM tblTeamInt WHERE tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ")") AS StaffNames
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

You might need to Group By the Concatenate expression (though you should be
able to use Expression as the totals operator).

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
Mike, you should always check the code you are given. I believe that I
messed up the order by clause by typing the wrong table and field names.

TeamIntID in tblTeamInt

It should probably read

ORDER BY tblTeamInt.TeamIntID

What I typed was
ORDER BY TeamInt.Id

The SQL interpreter sees that and complains because it has no idea what
that table is and what that field is.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike said:
Sorry John, i have had a look at this again and it seems that the concatenate
function in vba is what is putting this out. When i remove the ORDER BY
command the problem goes, but when it is left in a 'too few parameters,
expected 1' error message comes up and debugger points to the 'Set rs =
db.OpenRecordset(pstrSQL)' line of code within the DAO function..

Mike Diamond said:
Hi John, thanks for the quick reply, that works except that if there is 1
record or less to be concatenated, an error is returned. Is there anyway to
get a single field shown?

John Spencer said:
Try adding an order by clause to the query you are using in the
concatenate function

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,

Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
IN(SELECT [tblTeamInt]![StaffID] FROM tblTeamInt WHERE
tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ") ORDER BY TeamInt.Id") AS StaffNames

FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike Diamond wrote:
Hi John

Thanks again for your assistance earlier, that worked perfect for me except
for one other thing..(just to get blood from a stone..!)

I would really like for the records i am concatenating to be put in the
order that they were entered (ie sorted via the TeamIntID in tblTeamInt) any
ideas how i could do this?

Regards
Mike

:

On Mon, 2 Jul 2007 11:28:02 -0700, Mike Diamond

Hi John

Good Point..as below..

What i would like to do is have the abbreviation field concatenated for all
related records carrying the same submissionid. The below is showing me three
related records unconcatenated.

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]) AS StaffID
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]);
You're concatenating all the records (all one of them!!!) for the given
StaffID - that's what is in your Concatenate statement! I think you need
SubmissionID instead; and to do that you'll need to join tblSubmissions inside
the concatenate function. Try

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID IN(SELECT
[tblTeamInt]![StaffID] FROM tblTeamInt WHERE tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ")") AS StaffNames
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

You might need to Group By the Concatenate expression (though you should be
able to use Expression as the totals operator).

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
no, i had changed the field name to the correct one but this message still
comes up..

John Spencer said:
Mike, you should always check the code you are given. I believe that I
messed up the order by clause by typing the wrong table and field names.

TeamIntID in tblTeamInt

It should probably read

ORDER BY tblTeamInt.TeamIntID

What I typed was
ORDER BY TeamInt.Id

The SQL interpreter sees that and complains because it has no idea what
that table is and what that field is.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike said:
Sorry John, i have had a look at this again and it seems that the concatenate
function in vba is what is putting this out. When i remove the ORDER BY
command the problem goes, but when it is left in a 'too few parameters,
expected 1' error message comes up and debugger points to the 'Set rs =
db.OpenRecordset(pstrSQL)' line of code within the DAO function..

Mike Diamond said:
Hi John, thanks for the quick reply, that works except that if there is 1
record or less to be concatenated, an error is returned. Is there anyway to
get a single field shown?

:

Try adding an order by clause to the query you are using in the
concatenate function

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,

Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
IN(SELECT [tblTeamInt]![StaffID] FROM tblTeamInt WHERE
tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ") ORDER BY TeamInt.Id") AS StaffNames

FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike Diamond wrote:
Hi John

Thanks again for your assistance earlier, that worked perfect for me except
for one other thing..(just to get blood from a stone..!)

I would really like for the records i am concatenating to be put in the
order that they were entered (ie sorted via the TeamIntID in tblTeamInt) any
ideas how i could do this?

Regards
Mike

:

On Mon, 2 Jul 2007 11:28:02 -0700, Mike Diamond

Hi John

Good Point..as below..

What i would like to do is have the abbreviation field concatenated for all
related records carrying the same submissionid. The below is showing me three
related records unconcatenated.

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]) AS StaffID
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID =" &
[tblTeamInt]![StaffID]);
You're concatenating all the records (all one of them!!!) for the given
StaffID - that's what is in your Concatenate statement! I think you need
SubmissionID instead; and to do that you'll need to join tblSubmissions inside
the concatenate function. Try

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID IN(SELECT
[tblTeamInt]![StaffID] FROM tblTeamInt WHERE tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ")") AS StaffNames
FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN (tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID = tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

You might need to Group By the Concatenate expression (though you should be
able to use Expression as the totals operator).

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
My goof! I suspect you need a query that joins tblStaffMembers to tblTeamInt
so the field is available in the query.

Perhaps something like the following where we replace the subquery in the
where clause with a join and a where clause.

Concatenate("SELECT Abbreviation
FROM tblStaffMembers INNER JOIN tblTeamInt
ON tblStaffMembers.StaffID = tblTeamInt.StafffID
WHERE tblTeamInt.SubmissionID = " & tblSubmissions.Submission ID &
" ORDER BY tblTeamInt.TeamIntID") as StaffNames



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Mike Diamond said:
no, i had changed the field name to the correct one but this message still
comes up..

John Spencer said:
Mike, you should always check the code you are given. I believe that I
messed up the order by clause by typing the wrong table and field names.

TeamIntID in tblTeamInt

It should probably read

ORDER BY tblTeamInt.TeamIntID

What I typed was
ORDER BY TeamInt.Id

The SQL interpreter sees that and complains because it has no idea what
that table is and what that field is.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike said:
Sorry John, i have had a look at this again and it seems that the
concatenate
function in vba is what is putting this out. When i remove the ORDER BY
command the problem goes, but when it is left in a 'too few parameters,
expected 1' error message comes up and debugger points to the 'Set rs =
db.OpenRecordset(pstrSQL)' line of code within the DAO function..

:

Hi John, thanks for the quick reply, that works except that if there
is 1
record or less to be concatenated, an error is returned. Is there
anyway to
get a single field shown?

:

Try adding an order by clause to the query you are using in the
concatenate function

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,

Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
IN(SELECT [tblTeamInt]![StaffID] FROM tblTeamInt WHERE
tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ") ORDER BY TeamInt.Id") AS StaffNames

FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID
=
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID =
tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike Diamond wrote:
Hi John

Thanks again for your assistance earlier, that worked perfect for me
except
for one other thing..(just to get blood from a stone..!)

I would really like for the records i am concatenating to be put in
the
order that they were entered (ie sorted via the TeamIntID in
tblTeamInt) any
ideas how i could do this?

Regards
Mike

:

On Mon, 2 Jul 2007 11:28:02 -0700, Mike Diamond

Hi John

Good Point..as below..

What i would like to do is have the abbreviation field
concatenated for all
related records carrying the same submissionid. The below is
showing me three
related records unconcatenated.

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE
StaffID =" &
[tblTeamInt]![StaffID]) AS StaffID
FROM ((tblProjects INNER JOIN tblSubmissions ON
tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID =
tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE
StaffID =" &
[tblTeamInt]![StaffID]);
You're concatenating all the records (all one of them!!!) for the
given
StaffID - that's what is in your Concatenate statement! I think you
need
SubmissionID instead; and to do that you'll need to join
tblSubmissions inside
the concatenate function. Try

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
IN(SELECT
[tblTeamInt]![StaffID] FROM tblTeamInt WHERE
tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ")") AS StaffNames
FROM ((tblProjects INNER JOIN tblSubmissions ON
tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID =
tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

You might need to Group By the Concatenate expression (though you
should be
able to use Expression as the totals operator).

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
Thank you John for you time and assistance on this. I have updated the sql as
you suggested and the error is still there. It looks like it is a visual
basic error run time no 3061 and it refers to the line of code which sets
recordset?

John Spencer said:
My goof! I suspect you need a query that joins tblStaffMembers to tblTeamInt
so the field is available in the query.

Perhaps something like the following where we replace the subquery in the
where clause with a join and a where clause.

Concatenate("SELECT Abbreviation
FROM tblStaffMembers INNER JOIN tblTeamInt
ON tblStaffMembers.StaffID = tblTeamInt.StafffID
WHERE tblTeamInt.SubmissionID = " & tblSubmissions.Submission ID &
" ORDER BY tblTeamInt.TeamIntID") as StaffNames



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Mike Diamond said:
no, i had changed the field name to the correct one but this message still
comes up..

John Spencer said:
Mike, you should always check the code you are given. I believe that I
messed up the order by clause by typing the wrong table and field names.

TeamIntID in tblTeamInt

It should probably read

ORDER BY tblTeamInt.TeamIntID

What I typed was
ORDER BY TeamInt.Id

The SQL interpreter sees that and complains because it has no idea what
that table is and what that field is.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike Diamond wrote:
Sorry John, i have had a look at this again and it seems that the
concatenate
function in vba is what is putting this out. When i remove the ORDER BY
command the problem goes, but when it is left in a 'too few parameters,
expected 1' error message comes up and debugger points to the 'Set rs =
db.OpenRecordset(pstrSQL)' line of code within the DAO function..

:

Hi John, thanks for the quick reply, that works except that if there
is 1
record or less to be concatenated, an error is returned. Is there
anyway to
get a single field shown?

:

Try adding an order by clause to the query you are using in the
concatenate function

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,

Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
IN(SELECT [tblTeamInt]![StaffID] FROM tblTeamInt WHERE
tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ") ORDER BY TeamInt.Id") AS StaffNames

FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID
=
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID =
tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike Diamond wrote:
Hi John

Thanks again for your assistance earlier, that worked perfect for me
except
for one other thing..(just to get blood from a stone..!)

I would really like for the records i am concatenating to be put in
the
order that they were entered (ie sorted via the TeamIntID in
tblTeamInt) any
ideas how i could do this?

Regards
Mike

:

On Mon, 2 Jul 2007 11:28:02 -0700, Mike Diamond

Hi John

Good Point..as below..

What i would like to do is have the abbreviation field
concatenated for all
related records carrying the same submissionid. The below is
showing me three
related records unconcatenated.

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE
StaffID =" &
[tblTeamInt]![StaffID]) AS StaffID
FROM ((tblProjects INNER JOIN tblSubmissions ON
tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID =
tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE
StaffID =" &
[tblTeamInt]![StaffID]);
You're concatenating all the records (all one of them!!!) for the
given
StaffID - that's what is in your Concatenate statement! I think you
need
SubmissionID instead; and to do that you'll need to join
tblSubmissions inside
the concatenate function. Try

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
IN(SELECT
[tblTeamInt]![StaffID] FROM tblTeamInt WHERE
tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ")") AS StaffNames
FROM ((tblProjects INNER JOIN tblSubmissions ON
tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID =
tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

You might need to Group By the Concatenate expression (though you
should be
able to use Expression as the totals operator).

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
That is strange. It works if you leave the order by clause off, so
there has to be something with the query string that we aren't doing
correctly.

Right now, I am not seeing what that is. Although there was a typo in
the last suggestion I gave you

Concatenate("SELECT Abbreviation
FROM tblStaffMembers INNER JOIN tblTeamInt
ON tblStaffMembers.StaffID = tblTeamInt.StafffID
WHERE tblTeamInt.SubmissionID = " & tblSubmissions.SubmissionID &
" ORDER BY tblTeamInt.TeamIntID") as StaffNames

I am assuming that submissionID is a number field. If it is a text
field then we need to add some quotes into that as text delimiters.


Concatenate("SELECT Abbreviation
FROM tblStaffMembers INNER JOIN tblTeamInt
ON tblStaffMembers.StaffID = tblTeamInt.StafffID
WHERE tblTeamInt.SubmissionID = """ & tblSubmissions.SubmissionID &
""" ORDER BY tblTeamInt.TeamIntID") as StaffNames

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike said:
Thank you John for you time and assistance on this. I have updated the sql as
you suggested and the error is still there. It looks like it is a visual
basic error run time no 3061 and it refers to the line of code which sets
recordset?

John Spencer said:
My goof! I suspect you need a query that joins tblStaffMembers to tblTeamInt
so the field is available in the query.

Perhaps something like the following where we replace the subquery in the
where clause with a join and a where clause.

Concatenate("SELECT Abbreviation
FROM tblStaffMembers INNER JOIN tblTeamInt
ON tblStaffMembers.StaffID = tblTeamInt.StafffID
WHERE tblTeamInt.SubmissionID = " & tblSubmissions.Submission ID &
" ORDER BY tblTeamInt.TeamIntID") as StaffNames



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Mike Diamond said:
no, i had changed the field name to the correct one but this message still
comes up..

:

Mike, you should always check the code you are given. I believe that I
messed up the order by clause by typing the wrong table and field names.

TeamIntID in tblTeamInt

It should probably read

ORDER BY tblTeamInt.TeamIntID

What I typed was
ORDER BY TeamInt.Id

The SQL interpreter sees that and complains because it has no idea what
that table is and what that field is.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike Diamond wrote:
Sorry John, i have had a look at this again and it seems that the
concatenate
function in vba is what is putting this out. When i remove the ORDER BY
command the problem goes, but when it is left in a 'too few parameters,
expected 1' error message comes up and debugger points to the 'Set rs =
db.OpenRecordset(pstrSQL)' line of code within the DAO function..

:

Hi John, thanks for the quick reply, that works except that if there
is 1
record or less to be concatenated, an error is returned. Is there
anyway to
get a single field shown?

:

Try adding an order by clause to the query you are using in the
concatenate function

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,

Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
IN(SELECT [tblTeamInt]![StaffID] FROM tblTeamInt WHERE
tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ") ORDER BY TeamInt.Id") AS StaffNames

FROM ((tblProjects INNER JOIN tblSubmissions ON tblProjects.ProjectID
=
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID =
tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mike Diamond wrote:
Hi John

Thanks again for your assistance earlier, that worked perfect for me
except
for one other thing..(just to get blood from a stone..!)

I would really like for the records i am concatenating to be put in
the
order that they were entered (ie sorted via the TeamIntID in
tblTeamInt) any
ideas how i could do this?

Regards
Mike

:

On Mon, 2 Jul 2007 11:28:02 -0700, Mike Diamond

Hi John

Good Point..as below..

What i would like to do is have the abbreviation field
concatenated for all
related records carrying the same submissionid. The below is
showing me three
related records unconcatenated.

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE
StaffID =" &
[tblTeamInt]![StaffID]) AS StaffID
FROM ((tblProjects INNER JOIN tblSubmissions ON
tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID =
tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE
StaffID =" &
[tblTeamInt]![StaffID]);
You're concatenating all the records (all one of them!!!) for the
given
StaffID - that's what is in your Concatenate statement! I think you
need
SubmissionID instead; and to do that you'll need to join
tblSubmissions inside
the concatenate function. Try

SELECT tblProjects.ProjectID, tblSubmissions.SubmissionID,
Concatenate("SELECT Abbreviation FROM tblStaffMembers WHERE StaffID
IN(SELECT
[tblTeamInt]![StaffID] FROM tblTeamInt WHERE
tblTeamInt.SubmissionID=" &
tblSubmissions.SubmissionID & ")") AS StaffNames
FROM ((tblProjects INNER JOIN tblSubmissions ON
tblProjects.ProjectID =
tblSubmissions.ProjectID) INNER JOIN tblTeamExt ON
tblSubmissions.SubmissionID = tblTeamExt.SubmissionID) INNER JOIN
(tblTeamInt
INNER JOIN tblStaffMembers ON tblTeamInt.StaffID =
tblStaffMembers.StaffID)
ON tblSubmissions.SubmissionID = tblTeamInt.SubmissionID
GROUP BY tblProjects.ProjectID, tblSubmissions.SubmissionID;

You might need to Group By the Concatenate expression (though you
should be
able to use Expression as the totals operator).

John W. Vinson [MVP]
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 function from a Query, not a table 5
Concatenate Question 6
Concatenate Question 1
Undefined function 'concatenate' in expression 0
Concatenate with ORDER BY clause 2
Concatenated Field - Sort Order 4
Concatenate with SQL - Question for Michel Walsh 3
Concatenate Question 2

Back
Top