Concatenate - 255 character limit

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
 
J

John Viescas

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
 
D

Duane Hookom

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.
 
T

Ted

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...
 
D

Duane Hookom

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...
 
T

Ted

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

character limits 4
Concatenate one-to-many into one row 1
Query Exceeds the limit of data for a single record 7
How can I change 255 character limit in 'hyperlink' function? 0
Top n query using Allen Browne's "ConcatRelated" 3
beyond the 255 character limit 4
Exceed textbox 255 character limit 2
Concatenate many to one 5

Top