How do I combine multiple records?

  • Thread starter Thread starter Keir
  • Start date Start date
K

Keir

Let's say I have a table with one field and 50 records.
How do I create a query that combines the content of all
the records from my table in one record field?

I have tried Microsoft Knowledge Base Article 318642 and
it works but not properly. It does not retrieve all the
content. Say my table had in total 1000 characters over 50
records. The query result might show 800. It instructs you
to try it on the Northwind database. I did excatly as
instructed but if you look at the query result the string
is incomplete (not all records present and last word in
string is incomplete)
 
If you put SQL query string in new query it pulls out all
the records. But, using function below it only pulls out
first 270 - 300 characters. It is as if it gets so to
about 270-300 characters and then stops even though more
records exist.

This appears to be an error or restriction. Can you help?
Or suggest an alternative way

Keir

-----Original Message-----
Modify the code as follows
If IsMissing(strDelimiter) Then strDelimiter = "; "
strSQL = "SELECT [" & strFieldNameIn & "] FROM [" & strTblQryIn & "]"
strSQL = strSQL & " WHERE [" & strLinkChildFieldNameIn & "] = [ParamIn]"
qd.SQL = strSQL
debug.Print strSQL
stop 'or place a break point
qd.Parameters("ParamIn").Value = varPKVvalue
This will stop your code. Press [Ctrl]+[G] to view the debug window. Copy
the sql from the debug window and paste it into the SQL view of a blank
query. View the query datasheet to see how many records you have.

--
Duane Hookom
MS Access MVP


Keir said:
Let's say I have a table with one field and 50 records.
How do I create a query that combines the content of all
the records from my table in one record field?

I have tried Microsoft Knowledge Base Article 318642 and
it works but not properly. It does not retrieve all the
content. Say my table had in total 1000 characters over 50
records. The query result might show 800. It instructs you
to try it on the Northwind database. I did excatly as
instructed but if you look at the query result the string
is incomplete (not all records present and last word in
string is incomplete)
Click to expand...


.
Click to expand...
 
If it stops at 256 characters than it is possible you are using words like
"DISTINCT" or "GROUP BY" in your query. It would help to know how you are
using this function.

--
Duane Hookom
MS Access MVP


Keir said:
If you put SQL query string in new query it pulls out all
the records. But, using function below it only pulls out
first 270 - 300 characters. It is as if it gets so to
about 270-300 characters and then stops even though more
records exist.

This appears to be an error or restriction. Can you help?
Or suggest an alternative way

Keir

-----Original Message-----
Modify the code as follows
If IsMissing(strDelimiter) Then strDelimiter = "; "
strSQL = "SELECT [" & strFieldNameIn & "] FROM [" & strTblQryIn & "]"
strSQL = strSQL & " WHERE [" & strLinkChildFieldNameIn & "] = [ParamIn]"
qd.SQL = strSQL
debug.Print strSQL
stop 'or place a break point
qd.Parameters("ParamIn").Value = varPKVvalue
This will stop your code. Press [Ctrl]+[G] to view the debug window. Copy
the sql from the debug window and paste it into the SQL view of a blank
query. View the query datasheet to see how many records you have.

--
Duane Hookom
MS Access MVP


Keir said:
Let's say I have a table with one field and 50 records.
How do I create a query that combines the content of all
the records from my table in one record field?

I have tried Microsoft Knowledge Base Article 318642 and
it works but not properly. It does not retrieve all the
content. Say my table had in total 1000 characters over 50
records. The query result might show 800. It instructs you
to try it on the Northwind database. I did excatly as
instructed but if you look at the query result the string
is incomplete (not all records present and last word in
string is incomplete)
Click to expand...


.
Click to expand...
Click to expand...
 
No not at all. I have just added a new post about this.
Search for 'Concatenate function limited to 255 chars?'

I simply call this function with the following:

SELECT fConcatenateRecords("productname","Table1",",") AS
Expr1;

Table1 contains copied/pasted records of products from
Northwinds

I'm really puzzled. I appreciate your help

Keir

-----Original Message-----
If it stops at 256 characters than it is possible you are using words like
"DISTINCT" or "GROUP BY" in your query. It would help to know how you are
using this function.

--
Duane Hookom
MS Access MVP


Keir said:
If you put SQL query string in new query it pulls out all
the records. But, using function below it only pulls out
first 270 - 300 characters. It is as if it gets so to
about 270-300 characters and then stops even though more
records exist.

This appears to be an error or restriction. Can you help?
Or suggest an alternative way

Keir

-----Original Message-----
Modify the code as follows
If IsMissing(strDelimiter) Then strDelimiter = "; "
strSQL = "SELECT [" & strFieldNameIn & "] FROM [" & strTblQryIn & "]"
strSQL = strSQL & " WHERE [" &
Click to expand...
strLinkChildFieldNameIn
& "] = [ParamIn]"
qd.SQL = strSQL
debug.Print strSQL
stop 'or place a break point
qd.Parameters("ParamIn").Value = varPKVvalue
This will stop your code. Press [Ctrl]+[G] to view the debug window. Copy
the sql from the debug window and paste it into the SQL view of a blank
query. View the query datasheet to see how many records you have.

--
Duane Hookom
MS Access MVP


Let's say I have a table with one field and 50 records.
How do I create a query that combines the content of all
the records from my table in one record field?

I have tried Microsoft Knowledge Base Article 318642 and
it works but not properly. It does not retrieve all the
content. Say my table had in total 1000 characters
Click to expand...
over
50
records. The query result might show 800. It
Click to expand...
instructs
you
to try it on the Northwind database. I did excatly as
instructed but if you look at the query result the string
is incomplete (not all records present and last word in
string is incomplete)


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top