8 Replies Latest reply on Jun 25, 2017 12:58 PM by texas0478

    JOIN  TWO TABLE (as UNION SELECT)

    Lub

      Hi,

      I must reply with filemaker a database that is now on Microsoft Access. I would like to join two tables/queries in one report.

      I need to have a report in wich I can see both transaction from two tables/queries (in ACCESS SQL this is the statement

       

      SELECT tblOrdini.IDordine, tblOrdini.DataOrdine AS Data, qryLibriAsc.Titolo AS Causale, qryDettaglioOrdini.Prezzo AS Importo

      FROM qryLibriAsc INNER JOIN (qryDettaglioOrdini INNER JOIN tblOrdini ON qryDettaglioOrdini.OrdineFK = tblOrdini.IDordine) ON qryLibriAsc.IDlibro = qryDettaglioOrdini.LibroFK

      UNION ALL SELECT tblOrdini.IDordine, qryPagamenti.DataPagamento AS Data, "Pagamento" AS Causale, qryPagamenti.Importo

      FROM qryPagamenti INNER JOIN tblOrdini ON qryPagamenti.OrdineFK = tblOrdini.IDordine

      ORDER BY data;

       

      IS it possible in FILEMAKER to have this procedure ?

      For example:

      TBL1: id, date, description, amount

      TBL2: id, date, description, amount

       

      REPORT:  merge both transaction from two table.

       

       

      thanks all

      angelo

        • 1. Re: JOIN  TWO TABLE (as UNION SELECT)
          philmodjunk

          Import both sets of records into the same table.

          • 2. Re: JOIN  TWO TABLE (as UNION SELECT)
            Lub

            No... I don't want a reduntant data.  I need only a report as a query in Access that show The merged transaction order by date.

            • 3. Re: JOIN  TWO TABLE (as UNION SELECT)
              beverly

              If it's an Import, the second table has a constant/literal that would need to be supplied after the import:

              "Pagamento"

               

              But is it an import? is the data already pushed into FileMaker (from Access) and now a report is needed?

               

              beverly

              • 4. Re: JOIN  TWO TABLE (as UNION SELECT)
                Lub

                pagamento is an alias.

                Its a new database (that exist now on Access).

                from two tables orders and payments i need a report for  a customer that merge transaction from both tables

                • 5. Re: JOIN  TWO TABLE (as UNION SELECT)
                  philmodjunk

                  FileMaker can't do a Union. It can use import records to combine data into one table for report purposes. The table can be one that is regularly purged if you want it only used for reporting, but if you want a Union type join, it's really the only way to do that with native FileMaker features.

                  • 6. Re: JOIN  TWO TABLE (as UNION SELECT)
                    beverly

                    OK. Let's clarify that!

                    1) FileMaker SQL plug-in can UNION

                    2) FileMaker function, ExecuteSQL() can UNION

                    3) FileMaker as ODBC source can UNION

                    4) FileMaker Import using SQL (external ODBC source) can UNION

                    otherwise, true.

                     

                    But these may not help with Access external source...

                    beverly

                    • 7. Re: JOIN  TWO TABLE (as UNION SELECT)
                      Lub

                      thanks beverly... i've seen....

                      but I think now that filemaker isn't SQL-CENTRICO. for this purposes best is Microsoft Access and (of course) MY SQL, Microsoft SQL eccetera.

                      It is too difficult to have a simple UNION JOIN result that in Access I can have with a couple of query and sub-query

                       

                      so sad ....

                      angelo

                      • 8. Re: JOIN  TWO TABLE (as UNION SELECT)
                        texas0478

                        Hi,

                         

                        I have lately faced the same problem.  You are right, Fillemaker is not so much SQL friendly, at least for two reasons:

                        (1) native FM SQL engine within the filemaker DB itself is unable to alter table data, hence any query result is difficult to work with (ie. a separate script is needed to write up the results into a destination table)

                        (2) even if the previous point is worked around, the results need to be updated manually every time the DB changes (ie. no such automatic update exists as per with table occuarances)

                         

                        For point (1) the easiest thing is to use this plugin

                        https://www.dracoventions.com/products/2empowerFM/family/sql.php

                        that enables you to use an extended SQL command set, so that you can not only read but also write table contents.

                        In practice this means that you can use the 'INSERT INTO' sql statement for your database and you can combine it with the 'SELECT' and 'UNION SELECT' statements.  By the end of the day, within just two script lines you will be able to directly select the necessary records from any desired number of tables, join them AND write the results into a destination table. 

                         

                        (Be aware that the destination table needs to exist with the given structure suitable to treat the joined records.  Once your destination table is generated, it is usually a good idea to run a 'DELETE' SQL statement on the table before you add the new records.)

                         

                        If you need more help on how to carry out such SQL querying from a Filemaker script, the easiest thing to do is to read the help file on the plugin's home page.

                         

                        For point (2)  depending on the purpose of your 'joint' table, you would need to either fire the script if any record changes that results in a change in your destination table, or – more resource friendly – fire the script only if the report/query would need to be generated/run.  E.g I manually fire the joining SQL script whenever the layout is opened that is based on the destination table in question.

                         

                        Sorry if I was too lengthy but hope it made sense

                        1 of 1 people found this helpful