Redesign Query Results

[BU 12/02]

Hi,

After running my query I get the needed results. What I would like to do is have the information displayed differently, for example:
My query allows me to perform a search based on categories. For example, psychologists. However, this doctor might be a psychologist for family, children, married couples, etc. Instead of seeing the name of the doctor four times in a row b/c his/her several specialty, I would like to see it displayed in other columns. (ie John Smith (column 1 row 1) psychologist (column 2 row 1) Family (column 3 row 1) Children (column 4 row 1) married couples (column 5 row 1) instead of John Smith (column 1 row 1) psychologist (column 2 row 1) Family (column 3 row 1) and John Smith (column 1 row 2) psychologist (column 2 row 2) Family (column 3 row 2), etc. Sorry about the length of the explanation, hope you can understand what I mean.

Any way I can do this to the results of the existing query without redesigning my tables?

 

[AncientOne]

would this help?

http://support.microsoft.com/default.aspx?scid=kb;EN-US;322813

 

[crook]

You might try making a query similar to this:

field1: [name]
field2: iif([position] = "doctor", [position], null)
field3: iif([position] = "nurse", [position], null)
field4: iif([position] = "clerk", [position], null)

I use this method frequently when I need to take a vertical list, such as:
[name]-------[position]
John Doe-----doctor
John Doe-----clerk
Joan Smith---nurse

into a horizontal list, such as:
[name]-------[doctor]---[nurse]---[clerk]
John Doe-----doctor--------x--------clerk
Joan Smith-------x--------nurse-------x--

It does require that you have a static list of positions though, as you must create a field for each one.

Hope this helps,
- crook