Conditional query in access 2003

[mosquito_admin]

Hi all !


I have a access 2003 DB with one table, for ex. which contains the info about the users and their PCs like this:
| name | PC1 | PC2 | PC3 |
| Jack |Asus |Lenovo| HP |
|John | DELL | | |
|Anna | |Asus | |

Now I need a sql query code that return this info but distincted, like this:

| name | PC |
| Jack | Asus |
| |Lenovo|
| | HP |
|John | DELL |
|Anna | Asus |

or in the bad case like this:

| name | PC |
| Jack | Asus |
| Jack |Lenovo|
| Jack | HP |
|John | DELL |
|Anna | Asus |

Can anyone help me to do that ?
Please !!!

[pbaldy]

Hopefully you're fixing the original design, which is flawed. You can use a UNION query:

SELECT Name, PC1
FROM TableName
WHERE PC1 Is Not Null
UNION ALL
SELECT Name, PC2
FROM TableName
WHERE PC2 Is Not Null
...

That will repeat the name, but you can get the look you want on a report.

[mosquito_admin]

My idea was to create separated queries for each PC and then to concatenate its. But your idea and solution are better.

Thank you very much pbaldy !

[pbaldy]

Happy to help, and welcome to the site by the way!