One database, two Tables.

Title

One database, two Tables.

Post

     Let's try this again.  PhilModJunk doesn't want to understand the problem and apparently we have a problem explaining things to each other.  He says my database is too complicated, too big, and I have difficulty understanding exactly what he means by 'structure.'  Is 'structure' the way I use the fields?  If so, I've recreated the 'structure' here is a simplified form.I could copy and paste all of the fields but this post is too long already.  ------- so, here goes.  

     I asked this once before and thought I had it figured out.  But, alas, it still won't work.  

     Once upon a time I had two separate databases that were used, almost all of the time, together.  Both have income/expense information in them.  I used both of these for a long time and I'm happy with the way they worked separately.  Then, I thought, "Wouldn't it be nice if both of these databases were combined?"  The thought of rewriting all of the fields of the second database, over 500 fields, into the first database was overwhelming.  Then, I discovered the button that lets me import one database into another.  So, I imported the second one into the first.  The result, I just added some buttons to jump back and forth and now it's all in one database. 

     Here's the thing though.  Each database had its own Table for fields.  So, now I have two databases in one, i.e., one database, with two separate tables.  One database.  Two different data field tables.  I merged two commonly used files into one so I could share info more easily.  Note:  I have removed all relationships between the two tables.  WHY?  I have no idea what to make a relationship.

     >Problem: Database 2 has Summary and calculation results (and/or calculations that are summarized) that I want to use in Database 1.  The simplest step would be to just copy and paste the summary fields I needed from Database 2 into Database one.  However, it doesn't work.  all I get is a blank result.  During one attempt to put summary data fields from 2 into 1, I think through a calculation field, FM Pro said I could only do that through a Global field.  So, I messed with those for a while.  

     I Created a Global field that was a calculation of the Summary/Calculation field that looks something like ...

           Expense General (calculation) = x+y+z

           Expense General_G (calculation, global) = Expense General

It worked at first.  I was happy to see the fields with the correct values.  Then, adding more data and now the global fields are empty.  So, if I have a calculation field that has a result, why does the result for the global calculation go empty?  Especially if the Global field only has the same calculation?

Here is a sample of the 'structure' of the fields in database 2 that I want to see in database 1.

========Manage Database for Company=======

     Table: Accounting 2013

     Expense: Type        Text          Indexed, Required value, Allow Override

     Entertainment_Calc:                Calculation[12] =If(Extend(Expense: Type)="Entertainment";Amount: Tax Back Calculated;0)

     Food_Calc:                             Calculation[12] =If(Extend(Expense: Type)="Food";Amount: Tax Back Calculated;0)

     . . . . . . . . . . .   etc.  All of the fields being calculated and summarized are of the same form.

     -------------- note:     "Entertainment," Food," "Gifts," "Clothing," "Miscellaneous," "Travel" are in a dropdown menu.

     Entertainment_summary:      Summary         = Total Entertainment_Calc

     Food_Summary:                   Summary         = Total Food_Calc

     . . . . . . . . . . .   etc. All of the fields being summarized are of the same form.

     Expense: Total_Summary      Calculation       Unstored, =Entertainment: Expense_Summary + Food: Expense_ Summary + Gifts: Expense_ Summary + Clothing: Expenses_ Summary + Miscellaneous: Expenses_ Summary + Travel: Expenses_ Summary

     -----------------Note: This works just fine.  I've been using this same format for over 10 years. 

     -----------------Note: I have more sets of expenses like these in the same Table  

     =============================================================================

     -----------------Note: I created these because it said I couldn't put Summary fields into the part of the database created originally, i.e., Income_Expenses with a table name of Income and Expenses

     Entertainment_G:        Calculation         = Global, Entertainment_Calc

     Food_G:                      Calculation         = Global, Food_Calc

     . . . . . . etc.

     Expense: Total_G        Calculation       Gobal, =Entertainment_G + Food_G + Gifts_G + Clothing_G + Miscellaneous_G + Travel_G

     -----------------Note: When I created these, they worked just fine.  But, as time goes on, the Global result goes to 0

     Now, if I could get the Global fields to show something other than 0 in my primary database, we'd be stylin.'  Or, if there is a magical way I can view the summary fields from database 2 in 1, that would be great too.