Query help needed please... 1

[pmaxon]

I am working with a database which contains among other fields a name, email address 1 and email address 2. Some rows do not have anything in the email address 1 or 2 field, some have info in the 1 field and some have info in the 1 and 2 field. I need a query that will produce a seperate row in the output with name and email 1 and name and email 2 if they have two email address. I am unable to get this to work. I am producing a csv file to import into my mailing program for a club newsletter. Any suggestions?

TIA

 

[lupins46]

Select * from mytable where not isnull([email address 1])
Union
Select * from mytable where not isnull([email address 2])

Paste this into the SQL view of a new query; chnage mytable to your own table name and make sure the email fields have the correct names.

 

[pmaxon]

GENIUS! Thanks, I'll give that a try when I get home from work.

 

[pmaxon]

Well, I had a copy of the db here at work so I tried it. What that produced was one row if there was data in email1 AND email2. What I am after is actuall 2 rows, one containing email1 and the other containing email2.

 

[lupins46]

Ok, yes I see.

Select field1, field2......, [email address 1]as EM
from mytable where not isnull([email address 1])
Union
Select field1, field2......, [email address 2]as EM from mytable where not isnull([email address 2])

You must exclude address2 in the first select and exclude address1 from the second select. But you will have to list all the fields you want in each select statement (and they must be the same fields in the same order)

 

[pmaxon]

There was a farmer who had a dog... and BINGO was his name-O!
B-I-N-G-O

That worked! Thank you so very much!