9 Replies Latest reply on Dec 30, 2009 3:38 PM by philmodjunk

    Combining Data from two different tables in one subsummary report

    canciod

      Title

      Combining Data from two different tables in one subsummary report

      Post

      I have been attempting to create a "profit and loss" type of report in a data base I created for a school food services program.  I have one data base with several tables, one for income earned through meals served and one for labor costs (still need to add a table for food costs, but that will come later).  I cannot seem to combine summary reports (by month and location) that display both the revenue and the labor costs.  I have created table occurances for the labor cost and participation and have joined them.  I thought that would do it but it has not. I can get the revenue information to report perfectly, but cannot bring over the labor cost information.  Any advice would be very much appreciated.

        • 1. Re: Combining Data from two different tables in one subsummary report
          philmodjunk
            

          Is this what you are trying to do?

           

          Month              Revenue    Labor Cost

          January, 2009   $2,000.00   500.00

          February, 2009  $2,000.00   500.00

          March, 2009     $2,000.00   500.00

          April, 2009       $2,000.00   500.00

           

          If not, please give an example

          • 2. Re: Combining Data from two different tables in one subsummary report
            canciod
              

            Actually what I want it to look like is as follows:

             

            Location (School Name - there are five schools in our district that serve food)

             

            Month :  October, 2009

             

               Revenue/Income                                        $5,800.00

             

               Labor Cost                                                 2,000.00

             

            Profit/Loss                                                   $3,200.00

             

            This needs to be repeated for each month and for each location.  After I figure this out I will need to add Food cost as a second cost item.  I have developed calculations that summarize the Revenue by location and month and that seems to show up on this "profit and loss" layout just fine.  I also can generate reports for labor costs that summarize labor costs by location and month as well.  I just can't seem to get the information from both on one report and then do a calculation on these summarized fields.

             

            Thanks for your help.  I am really frustrated...

             

             

            • 3. Re: Combining Data from two different tables in one subsummary report
              comment_1
                 Can't you keep all types of transactions in a single table?
              • 4. Re: Combining Data from two different tables in one subsummary report
                canciod
                  

                It would become quite unmanageable.  Revenue is based upon 20 -25 different factors that need to be calculated and labor costs have a totally separate set of variables that need to be calculated.  I could try that... but it would be very hard to keep the components separate.

                 

                thanks for your feedback.  It is another approach.  

                • 5. Re: Combining Data from two different tables in one subsummary report
                  philmodjunk
                    

                  It's an approach that makes the report you specify much simpler to create. What I posted was an approach that could use a table of "month" records that use aggregate functions such as SUM() to compute monthly totals and such from each of your different tables. That approach lends itself to the tabular format I posted as all your subtotals for a given month are computed from a single record.

                   

                  Creating the format you specify would simply require re-arranging the field locations.

                   

                  Just keep in mind that you have a trade-off here. Complexities from keeping all your transactions in a single table (with more flexibility) or complexities (and less flexibility) in setting up this report with a "months" table to compute the various sub-totals.

                   

                  I lean toward putting all the transactions in a single table if at all possible.

                  • 6. Re: Combining Data from two different tables in one subsummary report
                    canciod
                      

                    Can you elaborate a bit more on the approach of creating a table of "month" records that use aggregate functions?  Would my data base have two tables, one that has one record for each month and another with all the revenue and cost information in it??

                     

                    I will play around with this approach a bit.  Can you give me an example of how one record in the month table would look?

                     

                    thanks for the advice.  I am beginning to lean in that direction as this report needs to get done:))

                    • 7. Re: Combining Data from two different tables in one subsummary report
                      FentonJones
                        

                      Another approach entirely. If you had a table which had a record with the Year|Month (200912, 201001, etc.)* and the Location, a single record for each unique instance, then you could create a calculation (relational) to sum the revenue and one for costs. In the 2 target tables you would need a calculation for the Year_Month, as well as one for the Location (really should be an ID field). 

                       

                      The relationships from the Monthly Reports table would be based on YearMonth and Location. The calculation(s) would be: Sum (relationship::amount). You only need 2 relationships to the 2 data tables, and 2 calculations (in the YearMonth_Locations table).

                       

                      So it's not really a "subsummary" report, but it looks much the same and has the same numbers. The only tricky part is to create those records. If the Locations never change, you could just create a bunch of records for the next few years with a Loop. Or you could be a bit more complex, and test for the existence of the month/year/locations needed and create them on the fly if needed when you went there. 

                       

                      The above would produce dynamic totals, ie., if someone edited the original data in the data entry tables, the totals would change. If this is for historical data, and editing earlier entries is not allowed, then you could set the totals into regular number fields, for more speed. I'd keep the relational totals also, for troubleshooting.

                       

                      The Yearly could be just another 2 relationships and 2 calculations. Alternatively you could do the year as Summary fields. But I'd do the relationships. It could appear in a Sumsummary part however, so it only appears once per a year.

                       

                      * Year(date) & Right ("0" & Month(date); 2), result number or text, stored

                      • 8. Re: Combining Data from two different tables in one subsummary report
                        canciod
                          

                        Thanks for the additional approach.  I can see a long night ahead of me... :))  These are such good ideas, but as a relatively new user, I will need to work through them pretty methodically. 

                         

                         

                        • 9. Re: Combining Data from two different tables in one subsummary report
                          philmodjunk
                             Fenton is describing the same basic approach as I am. I'd use the following date calculation instead though: DateField - Day(DateField) + 1 set to return a Date result type. This calculation produces an actual date for the first of each month.