2 Replies Latest reply on Jul 19, 2010 8:49 AM by misterg65

    Combining Excel Data in FMPro



      Combining Excel Data in FMPro


      As part of my job I have to analyse exam data. I get my information from several different sources,all with their own formats.

      I have general data on the students, common across the school (Special Needs, etc).

      Almost all students will complete a common exam (data obtained from the exam board).

      In the following year these would then be split into new groups and in one of three categories. Some may drop science, some may do one course ("CourseA") whilst others do another course ("CourseB"). The results from both these courses again comes from the exam board.

      I need an elegant way to combine these so that candidate number 1004 for instance gets updated with all the information. When I tried this before I got a new record for "1004" for each sheet I imported.

      I've tried Bento, but I can't work out what to do there, either :(

      I've looked at Access... but that's an ugly beast!

      Thanks in advance

        • 1. Re: Combining Excel Data in FMPro

          Basically, you need to learn about how to design a relational database. Each "entity" requires a table, with records for instances. Links need to be made between logically related records.

          Your "candidate number" may be the unique primary ID of the "candidate" table. It would be used as a foreign key in other tables (where relevant). More or less the same for "courses", they'd have their own unique primary ID. 

          There would be at least one join table, to record unique combinations of two foreign keys; CandidateID & CourseID for example. It would have at least the two ids, and likely a date and/or year, and whatever other relevant fields.

          This are not "FileMaker specific" requirements, they apply to any relationship database. One of the best books for beginners about general design, "Database Design for Mere Mortals," was written by Michael J. Hernandez, an Access programer. It does not matter, basic structural design is much the same for everyone.

          Generally (with specific exceptions) a particular piece of data (such as names, etc.) exists in only one place. It is then visiable, via relationships, from any other place with a logical connection.

          Yes, most of the above seems vague. But, to us, your description of what you need to do is vague. There is not enough information to give you advice, which is why your post has not been answered yet.

          Once the structure is set up, the data would need to be imported into the correct tables, with ids populated correctly. Then all data for a particular candidate could be made visible. 

          My advice is to slow down. Do not try and explain what is certainly going to be a multiple table solution in just a few short paragraphs. Give us more details, while trying to remain on track.

          You need to first set up the tables and relationships for the main objects. Once you grasp the way structure is built, you will see that the general principles apply to other sets of data also.

          P.S. It is particularly difficult sometimes to explain educational database entities. The words "group", "course" and "class" for example can be used in different ways.

          • 2. Re: Combining Excel Data in FMPro

            Thanks for the advice. It is obvious now that I was right! A database is more what I require ... it's now down to the choice between running Access via Parallels or Filemaker natively. The advantage with Access is I already have it - no extra outlay required. The disadvantage is that it is Access :)