3 Replies Latest reply on Mar 25, 2010 10:49 PM by RickWhitelaw

    Using OR to search across multiple tables

    spinny

      Title

      Using OR to search across multiple tables

      Post

      Hi All,

       

      I have 8 tables that store data in my recruiting database.  One for each major tournament we recruit at.  

       

      Would it be possible to perform a find that did the folowing

       

      searched the rating field in all 8 tables

      found any ratings that were either 1+ or 1

      returned them as a report

       

      If I were doing this in Excel I would use a formula that searched each table and then used the OR function.  When I set the button up and specify find it only allows me to use the AND function - anyway to change this ?

       

      Thanks

       

      Andy

       

      MAC OSX 10.6.2 - FMPro10 - nearly 2 weeks experience with FM!

        • 1. Re: Using OR to search across multiple tables
          RickWhitelaw

          While not answering your exact question, I have to ask. Wouldn't it be better to have a table of Tournaments (regardless of how many) rather than a table for each tournament? This would simplify your design and, in the event you were to add tournaments, each one added would simply mean adding a record rather than adding a table, which is in effect changing your design. This would also make your finds simpler. I'm not familiar with the rest of your design. I've always considered it best to group data "of the same kind" in its own table. If you continue the way things are, check out the Sum and Count functions and you may be able to extract the data without performing a find.

          • 2. Re: Using OR to search across multiple tables
            spinny

            It's tough because we are trying to do minimal data entry.  There are about 1500 players at every tournament we recruit at.  We are given everyone's details as an excel file before each tournament.  I import the detials into a seperate table for each tournament and then link via first name, last name & date of birth.  We then have a main data table with everyones full details in.  It was the simplest way I could think of that would allow us to import thousands of records at a time.

             

            I have a script that removes duplicates although I am trying to improve that as well.

            • 3. Re: Using OR to search across multiple tables
              RickWhitelaw

              I stand by my original advice. You can import all the players and have a unique ID assigned that can be used as a PK. Using First Name and Last Name and DOB as match fields may seem airtight, but it's not. Eventually you'll encounter more than one "John Smith" born on the same day. The records should be linked either by a "natural" but fool-proff method (hard to come by), or by a meaningless serial value. The amount of data is more or less irrelevant. !500 per event is not really a very large number. FM can handle millions and millions of records. It's more important to organize the data in tables according to the kind of data, say hierarchically. What I'm advising has no impact on the amount of data entry. I agree that automation beats user entry whenever it's possible to achieve.

                  "We then have a main data table with everyones full details in"

               

              Why have the data in more than one location? This seems to defeat the purpose of a relational database.