Concatenation

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

Guest

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?
 
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.
 
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 said:
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 said:
Please provide your syntax, results, and desired results.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
Please provide your syntax, results, and desired results.

--
Duane Hookom
MS Access MVP

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?
Click to expand...
Click to expand...
Click to expand...
 
Is the last line being truncated at a specific number of characters each
time? Is the truncation detectable in the query datasheet view? Have you
tried entering the function in the debug window?


--
Duane Hookom
MS Access MVP

JKarchner said:
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 said:
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 said:
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.

:

Please provide your syntax, results, and desired results.

--
Duane Hookom
MS Access MVP

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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Is the last line being truncated at a specific number of characters each
time? Is the truncation detectable in the query datasheet view? Have you
tried entering the function in the debug window?


--
Duane Hookom
MS Access MVP

JKarchner said:
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 said:
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



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.

:

Please provide your syntax, results, and desired results.

--
Duane Hookom
MS Access MVP

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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
Is the last line being truncated at a specific number of characters each
time? Is the truncation detectable in the query datasheet view? Have you
tried entering the function in the debug window?


--
Duane Hookom
MS Access MVP

JKarchner said:
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.

:

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



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.

:

Please provide your syntax, results, and desired results.

--
Duane Hookom
MS Access MVP

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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
Is the last line being truncated at a specific number of characters each
time? Is the truncation detectable in the query datasheet view? Have you
tried entering the function in the debug window?


--
Duane Hookom
MS Access MVP

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.

:

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



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.

:

Please provide your syntax, results, and desired results.

--
Duane Hookom
MS Access MVP

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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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?

:

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


--
Duane Hookom
MS Access MVP

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.

:

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



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.

:

Please provide your syntax, results, and desired results.

--
Duane Hookom
MS Access MVP

message
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?
Click to expand...
Click to expand...
Click to expand...
 
yes i did try that. when i used the GROUP BY i experience the same problem.
when i use the first query, it returns duplicate results.

Duane Hookom said:
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 said:
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 said:
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

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?

:

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


--
Duane Hookom
MS Access MVP

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.

:

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



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.

:

Please provide your syntax, results, and desired results.

--
Duane Hookom
MS Access MVP

message
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?
Click to expand...
Click to expand...
Click to expand...
 
If the group by returns duplicate results, remove one or more fields/columns
until you don't have duplicates.

--
Duane Hookom
MS Access MVP

JKarchner said:
yes i did try that. when i used the GROUP BY i experience the same
problem.
when i use the first query, it returns duplicate results.

Duane Hookom said:
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 said:
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.

:

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

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?

:

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


--
Duane Hookom
MS Access MVP

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.

:

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



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

:

Please provide your syntax, results, and desired results.

--
Duane Hookom
MS Access MVP

message
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?
Click to expand...
Click to expand...
Click to expand...
 
Im sorry i should have clarified. when i use GROUP BY, the results are still
truncated. when i use the first query you suggested (listed below) i still
get duplicate results.

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;

Thank you very much for all of your help thus far. And thank you in advance
for any additional help you can provide

Duane Hookom said:
If the group by returns duplicate results, remove one or more fields/columns
until you don't have duplicates.

--
Duane Hookom
MS Access MVP

JKarchner said:
yes i did try that. when i used the GROUP BY i experience the same
problem.
when i use the first query, it returns duplicate results.

Duane Hookom said:
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

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.

:

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

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?

:

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


--
Duane Hookom
MS Access MVP

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.

:

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



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

:

Please provide your syntax, results, and desired results.

--
Duane Hookom
MS Access MVP

message
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?
Click to expand...
Click to expand...
Click to expand...
 
You have told us you are getting duplicates. Tell us exactly which fields
you need unique values from.


--
Duane Hookom
MS Access MVP

JKarchner said:
Im sorry i should have clarified. when i use GROUP BY, the results are
still
truncated. when i use the first query you suggested (listed below) i
still
get duplicate results.

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;

Thank you very much for all of your help thus far. And thank you in
advance
for any additional help you can provide

Duane Hookom said:
If the group by returns duplicate results, remove one or more
fields/columns
until you don't have duplicates.

--
Duane Hookom
MS Access MVP

JKarchner said:
yes i did try that. when i used the GROUP BY i experience the same
problem.
when i use the first query, it returns duplicate results.

:

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

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.

:

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

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?

:

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


--
Duane Hookom
MS Access MVP

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

:

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



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

:

Please provide your syntax, results, and desired results.

--
Duane Hookom
MS Access MVP

message
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?
Click to expand...
Click to expand...
Click to expand...
 
The only field that needs to be unique is the Supplier_ID field. After
concatenating them, there should only be one instance of each Supplier_ID.

