Query to return same record more than once

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Morrison - 18 Sep 2004 16:31 GMT

I have a table that holds customer contact information. The table has
three email fields (Email1, Email2, and Email3). So each customer can
have up to 3 contact email addresses.

I need a query that will return the customers name with each of their
email addresses.

ie If customer has 3 email addresses then following is returned:

Bill Morrison Email1
Bill Morrison Email2
Bill Morrison Email3

Hope this makes sense

Thanks

Reply to this Message

MGFoster - 18 Sep 2004 18:50 GMT

It would be better to put the email addresses in a separate table. This
will Normalize your db. E.g.:

Customers CustEmails
- ----------- ---------------
CustID 1-->m CustID,
<other fields> Email

This way you can have many emails per customer.

The query would be like this:

SELECT C.FirstName & " " & C.LastName, E.Email
FROM Customers As C INNER JOIN CustEmails As E
ON C.CustID = E.CustID
WHERE <criteria>

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> I have a table that holds customer contact information. The table has
> three email fields (Email1, Email2, and Email3). So each customer can
[quoted text clipped - 8 lines]
> Bill Morrison Email2
> Bill Morrison Email3

Reply to this Message