Concatenate - 255 character limit

  • Thread starter Thread starter Ted
  • Start date Start date
T

Ted

I'm bumping up against the 255 character limit for concatenating with
a totals query.

I've got a table (tblGlebe) with the weekly events, names and times
for my swimming club. I've got to build a report (for the local
newspaper) showing each event, and the respective name & times
(field=swimmer, data already concatenated) of each club member. The
format of the report is that the names and times for each event appear
as in a single paragraph (rather than a long column) to the right of
the event name - hence concatenation.

So, I built a totals query (see below) that groups and concatentates
the swimmer field by event. When I run the report, I just group on the
event.

I'm using Duane Hookum's "Concatenate" function and this works nicely
(thank's Duane) except for my 255 character limit. That is, the
concatenated swimmer field cuts out at 255 characters.

SELECT event, Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder]) AS [Names]
FROM tblGlebe
GROUP BY event, Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder]);

Suggestions please.
There's probably a much easier way around this, but my brain's gone
numb.

Regards

Ted
 
Ted-

Why are you using a Totals query? You need to do that only if you also need
one of the aggregate functions - Count, Min, Max, Avg, Sum, StDev, Var - and
you're not using any of those. Group By tends to build an internal index to
do the grouping, and fields in an index can't be any longer than 255
characters. It's the Group By that's truncating your output - get rid of
it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
As John suggested, try to do without the Group By. If you can't, you can
try:
SELECT event, First(Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder])) AS [Names]
FROM tblGlebe
GROUP BY event;
Or, create a query that has only unique event values. Then use this query
with the Concatenate function.
 
You're right, the concatenation query is fine without "grouping".

Maybe, my problem is really with printing (& not queries) because the
reason I use "group by" in the query is so that when the report runs,
there is only one record per event.

For example:
without grouping (one results line per swimmer per event)
50FS Bell 34:35, Smith 36:95, Brown 45:73
50FS Bell 34:35, Smith 36:95, Brown 45:73
50FS Bell 34:35, Smith 36:95, Brown 45:73

with grouping (one results line per event)
50FS Bell 34:35, Smith 36:95, Brown 45:73


Thoughts?

Ted

Duane Hookom said:
As John suggested, try to do without the Group By. If you can't, you can
try:
SELECT event, First(Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder])) AS [Names]
FROM tblGlebe
GROUP BY event;
Or, create a query that has only unique event values. Then use this query
with the Concatenate function.


--
Duane Hookom
MS Access MVP


Ted said:
I'm bumping up against the 255 character limit for concatenating with
a totals query.

I've got a table (tblGlebe) with the weekly events, names and times
for my swimming club. I've got to build a report (for the local
newspaper) showing each event, and the respective name & times
(field=swimmer, data already concatenated) of each club member. The
format of the report is that the names and times for each event appear
as in a single paragraph (rather than a long column) to the right of
the event name - hence concatenation.

So, I built a totals query (see below) that groups and concatentates
the swimmer field by event. When I run the report, I just group on the
event.

I'm using Duane Hookum's "Concatenate" function and this works nicely
(thank's Duane) except for my 255 character limit. That is, the
concatenated swimmer field cuts out at 255 characters.

SELECT event, Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder]) AS [Names]
FROM tblGlebe
GROUP BY event, Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder]);

Suggestions please.
There's probably a much easier way around this, but my brain's gone
numb.

Regards

Ted
Click to expand...
Click to expand...
 
Create a group by query first:
==qgrpEvent=============
SELECT event
FROM tblGlebe
GROUP BY event;
==================
Or from other table with only one record per event. Then create a query:

SELECT event, Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder]) AS [Names]
FROM qgrpEvent;

--
Duane Hookom
MS Access MVP


Ted said:
You're right, the concatenation query is fine without "grouping".

Maybe, my problem is really with printing (& not queries) because the
reason I use "group by" in the query is so that when the report runs,
there is only one record per event.

