10 Replies Latest reply on Oct 16, 2013 12:12 PM by philmodjunk

    Access Group By

    JT

      Title

      Access Group By

      Post

           I'm still converting over from Access. I'm trying to recreate the Access Query functions, specifically the "Sum" functions to group across records.

           Approximation: Group(MemberID), Sum(Nz(Don+Dues+Premium)), Last(Submitted), Where(Submitted>=1/1/2012)

           SQL:

           SELECT [5Donation].MemberID, Sum(Nz([Donation])+Nz([Dues])+Nz([Premium])) AS SumOfAmount, Last([5Donation].Submitted) AS LastOfSubmitted
           FROM 5Donation
           WHERE ((([5Donation].Submitted)>=#1/1/2012#))
           GROUP BY [5Donation].MemberID;
            
           And on a related note, can I do SQL in FM? It looks like it's only valid through the ODBC interface.
            

        • 1. Re: Access Group By
          philmodjunk
               

                    ...can I do SQL in FM?

               If you have FileMaker 12, you can use the ExecuteSQL function to query your database and you can use that with JOIN, WHERE, and GROUPBY clauses just like you would in Access, though some of the syntax details (can't use the square brackets like you do in your example) are different. The SELECT query examples found in the ODBC and JDBC guide are valid examples of FileMaker SQL that you can also use in this function.

               You will likely also find the SQL Explorer file released by SeedCode to be helpful in exploring how to use this function.

               Without using SQL there are several different ways that you can use to get sub totals for different groups of records. If you perform a find to pull up a found set of records on your layout and then sort them to group them by that "group by" field, you can add a sub summary part "when sorted by" the same Group by field (called a "break" field in FileMaker help articles...). You can then place a field of type Summary inside this sub summary layout part to get an aggregate result based on each group of records in your found set. Note that a sub summary layout part is invisible when the records are not sorted in a sort order that includes its break field--thus you can make different report elements appear and disappear from a report simply by sorting your found set into different sort orders. There's also a function, getSummary that can access the same subtotals for use in calculations.

               Another approach is to use either summary fields or calculations with an aggregate function such as sum, count, average.... to compute an aggregate value based on a set of related records from a related table so there are cases where you can set up some relationships to provide such totals.

               Here's some links that you may find helpful:

               Summary Report Tutorial: Creating Filemaker Pro summary reports--Tutorial

               Summary Recaps: FMP 12 Tip: Summary Recaps (Portal Subtotals)

          • 2. Re: Access Group By
            JT

                 Thanks. Good Info. I think I understand that.

                 But I don't see an option for only the subtotals. Right now, the results show one line for each ID. I want one line per unique ID with the subtotal and last donation date. So if I have 4 people who gave a total of 50 times, I want 4 lines in the output.

                 Also, ExecuteSQL seems always to ask for an ODBC connection and won't act on the FM tables. Shouldn't I be able to just say tableName::fieldName and have the SQL work?

            • 3. Re: Access Group By
              philmodjunk

                   You need to use the ExecuteSQL() function call in a calculation, not the Execute SQL script step.

                   I suggest reading the two links that I provided at the end of my last post. The summary tutorial describes how to get a report with a single row for each sub total and the other walks you through the process of using the ExecuteSQL calculation.

                   Please note that ExecuteSQL() is a new feature added with FileMaker 12. You won't find it in earlier versions.

              • 4. Re: Access Group By
                JT

                     I did read both, but they are long and intricate, especially for someone not familiar with the system and trying to translate from their example to another use. Can you narrow down for me which section/wording will provide one line per ID instead of one line per record?

                     I see the ExecuteSQL function now. I'll look more closely at that.

                      

                      

                • 5. Re: Access Group By
                  philmodjunk

                       It's right at the end of the tutorial as it starts with a standard summary report with sub summary parts and then you make a single modification to the layout--you remove the body layout part and you now have one row for every group without any list of data from individual records.

                       But first, you have to be able to create that summary report. wink

                  • 6. Re: Access Group By
                    JT

                         I've got that. Maybe the confusion is in the exporting of records. I don't want to just look at it. I want to use it as output for other applications. So if I export the records to excel, I'm still getting the individual line items.

                         I've got a new layout that has no body. Only includes the sub-part with the MemberID and the summary field (total of donation) and last gift (max of date). When I export this, I still get one record for each record in the table (each donation), not one record for Member ID.

                         Is there a different procedure to get the excel output to be correct versus just the layout? Thanks again for the help.

                    • 7. Re: Access Group By
                      philmodjunk
                           

                                So if I export the records to excel, I'm still getting the individual line items.

                           Not if you export using the Group By option that appears in the dialog when you select Export Records from the File menu. And this does not require using the summary report layout that you created to produce these results either.

                      • 8. Re: Access Group By
                        JT

                             That sounds promising. I'll give it a try.

                              

                        • 9. Re: Access Group By
                          JT

                               I did try but it didn't work out. The 'Group By' seemed to be read-only. I didn't see a way to drag a field there to be grouped-by. There are 'Move' buttons to have the fields be visible in the output, but there's no button or click-drag functionality to take it to the Group By area of the export. Am I missing something?

                          • 10. Re: Access Group By
                            philmodjunk

                                 Are your records sorted to group them the way you want them grouped before opening this dialog?

                                 This pane automatically lists any field on which grouping is possible and this, in turn, is controlled by the found set's current sort order.