Sorting question (by column)

[joave]

Hi all:


I have a table with let's say multiple sports as each heading. If the
person likes a certain sport, the answer would be 1 (or yes), it not the
answer would be 2 (or no) and if the person has never seen or heard of it the
answer would be 3.

I need to make a SQL query that tells me which sports were liked the best
and weren't liked at all by say, the Top 5. So here is the table:

Name Soccer Baseball Skiing Waterboarding Football Basketball

Dave 1 2 1 3
1 2
Scott 2 2 2 2
1 3
Holly 3 2 2 1
3 2


I made a query using the Sum function that tells me how many of each
answer there were (Yes, No, Never Tried). Therefore the Query returns this
for number of no's: Soccer 1 Baseball 3 Skiing 2 Waterboarding 1 Football 0
Basketball 2

I need to place those in order and only get the Top 3 results. Can you
suggest how to do this please?

Thank you,

Dave

 

[Duane Hookom]

I'm not sure exactly what you are looking for but I believe the best start
would be to normalize your table with a union query.
SELECT NameField, "Soccer" as Sport, [Soccer] As Response
FROM tblMultipleSports
UNION ALL
SELECT NameField, "Baseball", [Baseball]
FROM tblMultipleSport
UNION ALL
SELECT NameField, "Skiing", [Skiing]
FROM tblMultipleSport
-- etc --

You can then create a totals query to get what you want.

 

[joave]

Hi Duane,

Yeah, that is where I thought I would need to go. This was the answer -
thanks for your help!

Dave

I'm not sure exactly what you are looking for but I believe the best start
would be to normalize your table with a union query.
SELECT NameField, "Soccer" as Sport, [Soccer] As Response
FROM tblMultipleSports
UNION ALL
SELECT NameField, "Baseball", [Baseball]
FROM tblMultipleSport
UNION ALL
SELECT NameField, "Skiing", [Skiing]
FROM tblMultipleSport
-- etc --

You can then create a totals query to get what you want.

--
Duane Hookom
Microsoft Access MVP

Hi all:


I have a table with let's say multiple sports as each heading. If the
person likes a certain sport, the answer would be 1 (or yes), it not the
answer would be 2 (or no) and if the person has never seen or heard of it the
answer would be 3.

I need to make a SQL query that tells me which sports were liked the best
and weren't liked at all by say, the Top 5. So here is the table:

Name Soccer Baseball Skiing Waterboarding Football Basketball

Dave 1 2 1 3
1 2
Scott 2 2 2 2
1 3
Holly 3 2 2 1
3 2


I made a query using the Sum function that tells me how many of each
answer there were (Yes, No, Never Tried). Therefore the Query returns this
for number of no's: Soccer 1 Baseball 3 Skiing 2 Waterboarding 1 Football 0
Basketball 2

I need to place those in order and only get the Top 3 results. Can you
suggest how to do this please?

Thank you,

Dave