For example:
without grouping (one results line per swimmer per event)
50FS Bell 34:35, Smith 36:95, Brown 45:73
50FS Bell 34:35, Smith 36:95, Brown 45:73
50FS Bell 34:35, Smith 36:95, Brown 45:73

with grouping (one results line per event)
50FS Bell 34:35, Smith 36:95, Brown 45:73


Thoughts?

Ted

"Duane Hookom" <[email protected]> wrote in message
As John suggested, try to do without the Group By. If you can't, you can
try:
SELECT event, First(Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder])) AS [Names]
FROM tblGlebe
GROUP BY event;
Or, create a query that has only unique event values. Then use this query
with the Concatenate function.


--
Duane Hookom
MS Access MVP


Ted said:
I'm bumping up against the 255 character limit for concatenating with
a totals query.

I've got a table (tblGlebe) with the weekly events, names and times
for my swimming club. I've got to build a report (for the local
newspaper) showing each event, and the respective name & times
(field=swimmer, data already concatenated) of each club member. The
format of the report is that the names and times for each event appear
as in a single paragraph (rather than a long column) to the right of
the event name - hence concatenation.

So, I built a totals query (see below) that groups and concatentates
the swimmer field by event. When I run the report, I just group on the
event.

I'm using Duane Hookum's "Concatenate" function and this works nicely
(thank's Duane) except for my 255 character limit. That is, the
concatenated swimmer field cuts out at 255 characters.

SELECT event, Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder]) AS [Names]
FROM tblGlebe
GROUP BY event, Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder]);

Suggestions please.
There's probably a much easier way around this, but my brain's gone
numb.

Regards

Ted
Click to expand...
Click to expand...
Click to expand...
 
Tah, nah!!!
Works a treat. Many thanks.
Ted

Duane Hookom said:
Create a group by query first:
==qgrpEvent=============
SELECT event
FROM tblGlebe
GROUP BY event;
==================
Or from other table with only one record per event. Then create a query:

SELECT event, Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder]) AS [Names]
FROM qgrpEvent;

--
Duane Hookom
MS Access MVP


Ted said:
You're right, the concatenation query is fine without "grouping".

Maybe, my problem is really with printing (& not queries) because the
reason I use "group by" in the query is so that when the report runs,
there is only one record per event.

For example:
without grouping (one results line per swimmer per event)
50FS Bell 34:35, Smith 36:95, Brown 45:73
50FS Bell 34:35, Smith 36:95, Brown 45:73
50FS Bell 34:35, Smith 36:95, Brown 45:73

with grouping (one results line per event)
50FS Bell 34:35, Smith 36:95, Brown 45:73


Thoughts?

Ted

"Duane Hookom" <[email protected]> wrote in message
As John suggested, try to do without the Group By. If you can't, you can
try:
SELECT event, First(Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder])) AS [Names]
FROM tblGlebe
GROUP BY event;
Or, create a query that has only unique event values. Then use this query
with the Concatenate function.


--
Duane Hookom
MS Access MVP


I'm bumping up against the 255 character limit for concatenating with
a totals query.

I've got a table (tblGlebe) with the weekly events, names and times
for my swimming club. I've got to build a report (for the local
newspaper) showing each event, and the respective name & times
(field=swimmer, data already concatenated) of each club member. The
format of the report is that the names and times for each event appear
as in a single paragraph (rather than a long column) to the right of
the event name - hence concatenation.

So, I built a totals query (see below) that groups and concatentates
the swimmer field by event. When I run the report, I just group on the
event.

I'm using Duane Hookum's "Concatenate" function and this works nicely
(thank's Duane) except for my 255 character limit. That is, the
concatenated swimmer field cuts out at 255 characters.

SELECT event, Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder]) AS [Names]
FROM tblGlebe
GROUP BY event, Concatenate("SELECT Swimmer FROM tblGlebe WHERE
eventorder =" & [eventorder]);

Suggestions please.
There's probably a much easier way around this, but my brain's gone
numb.

Regards

Ted
Click to expand...
Click to expand...
Click to expand...
 
Back
Top