Re: Concatenation

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I thought I suggested you get rid of distinct back on Aug 25. Did you try
the Group By SQL that I proposed?


--
Duane Hookom
MS Access MVP

"JKarchner" <JKarchner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B60F1CCA-BF83-4334-A071-08390F5967DC@xxxxxxxxxxxxxxxx
Sorry for taking so long to try out your suggestion. I tried what you
said
and it works perfectly fine. However i think i know what my problem is.
I
use DISTINCT to ensure that only one of each result is returned (when i do
not use it i get multiple results), and i had read that the results are
forced to 255 character limit when DISTINCT is used. Is there a way
around
this? Or are there other things i can try? Thank you very much for all
of
your help so far.

"Duane Hookom" wrote:

You would need to open the debug window and insert
?Concatenate("SELECT GlobalAffiliation & Chr(9) FROM
qryAI2006GlobalAffiliations WHERE Supplier_ID =xxx",Chr(13) & Chr(10))

The above should all be on one line with a real Supplier_ID value in
place
of xxx.


--
Duane Hookom
MS Access MVP

"JKarchner" <JKarchner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:77F1FA49-3C53-49C6-83BE-29230E407422@xxxxxxxxxxxxxxxx
Yes it is truncated after 255 characters. Would you like me to copy
the
concatenation function into the debug window? Just do that and run it
or
do
i have to insert data or anything?

"Duane Hookom" wrote:

Is the last line being truncated at a specific number of characters
each
time? Is the truncation detectable in the query data*** view? Have
you
tried entering the function in the debug window?


--
Duane Hookom
MS Access MVP

"JKarchner" <JKarchner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D4D9D409-54B1-46C3-8789-7AD9AB75AF57@xxxxxxxxxxxxxxxx
Neither of those suggestions work. With the first one, because
DISTINCT
is
not there, duplicates are returned. Each time records are
concatenated
together there is a duplicate. So if it happens 5 times there are 5
duplicate records. With the second, i still have the problem of
losing
the
final line in the record.

"Duane Hookom" wrote:

Try get rid of the DISTINCT
SELECT qryAI2006GlobalAffiliations.Supplier_ID,
qryAI2006GlobalAffiliations.Supplier,
qryAI2006GlobalAffiliations.Report_ID,
qryAI2006GlobalAffiliations.Report, Concatenate("SELECT
qryAI2006GlobalAffiliations.GlobalAffiliation & Chr(9) FROM
qryAI2006GlobalAffiliations WHERE
qryAI2006GlobalAffiliations.Supplier_ID
="
& [qryAI2006GlobalAffiliations.Supplier_ID],Chr(13) & Chr(10)) AS
GlobalAffiliations
FROM qryAI2006GlobalAffiliations
ORDER BY qryAI2006GlobalAffiliations.Supplier;

or

SELECT qryAI2006GlobalAffiliations.Supplier_ID,
qryAI2006GlobalAffiliations.Supplier,
qryAI2006GlobalAffiliations.Report_ID,
qryAI2006GlobalAffiliations.Report, First(Concatenate("SELECT
qryAI2006GlobalAffiliations.GlobalAffiliation & Chr(9) FROM
qryAI2006GlobalAffiliations WHERE
qryAI2006GlobalAffiliations.Supplier_ID
="
& [qryAI2006GlobalAffiliations.Supplier_ID],Chr(13) & Chr(10))) AS
GlobalAffiliations
FROM qryAI2006GlobalAffiliations
GROUP BY qryAI2006GlobalAffiliations.Supplier_ID,
qryAI2006GlobalAffiliations.Supplier,
qryAI2006GlobalAffiliations.Report_ID,
qryAI2006GlobalAffiliations.Report
ORDER BY qryAI2006GlobalAffiliations.Supplier;


--
Duane Hookom
MS Access MVP



"JKarchner" <JKarchner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6D935510-E5F4-40B5-903B-E3F51D9DFAE2@xxxxxxxxxxxxxxxx
Query:
SELECT DISTINCT qryAI2006GlobalAffiliations.Supplier_ID,
qryAI2006GlobalAffiliations.Supplier,
qryAI2006GlobalAffiliations.Report_ID,
qryAI2006GlobalAffiliations.Report, Concatenate("SELECT
qryAI2006GlobalAffiliations.GlobalAffiliation & Chr(9) FROM
qryAI2006GlobalAffiliations WHERE
qryAI2006GlobalAffiliations.Supplier_ID
="
& [qryAI2006GlobalAffiliations.Supplier_ID],Chr(13) & Chr(10)) AS
GlobalAffiliations
FROM qryAI2006GlobalAffiliations
ORDER BY qryAI2006GlobalAffiliations.Supplier;

Results for the concatenated column for one row:
GlobalAffiliations
------------------------------
SHB Automotive Module
Luk-Fahrzeug-Mydraulik Sanden
Toyo-Behr Japanese Components
Behr-Toyo Engine Cooling Systems
Shanghai Behr Thermal Systems
Dongfeng Behr Thermal Systems
HBPO (Hella Behr and Plastic Omnium)
Behr-Hella Thermocontrol
Sh


Desired Results from same:
GlobalAffiliations
--------------------------------------------
SHB Automotive Module
Luk-Fahrzeug-Mydraulik Sanden
Toyo-Behr Japanese Components
Behr-Toyo Engine Cooling Systems
Shanghai Behr Thermal Systems
Dongfeng Behr Thermal Systems
HBPO (Hella Behr and Plastic Omnium)
Behr-Hella Thermocontrol
Shanghai Sanden Behr Automotive Air Conditioning

if you notice only the last line of the record is different.
thank you for any help you can provide.

"Duane Hookom" wrote:

Please provide your syntax, results, and desired results.

--
Duane Hookom
MS Access MVP

"JKarchner" <JKarchner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:A97FD39C-8E6D-43E0-A162-7588082E0686@xxxxxxxxxxxxxxxx
I am having a problem with using Duane Hookom's concatenation
method
for
combining records and displaying them on separate lines. It
appears
that
too
many characters have been used and the full data isnt
displayed.
The
type
for the field that becomes concatenated is memo. Can anyone
help?














.

Quantcast