combine notes in separate rows...

[Kenneth J. Pino]

Hello,

The best way to describe my problem is with an example...

We have a table like such...

Client Date Notes
1 1/1/08 Interested in More widgets for special project
2 1/2/08 spoke to Mike, project beginning next monght...
fyi - Likes Dogs
1 1/5/08 Wants More Widgets for Ohio - possible upsell
opt.
3 1/4/08 Called and left message about new polly - will
followup
2 1/3/08 got message from George - interested in polly
options


We want it to look like such...

Client Notes
1 1/1/08 Interested in More widgets for special project
1/5/08 Wants More Widgets for Ohio - possible upsell opt.
2 1/2/08 spoke to Mike, project beginning next monght...
fyi - Likes Dogs 1/3/08 got message from George - interested in polly
options
3 1/4/08 Called and left message about new polly - will
followup

Realizing that it is better to store it the first way I can only offer this
explanation...

We are converting to a system that for some reason has one big notes field
and we need to get all our notes from the old system into a big block per
client to plop them in the notes field in the new system...

I'd even be willing to lose the 'date' field if I could just get the notes
into a big block per client... but I'm stumped - I can't find a way to do
this and I think that is because I can't find a reason anyone would want
to... except for the people who wrote our new package...

ideas?

Thanks,
KJ

 

[Kenneth J. Pino]

Sorry - lines cut wired in first post - here it is easier to read

Hello,

The best way to describe my problem is with an example...

We have a table like such...

Client Date Notes
1 1/1/08 Interested in More widgets for special project
2 1/2/08 spoke to Mike, project beginning next monght...
fyi - Likes Dogs
1 1/5/08 Wants More Widgets for Ohio - possible upsell
opt.
3 1/4/08 Called and left message about new polly - will
followup
2 1/3/08 got message from George - interested in polly
options


We want it to look like such...

Client Notes
1 1/1/08 Interested in More widgets for special project
1/5/08 Wants More Widgets for Ohio - possible upsell opt.
2 1/2/08 spoke to Mike, project beginning next monght...
fyi - Likes Dogs 1/3/08 got message from George - interested in polly
options
3 1/4/08 Called and left message about new polly - will
followup

Realizing that it is better to store it the first way I can only offer this
explanation...

We are converting to a system that for some reason has one big notes field
and we need to get all our notes from the old system into a big block per
client to plop them in the notes field in the new system...

I'd even be willing to lose the 'date' field if I could just get the notes
into a big block per client... but I'm stumped - I can't find a way to do
this and I think that is because I can't find a reason anyone would want
to... except for the people who wrote our new package...

ideas?

Thanks,
KJ

 

[John Spencer (MVP)]

You are correct the present system is a better design then the new system as
far as this goes.

Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with both ADO
and DAO. There are comments regarding which lines to comment or uncomment
based on which library you prefer. Access 97 is mostly DAO while the default
for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16
You can download the example file and import the Concatenate function from
Duane's sample. You will need to modify it if your data is in an MDB. Read
the code for which lines to comment in and comment out.
--

You could use something like the following

SELECT Distinct Client
, Concatenate("SELECT [Date] & ' ' & Notes FROM YourTable WHERE Client = " &
Y.Client & " ORDER BY [Date]",Chr(13) & Chr(10)) as Notes
FROM YourTable as Y

For efficiency you might want to use a subquery in the from clause

SELECT Client
, Concat("SELECT [Date] & ' ' & Notes FROM YourTable WHERE Client = " &
Y.Client & " ORDER BY [Date]",Chr(13) & Chr(10)) as Notes
FROM (SELECT Distinct Client FROM YourTable) as Y


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Kenneth J. Pino]

Great - thanks!
I am going thru it right now - looks like it will do the trick...

An odd trick, but the trick none the less...

KJ

Just my two penny worth.

I've uses Duane's function for some years now to concatenate comments from
different teachers on the same students. Works like a dream.

Howard

You are correct the present system is a better design then the new system
as far as this goes.

Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with
both ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO
while the default for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16
You can download the example file and import the Concatenate function
from Duane's sample. You will need to modify it if your data is in an
MDB. Read the code for which lines to comment in and comment out.
--

You could use something like the following

SELECT Distinct Client
, Concatenate("SELECT [Date] & ' ' & Notes FROM YourTable WHERE Client =
" & Y.Client & " ORDER BY [Date]",Chr(13) & Chr(10)) as Notes
FROM YourTable as Y

For efficiency you might want to use a subquery in the from clause

SELECT Client
, Concat("SELECT [Date] & ' ' & Notes FROM YourTable WHERE Client = " &
Y.Client & " ORDER BY [Date]",Chr(13) & Chr(10)) as Notes
FROM (SELECT Distinct Client FROM YourTable) as Y


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,

The best way to describe my problem is with an example...

We have a table like such...

Client Date Notes
1 1/1/08 Interested in More widgets for special
project
2 1/2/08 spoke to Mike, project beginning next
monght... fyi - Likes Dogs
1 1/5/08 Wants More Widgets for Ohio - possible upsell
opt.
3 1/4/08 Called and left message about new polly -
will followup
2 1/3/08 got message from George - interested in polly
options


We want it to look like such...

Client Notes
1 1/1/08 Interested in More widgets for special
project 1/5/08 Wants More Widgets for Ohio - possible upsell opt.
2 1/2/08 spoke to Mike, project beginning next
monght... fyi - Likes Dogs 1/3/08 got message from George -
interested in polly options
3 1/4/08 Called and left message about new polly -
will followup

Realizing that it is better to store it the first way I can only offer
this explanation...

We are converting to a system that for some reason has one big notes
field and we need to get all our notes from the old system into a big
block per client to plop them in the notes field in the new system...

I'd even be willing to lose the 'date' field if I could just get the
notes into a big block per client... but I'm stumped - I can't find a
way to do this and I think that is because I can't find a reason anyone
would want to... except for the people who wrote our new package...

ideas?

Thanks,
KJ