5 Replies Latest reply on Aug 3, 2009 8:02 PM by soltmann

    Merge or combine 2 separate tables into a 3rd table

    soltmann

      Title

      Merge or combine 2 separate tables into a 3rd table

      Post

      In a Company database solution I have two tables for events, General events & Project related events. Is there a way to merge or combine 2 separate tables into a 3rd table that includes all records from the two tables and dynamically updates with changes.

       

      I want a layout that shows all events that is searchable and able to show just events due today, past due, next 30 days, etc.

        • 1. Re: Merge or combine 2 separate tables into a 3rd table
          philmodjunk
            

          You should use a single table and then manipulate the found set with find and sort actions or use a summary report to sort and group your records. I'd need to know more detail on your table(s) before I could give a more detailed response on how you can do this.

          • 2. Re: Merge or combine 2 separate tables into a 3rd table
            soltmann
              

            Here's the DB structure. Company is a mother table. It's direct descendants are a Projects table and the General Events table (among others: contacts, documents, etc). Dependent to the Projects table is the Project Events table. I don't see how Project Events and General Events could be one table based on this structure and the associated relationships.

             

            I did try one table but couldn't get Event portals to show the data correctly based on the context. In Company view I want 2 separate portals for General and Project Events. In a Project View the Events portal should show just events related to that project.

            • 3. Re: Merge or combine 2 separate tables into a 3rd table
              philmodjunk
                

              As always, the devil is in the details. I can't tell from that description whether you can use a single table for General and Project events or not.

               

              What fields do you have defined in each of these two tables?

              • 4. Re: Merge or combine 2 separate tables into a 3rd table
                comment_1
                  

                soltmann wrote:

                I don't see how Project Events and General Events could be one table based on this structure and the associated relationships..


                If that's all the difference between the two types of events, there's no reason why they couldn't be in the same table - and if you want to be able to search events and show results of any type, they SHOULD be in the same table.

                 

                All you need is two foreign keys in the Events table - one for CompanyID, and one for ProjectID - and two occurrences of the same table for the two relationships.


                • 5. Re: Merge or combine 2 separate tables into a 3rd table
                  soltmann
                    

                  Thanks!

                   

                  I tried this method before. I couldn't get it to work as the 2nd foreign key in Events can't be Company ID because it's in all Event records. After thinking it through again I realized that I needed a second Company ID, one unique to General Events. 

                   

                  So I created a second Company ID field ('Company ID General Activity') in Events with an If/Then calculation: If(Event Category="General"; Company ID;"") where if Category=General, the Company ID shows in the second Company ID field, otherwise if the category is Project it's blank.

                   

                  I used the calculated Company ID as the key between General Events and Company ID in the Events General table and all worked out correctly.

                   

                  Thanks for the inspiration to try that method again!