Concantenation HELP!

[WestWingFan]

Thanks in advance for any help. Surely I'm not the only one who has ever
wanted to do this.....

I have three tables like this:

Table 1
ID(autonumber) Field 1 Field 2 Field 3

Table 2 - many to many join
[Table 1 ID] [Table 3 ID]

Table 3
ID(autonumber) Field 1 Field 2

I would like to create a query/other SQL statement which will produce fields
from Table 1 and then a filed which shows a comma separated series of the
related Field1 in Table 3. So I think it would be like...

(Table1.Field1) (Table1.Field2) (Table3.Field1, Field1, Field 1)

I'm new to coding and have searched for something like this, but I've only
found the ability to concatenate when the values are in the same table. Any
help will be appreciated!

 

[Duane Hookom]

This type of question gets asked and answered at least weekly in Access news
groups. There is a Concatenate() function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[WestWingFan]

Duane!! Thanks for the help. So if Table 1 is tblAAStdTbl, Table 2 is
tblAAS_CSS_realation, and Table 3 is tblJCCSS, my SQL should look like this?

SELECT tblAAStdTbl.*, Concatenate("SELECT CSSCode FROM tbJCCSS.JCCSSID WHERE
JCCSSID = " & [JCCSSID]) AS CSSList
FROM tblAAStdTbl LEFT JOIN tblAAS_CSS_relation ON
tblAAStdTbl.AASID=tblAAS_CSS_relation.AASID;

Only I imported the module and when I try to run it I get an "Undefined
function 'Concatenate' in expression." error. Sigh. Ideas?

This type of question gets asked and answered at least weekly in Access news
groups. There is a Concatenate() function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP

Thanks in advance for any help. Surely I'm not the only one who has ever
wanted to do this.....

I have three tables like this:

Table 1
ID(autonumber) Field 1 Field 2 Field 3

Table 2 - many to many join
[Table 1 ID] [Table 3 ID]

Table 3
ID(autonumber) Field 1 Field 2

I would like to create a query/other SQL statement which will produce fields
from Table 1 and then a filed which shows a comma separated series of the
related Field1 in Table 3. So I think it would be like...

(Table1.Field1) (Table1.Field2) (Table3.Field1, Field1, Field 1)

I'm new to coding and have searched for something like this, but I've only
found the ability to concatenate when the values are in the same table. Any
help will be appreciated!

 

[Duane Hookom]

Did you name the module "concatenate"? If so, change the name to something
like "modConcatenate".

I would create a query from the table 2 and table 3. Use this query in the
sql statement inside the Concatenate() function. Your main query would select
only from table 1 since the table 2/3 values would come from the query.


SELECT tblAAStdTbl.*, Concatenate("SELECT CSSCode FROM qselYourQuery WHERE
JCCSSID = " & [JCCSSID]) AS CSSList
FROM tblAAStdTbl;

This assumes JCCSSID is numeric.

--
Duane Hookom
Microsoft Access MVP

Duane!! Thanks for the help. So if Table 1 is tblAAStdTbl, Table 2 is
tblAAS_CSS_realation, and Table 3 is tblJCCSS, my SQL should look like this?

SELECT tblAAStdTbl.*, Concatenate("SELECT CSSCode FROM tbJCCSS.JCCSSID WHERE
JCCSSID = " & [JCCSSID]) AS CSSList
FROM tblAAStdTbl LEFT JOIN tblAAS_CSS_relation ON
tblAAStdTbl.AASID=tblAAS_CSS_relation.AASID;

Only I imported the module and when I try to run it I get an "Undefined
function 'Concatenate' in expression." error. Sigh. Ideas?

This type of question gets asked and answered at least weekly in Access news
groups. There is a Concatenate() function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP

Thanks in advance for any help. Surely I'm not the only one who has ever
wanted to do this.....

I have three tables like this:

Table 1
ID(autonumber) Field 1 Field 2 Field 3

Table 2 - many to many join
[Table 1 ID] [Table 3 ID]

Table 3
ID(autonumber) Field 1 Field 2

I would like to create a query/other SQL statement which will produce fields
from Table 1 and then a filed which shows a comma separated series of the
related Field1 in Table 3. So I think it would be like...

(Table1.Field1) (Table1.Field2) (Table3.Field1, Field1, Field 1)

I'm new to coding and have searched for something like this, but I've only
found the ability to concatenate when the values are in the same table. Any
help will be appreciated!

 

[WestWingFan]

Thanks for all your help. Not quite done yet.

It's named something else. You are correct JCCSSID is numeric. My SQL
statement is:

SELECT tblAAStdTbl.*, Concatenate("SELECT CSSCode FROM qryJCCSSListStart
WHERE JCCSSID = " & [JCCSSID]) AS CSSList
FROM tblAAStdTbl INNER JOIN qryJCCSSListStart ON tblAAStdTbl.AASID =
qryJCCSSListStart.AASID;

When I run it I get a runtime error -2147217904(80040e10): no value given
for one or more required parameters... which when I go to debug highlights
line 32 of the code in the module. Where to now?

Did you name the module "concatenate"? If so, change the name to something
like "modConcatenate".

I would create a query from the table 2 and table 3. Use this query in the
sql statement inside the Concatenate() function. Your main query would select
only from table 1 since the table 2/3 values would come from the query.


SELECT tblAAStdTbl.*, Concatenate("SELECT CSSCode FROM qselYourQuery WHERE
JCCSSID = " & [JCCSSID]) AS CSSList
FROM tblAAStdTbl;

This assumes JCCSSID is numeric.

--
Duane Hookom
Microsoft Access MVP

Duane!! Thanks for the help. So if Table 1 is tblAAStdTbl, Table 2 is
tblAAS_CSS_realation, and Table 3 is tblJCCSS, my SQL should look like this?

SELECT tblAAStdTbl.*, Concatenate("SELECT CSSCode FROM tbJCCSS.JCCSSID WHERE
JCCSSID = " & [JCCSSID]) AS CSSList
FROM tblAAStdTbl LEFT JOIN tblAAS_CSS_relation ON
tblAAStdTbl.AASID=tblAAS_CSS_relation.AASID;

Only I imported the module and when I try to run it I get an "Undefined
function 'Concatenate' in expression." error. Sigh. Ideas?

This type of question gets asked and answered at least weekly in Access news
groups. There is a Concatenate() function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


:

Thanks in advance for any help. Surely I'm not the only one who has ever
wanted to do this.....

I have three tables like this:

Table 1
ID(autonumber) Field 1 Field 2 Field 3

Table 2 - many to many join
[Table 1 ID] [Table 3 ID]

Table 3
ID(autonumber) Field 1 Field 2

I would like to create a query/other SQL statement which will produce fields
from Table 1 and then a filed which shows a comma separated series of the
related Field1 in Table 3. So I think it would be like...

(Table1.Field1) (Table1.Field2) (Table3.Field1, Field1, Field 1)

I'm new to coding and have searched for something like this, but I've only
found the ability to concatenate when the values are in the same table. Any
help will be appreciated!

 

[Duane Hookom]

I would think you would not include qryJCCSSListStar in your main query. The
details from qryJCCSSListStar will be "gathered and returned" in the
Concatenate function.

Also, you must make sure you don't have any records with no value in the
JCCSSID field.
--
Duane Hookom
Microsoft Access MVP

Thanks for all your help. Not quite done yet.

It's named something else. You are correct JCCSSID is numeric. My SQL
statement is:

SELECT tblAAStdTbl.*, Concatenate("SELECT CSSCode FROM qryJCCSSListStart
WHERE JCCSSID = " & [JCCSSID]) AS CSSList
FROM tblAAStdTbl INNER JOIN qryJCCSSListStart ON tblAAStdTbl.AASID =
qryJCCSSListStart.AASID;

When I run it I get a runtime error -2147217904(80040e10): no value given
for one or more required parameters... which when I go to debug highlights
line 32 of the code in the module. Where to now?

Did you name the module "concatenate"? If so, change the name to something
like "modConcatenate".

I would create a query from the table 2 and table 3. Use this query in the
sql statement inside the Concatenate() function. Your main query would select
only from table 1 since the table 2/3 values would come from the query.


SELECT tblAAStdTbl.*, Concatenate("SELECT CSSCode FROM qselYourQuery WHERE
JCCSSID = " & [JCCSSID]) AS CSSList
FROM tblAAStdTbl;

This assumes JCCSSID is numeric.

--
Duane Hookom
Microsoft Access MVP

Duane!! Thanks for the help. So if Table 1 is tblAAStdTbl, Table 2 is
tblAAS_CSS_realation, and Table 3 is tblJCCSS, my SQL should look like this?

SELECT tblAAStdTbl.*, Concatenate("SELECT CSSCode FROM tbJCCSS.JCCSSID WHERE
JCCSSID = " & [JCCSSID]) AS CSSList
FROM tblAAStdTbl LEFT JOIN tblAAS_CSS_relation ON
tblAAStdTbl.AASID=tblAAS_CSS_relation.AASID;

Only I imported the module and when I try to run it I get an "Undefined
function 'Concatenate' in expression." error. Sigh. Ideas?

:

This type of question gets asked and answered at least weekly in Access news
groups. There is a Concatenate() function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


:

Thanks in advance for any help. Surely I'm not the only one who has ever
wanted to do this.....

I have three tables like this:

Table 1
ID(autonumber) Field 1 Field 2 Field 3

Table 2 - many to many join
[Table 1 ID] [Table 3 ID]

Table 3
ID(autonumber) Field 1 Field 2

I would like to create a query/other SQL statement which will produce fields
from Table 1 and then a filed which shows a comma separated series of the
related Field1 in Table 3. So I think it would be like...

(Table1.Field1) (Table1.Field2) (Table3.Field1, Field1, Field 1)

I'm new to coding and have searched for something like this, but I've only
found the ability to concatenate when the values are in the same table. Any
help will be appreciated!

 

[WestWingFan]

Works like a charm. Thanks for all your help!

I would think you would not include qryJCCSSListStar in your main query. The
details from qryJCCSSListStar will be "gathered and returned" in the
Concatenate function.

Also, you must make sure you don't have any records with no value in the
JCCSSID field.
--
Duane Hookom
Microsoft Access MVP

Thanks for all your help. Not quite done yet.

It's named something else. You are correct JCCSSID is numeric. My SQL
statement is:

SELECT tblAAStdTbl.*, Concatenate("SELECT CSSCode FROM qryJCCSSListStart
WHERE JCCSSID = " & [JCCSSID]) AS CSSList
FROM tblAAStdTbl INNER JOIN qryJCCSSListStart ON tblAAStdTbl.AASID =
qryJCCSSListStart.AASID;

When I run it I get a runtime error -2147217904(80040e10): no value given
for one or more required parameters... which when I go to debug highlights
line 32 of the code in the module. Where to now?

Did you name the module "concatenate"? If so, change the name to something
like "modConcatenate".

I would create a query from the table 2 and table 3. Use this query in the
sql statement inside the Concatenate() function. Your main query would select
only from table 1 since the table 2/3 values would come from the query.


SELECT tblAAStdTbl.*, Concatenate("SELECT CSSCode FROM qselYourQuery WHERE
JCCSSID = " & [JCCSSID]) AS CSSList
FROM tblAAStdTbl;

This assumes JCCSSID is numeric.

--
Duane Hookom
Microsoft Access MVP


:

Duane!! Thanks for the help. So if Table 1 is tblAAStdTbl, Table 2 is
tblAAS_CSS_realation, and Table 3 is tblJCCSS, my SQL should look like this?

SELECT tblAAStdTbl.*, Concatenate("SELECT CSSCode FROM tbJCCSS.JCCSSID WHERE
JCCSSID = " & [JCCSSID]) AS CSSList
FROM tblAAStdTbl LEFT JOIN tblAAS_CSS_relation ON
tblAAStdTbl.AASID=tblAAS_CSS_relation.AASID;

Only I imported the module and when I try to run it I get an "Undefined
function 'Concatenate' in expression." error. Sigh. Ideas?

:

This type of question gets asked and answered at least weekly in Access news
groups. There is a Concatenate() function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


:

Thanks in advance for any help. Surely I'm not the only one who has ever
wanted to do this.....

I have three tables like this:

Table 1
ID(autonumber) Field 1 Field 2 Field 3

Table 2 - many to many join
[Table 1 ID] [Table 3 ID]

Table 3
ID(autonumber) Field 1 Field 2

I would like to create a query/other SQL statement which will produce fields
from Table 1 and then a filed which shows a comma separated series of the
related Field1 in Table 3. So I think it would be like...

(Table1.Field1) (Table1.Field2) (Table3.Field1, Field1, Field 1)

I'm new to coding and have searched for something like this, but I've only
found the ability to concatenate when the values are in the same table. Any
help will be appreciated!