4 Replies Latest reply on Apr 12, 2012 4:57 PM by RobertLockett

    Database design and reporting with LOTS of fields

    RobertLockett

      Title

      Database design and reporting with LOTS of fields

      Post

      This is not, I fear, a simple question.

      Here’s the situation: I’m a birdwatcher and like many birdwatchers, try to keep good notes about what I’ve seen. To help me on a recent trip to Australia, I created an FM11 database (see the screenshot). To account for the possible 643 species of birds I might see on a given day, I had to create 643 number fields, then 643 summary fields to count the total number of a particular species I’d seen by the end of the trip.

      Needless to say, this took a while. It’s also proved beyond my modest capabilities to create a printable daily report that would list just those species I saw that day, not all 643 possibilities. Can anyone suggest how I might do that? Is my approach wrong? Thanks in advance.

      daylist.jpg

        • 1. Re: Database design and reporting with LOTS of fields
          philmodjunk

          How about throw out your design and start over?

          I realize you don't want to discard the data, but the structure of your database really complicates what you do with the data once you get it all entered.

          A set up where you have one record for each day of birding, but with a separate related table where each record represents one species observed on that day makes your reporting needs much easier to pull. Please note that such a design can be done where you have those tables, but the screen and data entry process looks much like you have here if you need this specific layout.

          DaysBirding----<ObservedSpecies>-----Species

          DaysBirding::Date = ObservedSpecies::Date
          Species::SpeciesID = ObservedSpecies::SpeciesID

          • 2. Re: Database design and reporting with LOTS of fields
            RobertLockett

            Phil,

            Thanks for the very fast response. The fact is, I'm asking this question so that I can design a better database for the future; this one for Australia is pretty well done.

            I'm struggling to understand the structure that you're recommending, but I suspected that my approach was fundamentally wrong. My current DB does, indeed, use one record for each day of birding. But can you explain

            DaysBirding----<ObservedSpecies>-----Species

            DaysBirding::Date = ObservedSpecies::Date
            Species::SpeciesID = ObservedSpecies::SpeciesID

            at more length. Sorry, I know just enough FM to be dangerous.

            Regards,

            Robert

            • 3. Re: Database design and reporting with LOTS of fields
              philmodjunk

              DaysBirding----<ObservedSpecies>-----Species

              Is my text based "sketch" of what you'd produce in Manage | Database | Relationships. These are the table occurrence "boxes" for three tables needed in your relationship. -----< represents a one to many relationship, in other words one record on the --- end of the line connects to potentially many records in the table on the ---< end of the line.

              DaysBirding is essentially the table you have now but with the bird species "count" fields removed.

              ObservedSpecies represents one species observed on one day spent birding. A number field in this table counts the number of birds of that species observed on that day. A summary report based on this table can list all species observed with totals and sub totals to produce the report you wanted.

              Species is one record for each species of bird with a field for identifying the categories shown on your tabs.

              The relationship details I gave:

              DaysBirding::Date = ObservedSpecies::Date
              Species::SpeciesID = ObservedSpecies::SpeciesID

              Show which fields are used to match values in the relationships. You'd drag from Date in the DaysBirding table to Date in the ObservedSpecies table to create the relationship shown in the first relationship. Most likely, you'd also enable "allow creation of records via this relationship" for Observed species in this first relationship as this will facilitate simpler data entry for recording your birding observations.

              Portals to OvservedSpecies can be placed on a DaysBirding layout in order to record your observations. A value list of species based on the Species table can be used to select the species being observed.

              Sorry, I know just enough FM to be dangerous.

              Looks to me like you'll need to invest in some training or a book on FileMaker to gain a more complete understanding. I can sketch the general outline here for you, but there are a lot of details involved--more than I can fit into a single post.

              • 4. Re: Database design and reporting with LOTS of fields
                RobertLockett

                Phil,

                Please excuse the long absence. I should have responded to your detailed and helpful post immediately. Thank you for taking the time to explain the concepts. You are right, I need to dive deeper into FM to understand it better. Again, thanks for your help.