Merge Comments / Wrap comments / Concatonate Comments

[Gary]

Okay I have a hard problem for anyone willing to take a stab at it.

I have a table on our mainframe which I loaded into Access.
Ultimately I have two fields Promotion number [12456] (Unique to the
promotion) and the Promotion comments. The comments are what have all the
detail about promotion. Example of Comments: giving customer ABC4567 a
discount of $20.00 or 20% for effective date of 1/1/2009 thru 12/31/2009.
The issue is that the date comes into Access like this:

Prom # Promo Comments Promotion line
12456 giving customer 1
12456 ABC4567 a 2
12456 discount of $20.00 3
12456 or 20% for 4
12456 effective date of 5
12456 1/1/2009 thru 6
12456 12/31/2009 7


I was able to write about 5 to 6 queries to get it to work but found out
where there are Promo # with more than 60 line items Access can not wrap the
data back into a sentence as it is to many records.

Looking for any help....

 

[Jerry Whittle]

I bet that one of the smart coders can figure out a nicer way, but here
goes....

First the field where you are storing the merged comments is a Memo field? A
text field will run out of space.

First I create a crosstab query like so called qryPromoXtab:

TRANSFORM First([Promo Comments] & " ") AS Expr1
SELECT tblProm.[Prom #]
FROM tblProm
GROUP BY tblProm.[Prom #]
ORDER BY tblProm.[Prom #]
PIVOT tblProm.[Promotion line];

Notice that I inserted a space like so:
([Promo Comments] & " ")

Next I looked for the largest line number. In your example it was 7.
Therefore the crosstab would create 7 Promo Comment fields. I then plugged
those 7 numerical field names into a select query based on the crosstab like
below. If you put in too many, you will get an error. If you have 60 fields,
you need 60 of them. The [] are imports as is using & instead of +.

SELECT qryPromoXtab.[Prom #], [1] & [2] & [3] & [4] & [5] & [6] & [7] AS
PromoComments
FROM qryPromoXtab;

This should merge everything and could be used as an insert query.

NOTE: I didn't test it out to 60 lines and I'm assuming (yes I know) that it
will work.

 

[MGFoster]

Okay I have a hard problem for anyone willing to take a stab at it.

I have a table on our mainframe which I loaded into Access.
Ultimately I have two fields Promotion number [12456] (Unique to the
promotion) and the Promotion comments. The comments are what have all the
detail about promotion. Example of Comments: giving customer ABC4567 a
discount of $20.00 or 20% for effective date of 1/1/2009 thru 12/31/2009.
The issue is that the date comes into Access like this:

Prom # Promo Comments Promotion line
12456 giving customer 1
12456 ABC4567 a 2
12456 discount of $20.00 3
12456 or 20% for 4
12456 effective date of 5
12456 1/1/2009 thru 6
12456 12/31/2009 7


I was able to write about 5 to 6 queries to get it to work but found out
where there are Promo # with more than 60 line items Access can not wrap the
data back into a sentence as it is to many records.

Looking for any help....

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll need a VBA function to concatenate the text from each row
(record). Here is a function that can do that:

http://www.mvps.org/access/modules/mdl0008.htm

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSd43F4echKqOuFEgEQI3QQCgmPHIcguPAg24eqOFcoNuSqFDZD8An2hS
Pgizg+9WVAdId7tyP/dRsIVE
=du+C
-----END PGP SIGNATURE-----

 

[Gary]

Jerry, What do I do if there are some instances where there are more than 60
fields to merge? Access can only do 60....

I bet that one of the smart coders can figure out a nicer way, but here
goes....

First the field where you are storing the merged comments is a Memo field? A
text field will run out of space.

First I create a crosstab query like so called qryPromoXtab:

TRANSFORM First([Promo Comments] & " ") AS Expr1
SELECT tblProm.[Prom #]
FROM tblProm
GROUP BY tblProm.[Prom #]
ORDER BY tblProm.[Prom #]
PIVOT tblProm.[Promotion line];

Notice that I inserted a space like so:
([Promo Comments] & " ")

Next I looked for the largest line number. In your example it was 7.
Therefore the crosstab would create 7 Promo Comment fields. I then plugged
those 7 numerical field names into a select query based on the crosstab like
below. If you put in too many, you will get an error. If you have 60 fields,
you need 60 of them. The [] are imports as is using & instead of +.

SELECT qryPromoXtab.[Prom #], [1] & [2] & [3] & [4] & [5] & [6] & [7] AS
PromoComments
FROM qryPromoXtab;

This should merge everything and could be used as an insert query.

NOTE: I didn't test it out to 60 lines and I'm assuming (yes I know) that it
will work.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Okay I have a hard problem for anyone willing to take a stab at it.

I have a table on our mainframe which I loaded into Access.
Ultimately I have two fields Promotion number [12456] (Unique to the
promotion) and the Promotion comments. The comments are what have all the
detail about promotion. Example of Comments: giving customer ABC4567 a
discount of $20.00 or 20% for effective date of 1/1/2009 thru 12/31/2009.
The issue is that the date comes into Access like this:

Prom # Promo Comments Promotion line
12456 giving customer 1
12456 ABC4567 a 2
12456 discount of $20.00 3
12456 or 20% for 4
12456 effective date of 5
12456 1/1/2009 thru 6
12456 12/31/2009 7


I was able to write about 5 to 6 queries to get it to work but found out
where there are Promo # with more than 60 line items Access can not wrap the
data back into a sentence as it is to many records.

Looking for any help....

 

[Gary]

Jerry, What do I do if I run into instances where thare are more than 60
fields to merge?

I bet that one of the smart coders can figure out a nicer way, but here
goes....

First the field where you are storing the merged comments is a Memo field? A
text field will run out of space.

First I create a crosstab query like so called qryPromoXtab:

TRANSFORM First([Promo Comments] & " ") AS Expr1
SELECT tblProm.[Prom #]
FROM tblProm
GROUP BY tblProm.[Prom #]
ORDER BY tblProm.[Prom #]
PIVOT tblProm.[Promotion line];

Notice that I inserted a space like so:
([Promo Comments] & " ")

Next I looked for the largest line number. In your example it was 7.
Therefore the crosstab would create 7 Promo Comment fields. I then plugged
those 7 numerical field names into a select query based on the crosstab like
below. If you put in too many, you will get an error. If you have 60 fields,
you need 60 of them. The [] are imports as is using & instead of +.

SELECT qryPromoXtab.[Prom #], [1] & [2] & [3] & [4] & [5] & [6] & [7] AS
PromoComments
FROM qryPromoXtab;

This should merge everything and could be used as an insert query.

NOTE: I didn't test it out to 60 lines and I'm assuming (yes I know) that it
will work.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Okay I have a hard problem for anyone willing to take a stab at it.

I have a table on our mainframe which I loaded into Access.
Ultimately I have two fields Promotion number [12456] (Unique to the
promotion) and the Promotion comments. The comments are what have all the
detail about promotion. Example of Comments: giving customer ABC4567 a
discount of $20.00 or 20% for effective date of 1/1/2009 thru 12/31/2009.
The issue is that the date comes into Access like this:

Prom # Promo Comments Promotion line
12456 giving customer 1
12456 ABC4567 a 2
12456 discount of $20.00 3
12456 or 20% for 4
12456 effective date of 5
12456 1/1/2009 thru 6
12456 12/31/2009 7


I was able to write about 5 to 6 queries to get it to work but found out
where there are Promo # with more than 60 line items Access can not wrap the
data back into a sentence as it is to many records.

Looking for any help....

 

[Gary]

MGFoster, I think I am going to use your URL to help me solve my problem.
First I have to start off by saying I have never used VBa before but after a
week now of going to the library and reading books I have learned that I need
to copy and paste the code you linked me to into a moduel in Access. Now that
I have learned that I need help in understanding how I get the code to work.
I truly will appreciate any help you have.

Regards,
Gary

Okay I have a hard problem for anyone willing to take a stab at it.

I have a table on our mainframe which I loaded into Access.
Ultimately I have two fields Promotion number [12456] (Unique to the
promotion) and the Promotion comments. The comments are what have all the
detail about promotion. Example of Comments: giving customer ABC4567 a
discount of $20.00 or 20% for effective date of 1/1/2009 thru 12/31/2009.
The issue is that the date comes into Access like this:

Prom # Promo Comments Promotion line
12456 giving customer 1
12456 ABC4567 a 2
12456 discount of $20.00 3
12456 or 20% for 4
12456 effective date of 5
12456 1/1/2009 thru 6
12456 12/31/2009 7


I was able to write about 5 to 6 queries to get it to work but found out
where there are Promo # with more than 60 line items Access can not wrap the
data back into a sentence as it is to many records.

Looking for any help....

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll need a VBA function to concatenate the text from each row
(record). Here is a function that can do that:

http://www.mvps.org/access/modules/mdl0008.htm

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSd43F4echKqOuFEgEQI3QQCgmPHIcguPAg24eqOFcoNuSqFDZD8An2hS
Pgizg+9WVAdId7tyP/dRsIVE
=du+C
-----END PGP SIGNATURE-----