5 Replies Latest reply on Mar 31, 2010 12:03 PM by philmodjunk

    Can you specify a range for each separate record in a field?

    trevmeister55

      Title

      Can you specify a range for each separate record in a field?

      Post

      Hello,

       

      I am very new to FM and using the 30-day free trial to see if I'll be able to accomplish what I need before I purchase the program.  I am trying to use the database to help me match up students with scholarships they are eligible for.  I have created one database with two tables (Students and Scholarships) and they each contain some unique fields (i.e., student first name and student last name only in Students table; scholarship name and scholarship deadline only in Scholarships table), as well as fields common to both tables to determine eligibility (i.e., age, grade, GPA, race, gender, etc.).

       

      When I am entering the records in the Scholarships table, many of the fields will have a range for the elibility criteria (i.e., ages 15-20, grades 10-12, minimum GPA 3.5, race Asian or Pacific Islander, etc.).  Is there a way to enter the "range" of specific information for each record?  Ultimately, I'd like to be able to choose any student and generate a list of all the scholarships he/she is eligible for, and also choose any scholarship and get a list of all the students who are eligible for it.  I understand it will be a "many to many" relationship.

       

      Sorry if I have not used the correct terminology.  As I said, I'm VERY new to this, although I consider myself a fast learner!

       

      Thanks so much!

      Tracie

        • 1. Re: Can you specify a range for each separate record in a field?
          philmodjunk

          IF you are performing a find, you can enter a range of values as a find criterion:

           

          10...15

           

          for example would specify finding all records with the values 10 through 15 entered in that field.

          • 2. Re: Can you specify a range for each separate record in a field?
            trevmeister55

            Hi Phil,

             

            Thanks for your response!  What I'm trying to do is not actually a find, but determining the specific requirements for each scholarship when it is entered as a new record. 

             

            For example, one of my fields is GPA.  When I enter it for the student in the Students table, it's easy since each student has one specific GPA (like 3.50 or 3.77).  But in the Schlolarships table, each separate scholarship record will have it's own requirement, which is usually a range.  It could be "3.00 to 3.50" or "3.55 and higher".  That specific information is what I'm trying to figure out how to enter when I input the record in the table.

             

            Thanks!

            Tracie

            • 3. Re: Can you specify a range for each separate record in a field?
              philmodjunk

              For purposes of data entry and searching, I'd enter the minimum and maximum values in separate fields.

               

              For report purposes, I'd either use a calculation or merge text to combine the contents of these two fields into something like "3.00 to 3.50".

               

              That still leaves "or higher" as an issue. There are several options here depending on whether you will be searching on this range or not. If you need to search on this range, I'd set something up that enters an impossibly large value in the max value field (such as a GPA of 99) and use a calculation field that replaces this value with the "or higher" text on any report layouts.

              • 4. Re: Can you specify a range for each separate record in a field?
                trevmeister55

                Well, that certainly makes sense!  Sometimes the simplest solution is the simplest one!

                 

                So now I've created my 2 Tables and entered 3 records for each one (Students and Scholarships) to run a trial search.  I used separate minimum / maximum fields for age, grade level, and GPA in the Scholarships table.  What is the best way to enter race?  Some students are multi-racial, so I may need to enter 2 or 3 races for them.  Some scholarships are for any race, while others are limited to maybe 1, 2, or 3 races.

                 

                Also, what is the next step to doing my search?  I want to be able to pick a specific student (I'm guessing I could choose the unique Student ID number) and have FM use that student's information to search through the table of scholarships and give me a list of each scholarship they are eligibile for, along with that scholarship's deadline date.  Here's what I'd like the final report to look like:

                 

                 

                (Student Name and Date of Report at top of page)

                 

                ABC Scholarship          03/31/2010

                DEF Scholarship          04/14/2010

                GHI Scholarship           04/03/2010

                JKL Scholarship           03/31/2010

                 

                Thanks!

                Tracie

                • 5. Re: Can you specify a range for each separate record in a field?
                  philmodjunk

                  Perhaps a check box field will work for ethnic information as that will allow selecting mutliple values.

                   

                  I can't give you all the details of how to do your finds as I don't know enough about how you've set up your student and scholarship records.

                   

                  Sticking to the topic of this thread, any "range" criteria where you've set up minimum and maximum values can be searched like this.

                   

                  Say you want all records where a GPA of 3.0 falls within a scholarship's range.

                   

                  In find mode, enter < 3.0 in the min field and also enter > 3.0 in the max field.