Combine field from several records

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

Guest

I have three records with the same badge number and I would like to combine
the text from all three comment fields into one field and get one record,
like:

badge comment
1 good worker
1 on-time
1 skilled
2 skilled

to

1 good worker, on-time, skilled
2 skilled

I've looked at the Duane Hookoms website and found the below expression.

Comments: Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" &
[Badge] & """")

However, it's not working. The query results still show four records:

badge comment
1 good worker
1 good worker
1 good worker
2 skilled

Any idea what I'm doing wrong? Thank you!
 
I suspect it would help if you posted the entire SQL Statement. My best
guess without that is that you are returning multiple records for each badge
number and then seeing the comments for each record.

I would expect to see a query that looks like the following.

SELECT DISTINCT Badge,
Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" & [Badge]
& """ ORDER BY Comment") as Comments
FROM YourTable


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks John. Here is my SQL:

SELECT tbl_Information.Badge, tbl_Information.Name, Concatenate("SELECT
Comment from tbl_Information WHERE Badge=""" & [Badge] & """") AS Comments
FROM tbl_Information;

I tried your suggestions and I did get one record, but the comment field
said, "on-time".

John Spencer said:
I suspect it would help if you posted the entire SQL Statement. My best
guess without that is that you are returning multiple records for each badge
number and then seeing the comments for each record.

I would expect to see a query that looks like the following.

SELECT DISTINCT Badge,
Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" & [Badge]
& """ ORDER BY Comment") as Comments
FROM YourTable


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

Alex said:
I have three records with the same badge number and I would like to combine
the text from all three comment fields into one field and get one record,
like:

badge comment
1 good worker
1 on-time
1 skilled
2 skilled

to

1 good worker, on-time, skilled
2 skilled

I've looked at the Duane Hookoms website and found the below expression.

Comments: Concatenate("SELECT Comment from tbl_Information WHERE Badge="""
&
[Badge] & """")

However, it's not working. The query results still show four records:

badge comment
1 good worker
1 good worker
1 good worker
2 skilled

Any idea what I'm doing wrong? Thank you!
Click to expand...
Click to expand...
 
SELECT DISTINCT tbl_Information.Badge, tbl_Information.Name
, Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" & [Badge]
& """") AS Comments
FROM tbl_Information;

If you are looking at the results in the Datasheet view of a query, have you
made the column big enough (wider or taller) to show the additional data?

If you are looking the results on a form or a report have you made the
control large enough to show all the information being returned?

Beyond that I am stuck on why you are having a problem. Are the comments in
tbl_Information or do you have them in a separate table linked to
tbl_Information?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alex said:
Thanks John. Here is my SQL:

SELECT tbl_Information.Badge, tbl_Information.Name, Concatenate("SELECT
Comment from tbl_Information WHERE Badge=""" & [Badge] & """") AS Comments
FROM tbl_Information;

I tried your suggestions and I did get one record, but the comment field
said, "on-time".

John Spencer said:
I suspect it would help if you posted the entire SQL Statement. My best
guess without that is that you are returning multiple records for each
badge
number and then seeing the comments for each record.

I would expect to see a query that looks like the following.

SELECT DISTINCT Badge,
Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" &
[Badge]
& """ ORDER BY Comment") as Comments
FROM YourTable


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

Alex said:
I have three records with the same badge number and I would like to
combine
the text from all three comment fields into one field and get one
record,
like:

badge comment
1 good worker
1 on-time
1 skilled
2 skilled

to

1 good worker, on-time, skilled
2 skilled

I've looked at the Duane Hookoms website and found the below
expression.

Comments: Concatenate("SELECT Comment from tbl_Information WHERE
Badge="""
&
[Badge] & """")

However, it's not working. The query results still show four records:

badge comment
1 good worker
1 good worker
1 good worker
2 skilled

Any idea what I'm doing wrong? Thank you!
Click to expand...
Click to expand...
Click to expand...
 
SELECT [Badge], [Name], Concatenate("SELECT Comment from tbl_Information
WHERE Badge=""" & [Badge] & """ ORDER BY Comment") AS Comments
FROM tbl_Information

You were right, I made my row height taller and there were more comments in
the column. But, each comment was listed 3 or 4 times. I only have one
table, do I need to have 2 in order for this to work?

John Spencer said:
SELECT DISTINCT tbl_Information.Badge, tbl_Information.Name
, Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" & [Badge]
& """") AS Comments
FROM tbl_Information;

If you are looking at the results in the Datasheet view of a query, have you
made the column big enough (wider or taller) to show the additional data?

If you are looking the results on a form or a report have you made the
control large enough to show all the information being returned?

Beyond that I am stuck on why you are having a problem. Are the comments in
tbl_Information or do you have them in a separate table linked to
tbl_Information?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alex said:
Thanks John. Here is my SQL:

SELECT tbl_Information.Badge, tbl_Information.Name, Concatenate("SELECT
Comment from tbl_Information WHERE Badge=""" & [Badge] & """") AS Comments
FROM tbl_Information;

I tried your suggestions and I did get one record, but the comment field
said, "on-time".

John Spencer said:
I suspect it would help if you posted the entire SQL Statement. My best
guess without that is that you are returning multiple records for each
badge
number and then seeing the comments for each record.

I would expect to see a query that looks like the following.

SELECT DISTINCT Badge,
Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" &
[Badge]
& """ ORDER BY Comment") as Comments
FROM YourTable


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

I have three records with the same badge number and I would like to
combine
the text from all three comment fields into one field and get one
record,
like:

badge comment
1 good worker
1 on-time
1 skilled
2 skilled

to

1 good worker, on-time, skilled
2 skilled

I've looked at the Duane Hookoms website and found the below
expression.

Comments: Concatenate("SELECT Comment from tbl_Information WHERE
Badge="""
&
[Badge] & """")

However, it's not working. The query results still show four records:

badge comment
1 good worker
1 good worker
1 good worker
2 skilled

Any idea what I'm doing wrong? Thank you!
Click to expand...
Click to expand...
Click to expand...
 
No you don't need to have two tables. Each comment should only be
listed ONE time for each time it is in the table, but if you have good
job in comment one and good job in a second record for the same badge
number, you will see good job twice. Try using Distinct in the
Concatenate query and see if that trims the comments for youl


SELECT DISTINCT tbl_Information.Badge, tbl_Information.Name
, Concatenate("SELECT DISTINCT Comment from tbl_Information WHERE
Badge=""" & [Badge]
& """") AS Comments
FROM tbl_Information;


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

SELECT [Badge], [Name], Concatenate("SELECT Comment from tbl_Information
WHERE Badge=""" & [Badge] & """ ORDER BY Comment") AS Comments
FROM tbl_Information

You were right, I made my row height taller and there were more comments in
the column. But, each comment was listed 3 or 4 times. I only have one
table, do I need to have 2 in order for this to work?

John Spencer said:
SELECT DISTINCT tbl_Information.Badge, tbl_Information.Name
, Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" & [Badge]
& """") AS Comments
FROM tbl_Information;

If you are looking at the results in the Datasheet view of a query, have you
made the column big enough (wider or taller) to show the additional data?

If you are looking the results on a form or a report have you made the
control large enough to show all the information being returned?

Beyond that I am stuck on why you are having a problem. Are the comments in
tbl_Information or do you have them in a separate table linked to
tbl_Information?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alex said:
Thanks John. Here is my SQL:

SELECT tbl_Information.Badge, tbl_Information.Name, Concatenate("SELECT
Comment from tbl_Information WHERE Badge=""" & [Badge] & """") AS Comments
FROM tbl_Information;

I tried your suggestions and I did get one record, but the comment field
said, "on-time".

:

I suspect it would help if you posted the entire SQL Statement. My best
guess without that is that you are returning multiple records for each
badge
number and then seeing the comments for each record.

I would expect to see a query that looks like the following.

SELECT DISTINCT Badge,
Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" &
[Badge]
& """ ORDER BY Comment") as Comments
FROM YourTable


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

I have three records with the same badge number and I would like to
combine
the text from all three comment fields into one field and get one
record,
like:

badge comment
1 good worker
1 on-time
1 skilled
2 skilled

to

1 good worker, on-time, skilled
2 skilled

I've looked at the Duane Hookoms website and found the below
expression.

Comments: Concatenate("SELECT Comment from tbl_Information WHERE
Badge="""
&
[Badge] & """")

However, it's not working. The query results still show four records:

badge comment
1 good worker
1 good worker
1 good worker
2 skilled

Any idea what I'm doing wrong? Thank you!
Click to expand...
Click to expand...
Click to expand...
 
John - thanks for hanging in there with me on this one. I don't know how on
earth it happened, but you're right; the comments were listed in more than
one table record and I didn't see them because of the small row height. I
must have created an update query somewhere along the line. Case closed.
Thank you again.

John Spencer said:
No you don't need to have two tables. Each comment should only be
listed ONE time for each time it is in the table, but if you have good
job in comment one and good job in a second record for the same badge
number, you will see good job twice. Try using Distinct in the
Concatenate query and see if that trims the comments for youl


SELECT DISTINCT tbl_Information.Badge, tbl_Information.Name
, Concatenate("SELECT DISTINCT Comment from tbl_Information WHERE
Badge=""" & [Badge]
& """") AS Comments
FROM tbl_Information;


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

SELECT [Badge], [Name], Concatenate("SELECT Comment from tbl_Information
WHERE Badge=""" & [Badge] & """ ORDER BY Comment") AS Comments
FROM tbl_Information

You were right, I made my row height taller and there were more comments in
the column. But, each comment was listed 3 or 4 times. I only have one
table, do I need to have 2 in order for this to work?

John Spencer said:
SELECT DISTINCT tbl_Information.Badge, tbl_Information.Name
, Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" & [Badge]
& """") AS Comments
FROM tbl_Information;

If you are looking at the results in the Datasheet view of a query, have you
made the column big enough (wider or taller) to show the additional data?

If you are looking the results on a form or a report have you made the
control large enough to show all the information being returned?

Beyond that I am stuck on why you are having a problem. Are the comments in
tbl_Information or do you have them in a separate table linked to
tbl_Information?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John. Here is my SQL:

SELECT tbl_Information.Badge, tbl_Information.Name, Concatenate("SELECT
Comment from tbl_Information WHERE Badge=""" & [Badge] & """") AS Comments
FROM tbl_Information;

I tried your suggestions and I did get one record, but the comment field
said, "on-time".

:

I suspect it would help if you posted the entire SQL Statement. My best
guess without that is that you are returning multiple records for each
badge
number and then seeing the comments for each record.

I would expect to see a query that looks like the following.

SELECT DISTINCT Badge,
Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" &
[Badge]
& """ ORDER BY Comment") as Comments
FROM YourTable


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

I have three records with the same badge number and I would like to
combine
the text from all three comment fields into one field and get one
record,
like:

badge comment
1 good worker
1 on-time
1 skilled
2 skilled

to

1 good worker, on-time, skilled
2 skilled

I've looked at the Duane Hookoms website and found the below
expression.

Comments: Concatenate("SELECT Comment from tbl_Information WHERE
Badge="""
&
[Badge] & """")

However, it's not working. The query results still show four records:

badge comment
1 good worker
1 good worker
1 good worker
2 skilled

Any idea what I'm doing wrong? Thank you!
Click to expand...
Click to expand...
Click to expand...
 
I tried to use this code and I get an error 3085 message that states:
"Undefined function <name> in expression. (Error 3085)". Can you help me to
resolve? I checked the references and I have the Visual Basic for
Applications, Microsoft Access 11.0 Object Library, OLE Automation, Microsoft
DAO 3.6 Object Library and the Microsoft ActiveX Data Objects 2.5 Library all
checked. Is there something that I am missing?

Thank you.

John Spencer said:
No you don't need to have two tables. Each comment should only be
listed ONE time for each time it is in the table, but if you have good
job in comment one and good job in a second record for the same badge
number, you will see good job twice. Try using Distinct in the
Concatenate query and see if that trims the comments for youl


SELECT DISTINCT tbl_Information.Badge, tbl_Information.Name
, Concatenate("SELECT DISTINCT Comment from tbl_Information WHERE
Badge=""" & [Badge]
& """") AS Comments
FROM tbl_Information;


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

SELECT [Badge], [Name], Concatenate("SELECT Comment from tbl_Information
WHERE Badge=""" & [Badge] & """ ORDER BY Comment") AS Comments
FROM tbl_Information

You were right, I made my row height taller and there were more comments in
the column. But, each comment was listed 3 or 4 times. I only have one
table, do I need to have 2 in order for this to work?

John Spencer said:
SELECT DISTINCT tbl_Information.Badge, tbl_Information.Name
, Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" & [Badge]
& """") AS Comments
FROM tbl_Information;

If you are looking at the results in the Datasheet view of a query, have you
made the column big enough (wider or taller) to show the additional data?

If you are looking the results on a form or a report have you made the
control large enough to show all the information being returned?

Beyond that I am stuck on why you are having a problem. Are the comments in
tbl_Information or do you have them in a separate table linked to
tbl_Information?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John. Here is my SQL:

SELECT tbl_Information.Badge, tbl_Information.Name, Concatenate("SELECT
Comment from tbl_Information WHERE Badge=""" & [Badge] & """") AS Comments
FROM tbl_Information;

I tried your suggestions and I did get one record, but the comment field
said, "on-time".

:

I suspect it would help if you posted the entire SQL Statement. My best
guess without that is that you are returning multiple records for each
badge
number and then seeing the comments for each record.

I would expect to see a query that looks like the following.

SELECT DISTINCT Badge,
Concatenate("SELECT Comment from tbl_Information WHERE Badge=""" &
[Badge]
& """ ORDER BY Comment") as Comments
FROM YourTable


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

I have three records with the same badge number and I would like to
combine
the text from all three comment fields into one field and get one
record,
like:

badge comment
1 good worker
1 on-time
1 skilled
2 skilled

to

1 good worker, on-time, skilled
2 skilled

I've looked at the Duane Hookoms website and found the below
expression.

Comments: Concatenate("SELECT Comment from tbl_Information WHERE
Badge="""
&
[Badge] & """")

However, it's not working. The query results still show four records:

badge comment
1 good worker
1 good worker
1 good worker
2 skilled

Any idea what I'm doing wrong? Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Did you find and download the code for the concatenate function?
Did you name the module differently than the function? You should.

Concatenate function is at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane


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

Ned Lottsa Help said:
I tried to use this code and I get an error 3085 message that states:
"Undefined function <name> in expression. (Error 3085)". Can you help me
to
resolve? I checked the references and I have the Visual Basic for
Applications, Microsoft Access 11.0 Object Library, OLE Automation,
Microsoft
DAO 3.6 Object Library and the Microsoft ActiveX Data Objects 2.5 Library
all
checked. Is there something that I am missing?

Thank you.
Click to expand...

SNIP
 
Hi John,

I found and loaded the module to my DB and it is still not quite working.
What I am trying to do is concatenate a group of criteria identifiers to
customer records. One customer record could have multiple criteria
identifiers. Example:

Criteria # Met Customer Record #
1 0001234
2 0001234
5 0001234
3 0002345
7 0002345
9 0002345

Here is how I want the records to appear:

Criteria # Met Customer Record #
1,2,5 0001234
3,7,9 0002345

The example from Duane's access database for concatenation seems to refer to
names as part of the example, but i need to concatenate numbers to numbers.
Is there a way to be able to do this? Thanks again for your help.
 
The function should work fine with numbers. I would expect your query
string too look something like the following.

SELECT DISTINCT Concatenate("SELECT [Criteria # Met] FROM
[YourTableName] WHERE [Customer Record #] = """ & [customer Record 3] &
"""",",") as CriteriaMet
, [Customer Record #]
FROM [YourTableName]


In the grid:
Field: CriteriaMet: Concatenate("SELECT [Criteria # Met] FROM
[YourTableName] WHERE [Customer Record #] = """ & [customer Record 3] &
"""",",")

-- In query properties set Unique Fields to Yes.



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

I tried to execute the query exactly as you described, but when the table
pulls up i am getting an error (error # 3464) message in the criteria # met
field for each line that there is a customer record. Example to follow:

Criteria # Met Customer Record #
ERROR# 0001234
ERROR# 0002345
ERROR# 0003456

Not sure what I am doing wrong, any suggestions?

John Spencer said:
The function should work fine with numbers. I would expect your query
string too look something like the following.

SELECT DISTINCT Concatenate("SELECT [Criteria # Met] FROM
[YourTableName] WHERE [Customer Record #] = """ & [customer Record 3] &
"""",",") as CriteriaMet
, [Customer Record #]
FROM [YourTableName]


In the grid:
Field: CriteriaMet: Concatenate("SELECT [Criteria # Met] FROM
[YourTableName] WHERE [Customer Record #] = """ & [customer Record 3] &
"""",",")

-- In query properties set Unique Fields to Yes.



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

Hi John,

I found and loaded the module to my DB and it is still not quite working.
What I am trying to do is concatenate a group of criteria identifiers to
customer records. One customer record could have multiple criteria
identifiers. Example:

Criteria # Met Customer Record #
1 0001234
2 0001234
5 0001234
3 0002345
7 0002345
9 0002345

Here is how I want the records to appear:

Criteria # Met Customer Record #
1,2,5 0001234
3,7,9 0002345

The example from Duane's access database for concatenation seems to refer to
names as part of the example, but i need to concatenate numbers to numbers.
Is there a way to be able to do this? Thanks again for your help.
Click to expand...
Click to expand...
 
Additionally, when I hit the debug button, the highlighted area of code is as
follows (i don't know if this will help, but here goes):

Set rs = db.OpenRecordset(pstrSQL)

Ned Lottsa Help said:
Hi John,

I tried to execute the query exactly as you described, but when the table
pulls up i am getting an error (error # 3464) message in the criteria # met
field for each line that there is a customer record. Example to follow:

Criteria # Met Customer Record #
ERROR# 0001234
ERROR# 0002345
ERROR# 0003456

Not sure what I am doing wrong, any suggestions?

John Spencer said:
The function should work fine with numbers. I would expect your query
string too look something like the following.

SELECT DISTINCT Concatenate("SELECT [Criteria # Met] FROM
[YourTableName] WHERE [Customer Record #] = """ & [customer Record 3] &
"""",",") as CriteriaMet
, [Customer Record #]
FROM [YourTableName]


In the grid:
Field: CriteriaMet: Concatenate("SELECT [Criteria # Met] FROM
[YourTableName] WHERE [Customer Record #] = """ & [customer Record 3] &
"""",",")

-- In query properties set Unique Fields to Yes.



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

Hi John,

I found and loaded the module to my DB and it is still not quite working.
What I am trying to do is concatenate a group of criteria identifiers to
customer records. One customer record could have multiple criteria
identifiers. Example:

Criteria # Met Customer Record #
1 0001234
2 0001234
5 0001234
3 0002345
7 0002345
9 0002345

Here is how I want the records to appear:

Criteria # Met Customer Record #
1,2,5 0001234
3,7,9 0002345

The example from Duane's access database for concatenation seems to refer to
names as part of the example, but i need to concatenate numbers to numbers.
Is there a way to be able to do this? Thanks again for your help.

:

Did you find and download the code for the concatenate function?
Did you name the module differently than the function? You should.

Concatenate function is at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane


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

I tried to use this code and I get an error 3085 message that states:
"Undefined function <name> in expression. (Error 3085)". Can you help me
to
resolve? I checked the references and I have the Visual Basic for
Applications, Microsoft Access 11.0 Object Library, OLE Automation,
Microsoft
DAO 3.6 Object Library and the Microsoft ActiveX Data Objects 2.5 Library
all
checked. Is there something that I am missing?

Thank you.

SNIP
Click to expand...
Click to expand...
Click to expand...
 
Did you look at the comments in the code? If you are using an .mdb you
need to change the code a bit versus if you are using an adp.

The way to comment out code is to put an apostrophe at th beginning of a
line. The way to activate the code is to remove the apostrophe.

I believe that there are two places in Duane's code that you need to
make a change.

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

Additionally, when I hit the debug button, the highlighted area of code is as
follows (i don't know if this will help, but here goes):

Set rs = db.OpenRecordset(pstrSQL)

Ned Lottsa Help said:
Hi John,

I tried to execute the query exactly as you described, but when the table
pulls up i am getting an error (error # 3464) message in the criteria # met
field for each line that there is a customer record. Example to follow:

Criteria # Met Customer Record #
ERROR# 0001234
ERROR# 0002345
ERROR# 0003456

Not sure what I am doing wrong, any suggestions?

John Spencer said:
The function should work fine with numbers. I would expect your query
string too look something like the following.

SELECT DISTINCT Concatenate("SELECT [Criteria # Met] FROM
[YourTableName] WHERE [Customer Record #] = """ & [customer Record 3] &
"""",",") as CriteriaMet
, [Customer Record #]
FROM [YourTableName]


In the grid:
Field: CriteriaMet: Concatenate("SELECT [Criteria # Met] FROM
[YourTableName] WHERE [Customer Record #] = """ & [customer Record 3] &
"""",",")

-- In query properties set Unique Fields to Yes.



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


Ned Lottsa Help wrote:
Hi John,

I found and loaded the module to my DB and it is still not quite working.
What I am trying to do is concatenate a group of criteria identifiers to
customer records. One customer record could have multiple criteria
identifiers. Example:

Criteria # Met Customer Record #
1 0001234
2 0001234
5 0001234
3 0002345
7 0002345
9 0002345

Here is how I want the records to appear:

Criteria # Met Customer Record #
1,2,5 0001234
3,7,9 0002345

The example from Duane's access database for concatenation seems to refer to
names as part of the example, but i need to concatenate numbers to numbers.
Is there a way to be able to do this? Thanks again for your help.

:

Did you find and download the code for the concatenate function?
Did you name the module differently than the function? You should.

Concatenate function is at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane


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

I tried to use this code and I get an error 3085 message that states:
"Undefined function <name> in expression. (Error 3085)". Can you help me
to
resolve? I checked the references and I have the Visual Basic for
Applications, Microsoft Access 11.0 Object Library, OLE Automation,
Microsoft
DAO 3.6 Object Library and the Microsoft ActiveX Data Objects 2.5 Library
all
checked. Is there something that I am missing?

Thank you.

SNIP
Click to expand...
Click to expand...
Click to expand...
 
Hi John, I finally got the code to work. I had to take out some of the
quotations and I used Duane's sample database to guide me through. I did go
back as you suggested and marked out the code. Thank you so much for your
help.

John Spencer said:
Did you look at the comments in the code? If you are using an .mdb you
need to change the code a bit versus if you are using an adp.

The way to comment out code is to put an apostrophe at th beginning of a
line. The way to activate the code is to remove the apostrophe.

I believe that there are two places in Duane's code that you need to
make a change.

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

Additionally, when I hit the debug button, the highlighted area of code is as
follows (i don't know if this will help, but here goes):

Set rs = db.OpenRecordset(pstrSQL)

Ned Lottsa Help said:
Hi John,

I tried to execute the query exactly as you described, but when the table
pulls up i am getting an error (error # 3464) message in the criteria # met
field for each line that there is a customer record. Example to follow:

Criteria # Met Customer Record #
ERROR# 0001234
ERROR# 0002345
ERROR# 0003456

Not sure what I am doing wrong, any suggestions?

:

The function should work fine with numbers. I would expect your query
string too look something like the following.

SELECT DISTINCT Concatenate("SELECT [Criteria # Met] FROM
[YourTableName] WHERE [Customer Record #] = """ & [customer Record 3] &
"""",",") as CriteriaMet
, [Customer Record #]
FROM [YourTableName]


In the grid:
Field: CriteriaMet: Concatenate("SELECT [Criteria # Met] FROM
[YourTableName] WHERE [Customer Record #] = """ & [customer Record 3] &
"""",",")

-- In query properties set Unique Fields to Yes.



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


Ned Lottsa Help wrote:
Hi John,

I found and loaded the module to my DB and it is still not quite working.
What I am trying to do is concatenate a group of criteria identifiers to
customer records. One customer record could have multiple criteria
identifiers. Example:

Criteria # Met Customer Record #
1 0001234
2 0001234
5 0001234
3 0002345
7 0002345
9 0002345

Here is how I want the records to appear:

Criteria # Met Customer Record #
1,2,5 0001234
3,7,9 0002345

The example from Duane's access database for concatenation seems to refer to
names as part of the example, but i need to concatenate numbers to numbers.
Is there a way to be able to do this? Thanks again for your help.

:

Did you find and download the code for the concatenate function?
Did you name the module differently than the function? You should.

Concatenate function is at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane


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

I tried to use this code and I get an error 3085 message that states:
"Undefined function <name> in expression. (Error 3085)". Can you help me
to
resolve? I checked the references and I have the Visual Basic for
Applications, Microsoft Access 11.0 Object Library, OLE Automation,
Microsoft
DAO 3.6 Object Library and the Microsoft ActiveX Data Objects 2.5 Library
all
checked. Is there something that I am missing?

Thank you.

SNIP
Click to expand...
Click to expand...
Click to expand...
 
Back
Top