Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm building a database of spiders, organizing them by name, country, and region within that country.

The problem is that many of the spiders exist in multiple countries and regions within that country.

I want to be able to sort spiders by either country, or region, without having to enter a duplicate entry for every country or region that spider exists in.

For example: Say Spider A exists in the USA and Australia. In the USA it lives in the South and North West. In Australia it lives in New South Wales.

Is there a way I can make one entry for this spider, but tag it so it will show up when I search for spiders in USA>South or Australia>New South Wales?

share|improve this question

1 Answer 1

You want to record many-to-many associations between spiders and locations. If you were using a database, you would have a separate table to record the associations:

Spider table

Name        Web/Hunter   Nocturnal   ...
Spider A
Spider B
Spider C

Location table

Name        Latitude   Annual rainfall   ...
Location 1
Location 2
Location 3

Spider/Location table

Spider    Location
Spider A  Location 2
Spider A  Location 3
Spider B  Location 1
Spider B  Location 3
Spider C  Location 2

A database system, such as Access, has the necessary functionality to record this type of information and to specify a report of spiders by locations and another report of locations by spider.

It is possible to use a spreadsheet system, such as Excel, to hold this type of information. But it is not playing to the strengths of a spreadsheet so it is more difficult and less reliable.

You may know Excel and not Access. However, I would be surprised if you did not find it easier to learn Access and use that than try to implement this in Excel.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.