Duane Hookom said:
You have told us you are getting duplicates. Tell us exactly which fields
you need unique values from.


--
Duane Hookom
MS Access MVP

JKarchner said:
Im sorry i should have clarified. when i use GROUP BY, the results are
still
truncated. when i use the first query you suggested (listed below) i
still
get duplicate results.

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;

Thank you very much for all of your help thus far. And thank you in
advance
for any additional help you can provide

Duane Hookom said:
If the group by returns duplicate results, remove one or more
fields/columns
until you don't have duplicates.

--
Duane Hookom
MS Access MVP

yes i did try that. when i used the GROUP BY i experience the same
problem.
when i use the first query, it returns duplicate results.

:

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

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.

:

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

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?

:

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


--
Duane Hookom
MS Access MVP

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

:

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



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

:

Please provide your syntax, results, and desired results.

--
Duane Hookom
MS Access MVP

message
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?
Click to expand...
Click to expand...
Click to expand...
 
Create a query [qgrpSuppliers] like:

SELECT Supplier_ID
FROM qryAI2006GlobalAffiliations
GROUP BY Supplier_ID;

Then create another query like:
SELECT Supplier_ID,
Concatenate("SELECT GlobalAffiliation & Chr(9)
FROM qryAI2006GlobalAffiliations
WHERE Supplier_ID =" & [Supplier_ID],Chr(13) & Chr(10))
AS GlobalAffiliations
FROM qgrpSuppliers
ORDER BY Supplier;

--
Duane Hookom
MS Access MVP


JKarchner said:
The only field that needs to be unique is the Supplier_ID field. After
concatenating them, there should only be one instance of each Supplier_ID.

Duane Hookom said:
You have told us you are getting duplicates. Tell us exactly which fields
you need unique values from.


--
Duane Hookom
MS Access MVP

JKarchner said:
Im sorry i should have clarified. when i use GROUP BY, the results are
still
truncated. when i use the first query you suggested (listed below) i
still
get duplicate results.

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;

Thank you very much for all of your help thus far. And thank you in
advance
for any additional help you can provide

:

If the group by returns duplicate results, remove one or more
fields/columns
until you don't have duplicates.

--
Duane Hookom
MS Access MVP

yes i did try that. when i used the GROUP BY i experience the same
problem.
when i use the first query, it returns duplicate results.

:

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

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.

:

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

message
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?

:

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


--
Duane Hookom
MS Access MVP

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

:

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



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

:

Please provide your syntax, results, and desired
results.

--
Duane Hookom
MS Access MVP

in
message
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?
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much for all of your help. Your final suggestion worked
perfectly. Again thank you very much.

Duane Hookom said:
Create a query [qgrpSuppliers] like:

SELECT Supplier_ID
FROM qryAI2006GlobalAffiliations
GROUP BY Supplier_ID;

Then create another query like:
SELECT Supplier_ID,
Concatenate("SELECT GlobalAffiliation & Chr(9)
FROM qryAI2006GlobalAffiliations
WHERE Supplier_ID =" & [Supplier_ID],Chr(13) & Chr(10))
AS GlobalAffiliations
FROM qgrpSuppliers
ORDER BY Supplier;

--
Duane Hookom
MS Access MVP


JKarchner said:
The only field that needs to be unique is the Supplier_ID field. After
concatenating them, there should only be one instance of each Supplier_ID.

Duane Hookom said:
You have told us you are getting duplicates. Tell us exactly which fields
you need unique values from.


--
Duane Hookom
MS Access MVP

Im sorry i should have clarified. when i use GROUP BY, the results are
still
truncated. when i use the first query you suggested (listed below) i
still
get duplicate results.

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;

Thank you very much for all of your help thus far. And thank you in
advance
for any additional help you can provide

:

If the group by returns duplicate results, remove one or more
fields/columns
until you don't have duplicates.

--
Duane Hookom
MS Access MVP

yes i did try that. when i used the GROUP BY i experience the same
problem.
when i use the first query, it returns duplicate results.

:

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

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.

:

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

message
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?

:

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


--
Duane Hookom
MS Access MVP

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

:

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



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

:

Please provide your syntax, results, and desired
results.

--
Duane Hookom
MS Access MVP

in
message
I am having a problem with using Duane Hookom's
concatenation
method
for
combining records and displaying them on separate
lines.
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

Concatenate function from a Query, not a table 5
Concatenate with SQL - Question for Michel Walsh 3
Concatenate 1
Concatenate Question 2
character limits 4
Concatenate Problems 3
Force data type from Text to Memo in a simple Make Table Query 1
Concatenate, data type mismatch error 2

Back
Top