Sorting information 1

[whisperinangel]

We have a survey with about 160 participants, where the participants were asked to rank 20 different items into their top ten concerns. We have input their contact information along with their top ten into an Access table under "Topic 1", Topic 2", etc. What we would like to be able to do is do a query to find out their concerns and the number of each concern in each topic, number one through number ten. I figured using Access would be easier than Excel because of the query function, however I am unsure of the best way to do this, or even if it is possible.
Thank you in advance for any advice!

 

[rac2]

Under no circumstances is Access, or any relational database, easier to use than a spreadsheet.


From the description of this data, I would say you need three tables, Panelists, Issues, TopTenConcerns with colums like these

Panelists( participant_id, name, donor_level, bank_account_number, etc )

Issues ( issue_id, statement_of_issue )

TopTenConcerns ( participant_id, issue_id, rank )


To find out the concerns of Joe

SELECT statement_of_issue, rank
FROM Panelists
JOIN TopTenConcerns
     ON TopTenConcerns.participant_id = Panelists.participant_id
JOIN Issues
     ON Issues.issue_id = TopTenConcerns.issue_id
WHERE name = 'Joe'

The number of each concern in each topic -

SELECT statement_of_issue, COUNT(*)
FROM TopTenConcerns
JOIN Issues
     ON Issues.issue_id = TopTenConcerns.issue_id
GROUP BY statement_of_issue

Good luck.

 

[dhookom]

rac2 deserves a star for posting both an observation and a great alternative.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]