e-mail list in Access 2000

[Kungen]

Hi!

I have a column with e-mail addresses. Some of the rows are NULL.
What can I do to retreive all the addresses in one single line,
separated with semicolons?
I know how to use calculated fields (like this + ' ; ' + that etc) but
how do I write a query that "knows" how many addresses there are, omits
NULL values and puts it all in one nice row.

I've managed to do this in MS SQL-Server 2005 using a "stored
procedure". Stored procedures are not supported in Access 2000 as far
as I understand it, though. (Does anybody know if it is supported in
later versions of MS Access?)

Kungen

 

[John Vinson]

I have a column with e-mail addresses. Some of the rows are NULL.
What can I do to retreive all the addresses in one single line,
separated with semicolons?

See http://www.mvps.org/access/modules/mdl0004.htm for sample VBA code
(what you use in Access for similar purposes to SQL Stored
Procedures).

John W. Vinson[MVP]

 

[Kungen]

Thanks a lot.
I think, though, that I'm just a little too little knowledgeble to
quite understand how I can use this in my case as described below.
The "1:M relationship" parts of the code confuses me because I don't
need it and I don't know what parts of it all to use and not. I tried
to experiment with it, putting it in my context, but I only ended up
with error messages.

I would highly appreciate help as to write a query that just does this:

I have a (one single) column with e-mail addresses. Some of the rows
are NULL.
What can I do to retreive all the addresses in one single line,
separated with semicolons?

Regards
Kungen

 

[John Vinson]

I have a (one single) column with e-mail addresses. Some of the rows
are NULL.

Then this table is the "many" side. Are you trying to send all the
emails in your entire table in one message?? or is there anything in
common among the addresses you're sending?

You can use a criterion of IS NOT NULL to filter out the NULL
addresses.


John W. Vinson[MVP]

 

[Kungen]

Thanks John, I really appreciate you trying to help me.
Yes, the goal is to get all the e-mail addresses in a single row with
semicolons in between, in order to use in an e-mail client.

I think what I need is help actually writing this snippet of script,
cause I just dont get how to use the information on the page you linked
to. I get the idea with fConcatChild, I just don't succeed in using it
in my context.

John, or somebody else feeling like being my hero of the day?

Thanks
Kungen

 

[John Vinson]

I think what I need is help actually writing this snippet of script,
cause I just dont get how to use the information on the page you linked
to. I get the idea with fConcatChild, I just don't succeed in using it
in my context.

Well... I'd be willing to try to help, but I don't KNOW your context.
What's the structure and relationship of your tables - or is it just
one table? Do you have any criteria or do you just want every record
in the table concatenated? What's the name of the table, what's the
name of the field?

John W. Vinson[MVP]

 

[Kungen]

Hello, and thanks a lot for being patient!

As I wrote before:
"I have a column with e-mail addresses. Some of the rows are NULL.
What can I do to retreive all the addresses in one single line,
separated with semicolons?"

This means I have only one table, and only one column of data to be
retrieved. No relations whatsoever. Just this single column who's
content I need to get in a single row, separated by semicolons.

Thanks again!

Kungen