several report fields from one table field

I have two fields [number] which is a contact number and
[type] which is Cell, Home... I have a report where I
want to list each person's home number and cell number
but I want everything on one line not grouped by
employee, thus I need two fields. Can I write a two
queries similar to these BRIO queries which will create
the two fields for my report?

homephoneCASE WHEN [type]= "home" THEN [number] ELSE ""
END)

cellphone: (CASE WHEN [type]= "cell" THEN [number]ELSE ""
END)

Thanks for the help!

On Mon, 24 May 2004 11:56:05 -0700, "Steve"
wrote:

I have two fields [number] which is a contact number and
[type] which is Cell, Home... I have a report where I
want to list each person's home number and cell number
but I want everything on one line not grouped by
employee, thus I need two fields. Can I write a two
queries similar to these BRIO queries which will create
the two fields for my report?

homephoneCASE WHEN [type]= "home" THEN [number] ELSE ""
END)

cellphone: (CASE WHEN [type]= "cell" THEN [number]ELSE ""
END)

Thanks for the help!

The Access analog would use the IIF() function. In a Query design grid
vacant field cell type

Homephone: IIF([Type] = "home", [Number], "")

etc.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Thanks John, Is there a method that does not use the IIF
function? Everything I read says to keep away from it
since it is so slow. My report will take 7 of these type
of fields so maybe speed will not be a factor. I'll try
it out immediately.

I notice you are a major contributor to the forum and I
appreciate your generosity.