fConcatChild Function help?

[Ray S.]

I have been using Dev Ashish's Function fConcatChild
http://www.mvps.org/access/modules/mdl0004.htm
With a few minor modifications I was able to get it to do exactly what I
needed. Now, I'm trying to use a modification of it to concatenate a series
of short messages in a field so I can perform mail merges on grouped query
results: for example, I'll have names of employees whom I have to remind to
complete their timesheets, which is easy enough to performa a mailmerge on;
but I also have to notify managers that perhaps multiple numbers of their
employees have not complied. Dev's function should theoretically allow me to
send one email per manager with concatenated messages regarding each employee.

I've followed all the same procedures to do this as before, but for some
reason it does not work in my current project. I'm trying to track down what
the problem could be. The difference now is that I'm not using (and can't) an
autonumber data type for the primary key in the "one side table". It is the
employee id, which is a string.

The "one side table" has the fields ID and Manager. The "many side table"
has the ID, Manager, MgrEmail, Employee, and Message. I've tried using memo
type for the Message, but nothing works. The "many sided table" has managerID
and Message as a combined primary key. The "one sided table" has ID as the
primary key. I'm not sure if I have properly created the managerID as a
foreign key. It appears the relationship between the two tables is right.
When I open the "one side table" I can click on the little "plus" icon and
open the corresponding information in the "many side table".

The code compiles and I get no errors, but nothing appears in the
concatenated field. The function use is as follows:

SELECT onesidetable.Manager,
fConcatChild("manysidetable","ID","Message","String",
[onesidetable].[Message] AS Msg, manysidetable.MgrEmail
FROM onesidetable RIGHT JOIN manysidetable ON onesidetable.ID =
manysidetable.ID
GROUP BY onesidetable.Manager,
fConcatChild("manysidetable","ID","Message","String",[onesidetable].[Message], manysidetable.MgrEmail;

 

[Dirk Goldgar]

In

I have been using Dev Ashish's Function fConcatChild
http://www.mvps.org/access/modules/mdl0004.htm
With a few minor modifications I was able to get it to do exactly
what I
needed. Now, I'm trying to use a modification of it to concatenate a
series
of short messages in a field so I can perform mail merges on grouped
query
results: for example, I'll have names of employees whom I have to
remind to
complete their timesheets, which is easy enough to performa a
mailmerge on;
but I also have to notify managers that perhaps multiple numbers of
their
employees have not complied. Dev's function should theoretically
allow me to
send one email per manager with concatenated messages regarding each
employee.

I've followed all the same procedures to do this as before, but for
some
reason it does not work in my current project. I'm trying to track
down what
the problem could be. The difference now is that I'm not using (and
can't) an
autonumber data type for the primary key in the "one side table". It
is the
employee id, which is a string.

The "one side table" has the fields ID and Manager. The "many side
table"
has the ID, Manager, MgrEmail, Employee, and Message. I've tried
using memo
type for the Message, but nothing works. The "many sided table" has
managerID
and Message as a combined primary key. The "one sided table" has ID
as the
primary key. I'm not sure if I have properly created the managerID as
a
foreign key. It appears the relationship between the two tables is
right.
When I open the "one side table" I can click on the little "plus"
icon and
open the corresponding information in the "many side table".

The code compiles and I get no errors, but nothing appears in the
concatenated field. The function use is as follows:

SELECT onesidetable.Manager,
fConcatChild("manysidetable","ID","Message","String",
[onesidetable].[Message] AS Msg, manysidetable.MgrEmail
FROM onesidetable RIGHT JOIN manysidetable ON onesidetable.ID =
manysidetable.ID
GROUP BY onesidetable.Manager,
fConcatChild("manysidetable","ID","Message","String",[onesidetable].[Message],
manysidetable.MgrEmail;

The SQL statement you posted above is clearly wrong, because there's no
closing parenthesis on the function call. Exactly what you need isn't
completely clear to me, but I'd think the function expression should be
something like this (rearranged for readability):

fConcatChild(
"manysidetable",
"ManagerID",
"Message",
"String",
[onesidetable].[ID])

In the above,

+ "manysidetable" is the name of the table containing the values you
want to concatenate,

+ "ManagerID" is the name of the field in that table that links the
table to the one-side table,

+ "Message" is the name of the field containing the values you want to
concatenate,

+ "String" is your statement to the function that the ID values are
strings, and

+ [onesidetable].[ID] passes the value of the manager ID in the
[onesidetable] used in your query.

 

[Ray S.]

I may have typed the SQL wrong, but I figured out the problem by myself. I
just needed to add a library reference. Thanks anyway.

In

I have been using Dev Ashish's Function fConcatChild
http://www.mvps.org/access/modules/mdl0004.htm
With a few minor modifications I was able to get it to do exactly
what I
needed. Now, I'm trying to use a modification of it to concatenate a
series
of short messages in a field so I can perform mail merges on grouped
query
results: for example, I'll have names of employees whom I have to
remind to
complete their timesheets, which is easy enough to performa a
mailmerge on;
but I also have to notify managers that perhaps multiple numbers of
their
employees have not complied. Dev's function should theoretically
allow me to
send one email per manager with concatenated messages regarding each
employee.

I've followed all the same procedures to do this as before, but for
some
reason it does not work in my current project. I'm trying to track
down what
the problem could be. The difference now is that I'm not using (and
can't) an
autonumber data type for the primary key in the "one side table". It
is the
employee id, which is a string.

The "one side table" has the fields ID and Manager. The "many side
table"
has the ID, Manager, MgrEmail, Employee, and Message. I've tried
using memo
type for the Message, but nothing works. The "many sided table" has
managerID
and Message as a combined primary key. The "one sided table" has ID
as the
primary key. I'm not sure if I have properly created the managerID as
a
foreign key. It appears the relationship between the two tables is
right.
When I open the "one side table" I can click on the little "plus"
icon and
open the corresponding information in the "many side table".

The code compiles and I get no errors, but nothing appears in the
concatenated field. The function use is as follows:

SELECT onesidetable.Manager,
fConcatChild("manysidetable","ID","Message","String",
[onesidetable].[Message] AS Msg, manysidetable.MgrEmail
FROM onesidetable RIGHT JOIN manysidetable ON onesidetable.ID =
manysidetable.ID
GROUP BY onesidetable.Manager,
fConcatChild("manysidetable","ID","Message","String",[onesidetable].[Message],
manysidetable.MgrEmail;

The SQL statement you posted above is clearly wrong, because there's no
closing parenthesis on the function call. Exactly what you need isn't
completely clear to me, but I'd think the function expression should be
something like this (rearranged for readability):

fConcatChild(
"manysidetable",
"ManagerID",
"Message",
"String",
[onesidetable].[ID])

In the above,

+ "manysidetable" is the name of the table containing the values you
want to concatenate,

+ "ManagerID" is the name of the field in that table that links the
table to the one-side table,

+ "Message" is the name of the field containing the values you want to
concatenate,

+ "String" is your statement to the function that the ID values are
strings, and

+ [onesidetable].[ID] passes the value of the manager ID in the
[onesidetable] used in your query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)