how do i? puzzled......!

[terryljsmith]

how do i run a query to select one item with many categories?

ie,

name | cat1 | cat2 | cat3 | cat4 |

tom | hat | gloves | tie | watch |

doug | shoes | shirt | | hat |


and i would like the query results to look like this -


name | cat? |

tom | hat |
| gloves|
| tie |

doug | shoes |
| shirt |
| hat |

so that if one field is blank (cat3 for doug) it will roll up to the
next line........

possible?

 

[Michel Walsh]

SELECT name, cat1 AS cat
FROM tableName
WHERE cat1 NOT IS NULL

UNION ALL

SELECT name, cat2
FROM tableName
WHERE cat2 NOT IS NULL

UNION ALL

SELECT name, cat3
FROM tableName
WHERE cat3 NOT IS NULL

UNION ALL

SELECT name, cat4
FROM tableName
WHERE cat4 NOT IS NULL



Hoping it may help,
Vanderghast, Access MVP