Here's a query I'm trying to build up for a report.

I have a person's table with their names and 4 different phone number Fields (Day, Eve, Mobile, Fax).  A row might contain data in any of those 4 fields or
might not.  I might have multiple rows where different people have the same number.

I need to generate a query that lets me do a phone number that comes from each of those columns (if not null or blank) and sorted by phone number, then
by name.  The report itself, which would then do the grouping by people per phone number, would show something like:

555-555-1111
 John Doe
 Kid Doe
 Mary Doe
555-555-1112
 Jim Dane
555-555-1113
 Jerry Lewis

Suggestions welcome.

Note:

If you have other suggestions, I'd welcome them.  I ended up creating a memory table and selecting the data using one of the fields into the table and then
doing it again for each additional phone.  From there, I can do what I need.

Lance

Lance Ras wrote:

Here's a query I'm trying to build up for a report.

I have a person's table with their names and 4 different phone number Fields (Day, Eve, Mobile, Fax).  A row might contain data in any of those 4 fields or
might not.  I might have multiple rows where different people have the same number.

I need to generate a query that lets me do a phone number that comes from each of those columns (if not null or blank) and sorted by phone number, then
by name.  The report itself, which would then do the grouping by people per phone number, would show something like:

555-555-1111
 John Doe
 Kid Doe
 Mary Doe
555-555-1112
 Jim Dane
555-555-1113
 Jerry Lewis

Suggestions welcome.

Lance,

I think this will do what you want.

SELECT PhoneHome As Phone, FullName
  FROM Contact   
  WHERE PhoneHome is not null
UNION   
SELECT PhoneWork As Phone, FullName
  FROM Contact   
  WHERE PhoneWork is not null
UNION
SELECT Fax AS Phone, FullName
  FROM Contact
  WHERE Fax is not null
UNION
SELECT Mobile AS Phone, FullName
  FROM Contact
  WHERE Mobile is not null
ORDER BY Phone, FullName

Richard Harding

Thanks Richard,

I haven't really messed with Unions, so this makes a great excuse to do so.  Was a good excercise to learn about temp tables too!

Lance


Richard Harding wrote:

Lance,

I think this will do what you want.

SELECT PhoneHome As Phone, FullName
  FROM Contact   
  WHERE PhoneHome is not null
UNION   
SELECT PhoneWork As Phone, FullName
  FROM Contact   
  WHERE PhoneWork is not null
UNION
SELECT Fax AS Phone, FullName
  FROM Contact
  WHERE Fax is not null
UNION
SELECT Mobile AS Phone, FullName
  FROM Contact
  WHERE Mobile is not null
ORDER BY Phone, FullName

Richard Harding