SLQ Query... Values seens as column names

Databases
1

I need to do a query which I suspect it is not that difficult but obvioulsy I’m not searching for the right terms because I can’t find the information anywhere in the world.

I’m using Access (because that’s what I’ve been asked to) and I have a table that with four fields. I need to select two of those and show the values of one of them as the name of the column.

Let me give you an example. These fields have the following structure:


jobPosition  WorkerName
Salesman    Andrew Smith
Manager     Susan Roberts
Director      James Stwart

The output I need is


Salesman                  Manager                  Director
Andrew Smith            Susan Roberts           James Stwart

Any thoughts? I would have thought that a TRANSFORM would do… except that you need to use aggregate functions and a file header (which I don’t want)

Thansk in advance.

edit: It may be good to add that this query will be used to combine this table with a second table which has more information.

One of the fields in this table I’ve shown is a foreign key. If I do a regular inner join, I would get three files for every record of that table… something that I dont’ want.

2

Unfortunately there is no TRANSFORM or PIVOT function which will do this. There is a good tutorial here http://www.artfulsoftware.com/infotree/queries.php#78

3

Your request has more to do with reports. Trying to bend queries to output formatted reporting results is usually counter productive.

I’d look for Access reporting and pivot/transpose: http://stackoverflow.com/questions/1501372/how-to-transpose-rows-and-columns-in-access-2003

4

[FONT=verdana]If you know in advance what all the job names are, and if there is relatively few of them, I suppose you could do something like this:
[SIZE=2]

SELECT 'Salesman", Worker FROM MyTable WHERE JobPosition = 'Saleman'
UNION SELECT 'Manger', Worker FROM MyTable WHERE JobPosition = 'Manager'
UNION SELECT 'Directory', Worker FROM MyTable WHERE JobPosition = 'Director'
....
etc.

[/SIZE]

But you probably want a more open-ended solution, in which case that won’t work. I just thought I’d throw it in.

Mike[/FONT]

5

It is a nice idea… 905% of the times I know the job positions and there are not too many… but there’s a 5% that doesn’t fit… and thats the issue.

@Imitica; Thanks. I’ll look into that. And you’re right. This is for a report… but it will not be seen as an access report but exported to Excel so other departments can manipulate it.

@rcashell; Thank you. That looks like a quite complete reference :slight_smile: