About an Access Query

[G.Kumaraguru]

Folks Please Help me with this

I have an Access database and have a query return data like this...

______________________________________________________________________

SID	Skill		PersonCode		PersonName
______________________________________________________________________

1	Management	412			Bill Gates
2	Programming	412			Bill Gates
3	Martial Arts	412			Bill Gates
4	Programming	413			Steve Jobs
5	Dancing		413			Steve Jobs
6	Management	414			Suzuki samako
7	MartialArts	414			Suzuki samako


What i would like to have is a query that would return this..


______________________________________________________________________

SID	Skill		PersonCode		PersonName
______________________________________________________________________

1	Management	412			Bill Gates
4	Programming	413			Steve Jobs
6	Management	414			Suzuki samako


That is.. only their Primary skills alone, HOw do i do it ?

[ernmeister]

How do you know which is their primary skill?

[PaulLewis]

Not Enough info to be exact for you.

I HOPE you don't have all of that in one single table because if you do then you need to re-design your database.

If you have three tables to store this data then you are probably using a table for People, a table for Skills, and a table for the join.

However since you mention "Primary skill" I have a feeling you are using one tables - which is not a good idea.

Perhaps if you reply back, I or someone else can help. We need to know the tables and structure in place. Also, the SQL statement for the query would be useful as well.

Regards
Paul Lewis

[G.Kumaraguru]

The Thing here is that i have to have a query that queries
this query and produce the desired output. I have no access
to the original tables.

The skills appear ordered in this pattern 'Primary skill
first' 'secondary next and so on..'
The smallest SID number for a Personcode corresponds to
the primary skill the next smallest is the secondary skill
and so on..

The Format of the query output i showed you is analogous to the original confidential material.

If u could help me with the SQL statement for the new
query that queries the existing query, it would be really
helpful.

[G.Kumaraguru]

WOW..IT WORKED>>>YEH !!

IT WORKED LIKE A CHARM...

Thanks SASCHA. I Was Very Ignorant of the keyword 'First'
that made the difference.

Also, Thanks to PAUL LEWIS.. for his Interest.
Paul's code though visually appealing failed to produce
the desired result.

[PaulLewis]

Oh dear.

Well, Visual appeal has to count for something I only really added the order by clause so that you could be sure that the selected records were in the order you wished.

While I am surprised the SQL didn't work for you, I think that if you were to add the ORDER BY clause visually in Access by selecting a sort order in the query grid, you will improve the reliability of your query.

The FIRST keyword retrieves the first row that the database engine comes across. It is not in any way going to do any sorting for you (i.e. first doen's mean first alphabetically or anything).

Regards

i assume that you are quering an allready
sorted query, this is why i think you don't
have to sort your query, but to be on the
safe side you can extend the query to this
(and then even paul might be happy ;-) ).

SELECT
First(qry.SID) AS SID,
First(qry.Skill) AS Skill,
PersonCode,
PersonName
FROM
qry
GROUP BY
PersonCode,
PersonName
ORDER BY
First(qry.SID),
tblPersonSkills.PersonCode

cheers (and i will realy have 1+ beers now)

Sascha