query to summarize top ranking data

[CMR]

I have the following data. It lists a bunch of bird species (genus and species), the country they came from, the number of individuals from that country, the number of individuals from all countries, and finally the proportion (from the two previous columns) of individuals that came from that country (see 1st table).

I'd like to summarize information from it. Ultimately, I'd create a table that would list the genus and species, the top 3 countries for each genus and species, and the proportion that came from each country. I having a difficult time working out how to create code for a query(ies) that will do what I'd like it to, and if it can be done. In a perfect world, the data would be arranged to look like the 2nd table.



Any suggestions would be most appreciated.

[June7]

Rearranging records to horizontal (left to right) structure can be difficult.

Review:
http://forums.aspfree.com/microsoft-...ry-322123.html
http://allenbrowne.com/func-concat.html