Possible multi table relationship

[alejandro52]

Hello guys. I need some help. First of let me explain the database. I have created a table with countries in witch i have information about them, like name, size, population and so on. For each country i have the expenses for it. But my problem is that the expenses are divided in 7 categories and each category is divided by 3 sub categories in witch i have multiple data to add, for example in the 5ith category, in the 2 subcategory of America i have Oil and Food. How should i create my table of expenses so when i view a country i can see each category and subcategory separate so i can sum them. What i did with the income witch was just one category, i created a table, a created a relationship with the field country of income and country of countries and added a portal in the layout view of countries. Thanks in advance.

Edited July 8, 2009 by Guest

[bcooney]

Both income and expenses should be in the same table with a type field to distinguish them.

Each expense record needs a foreign key for CountryID, CategoryID (value list of CatIDs) and SubCatID (use condtl value list from join table).

You need a table of Categories, and a table of SubCategories.

You also need a join table of Categories and Subcategories, "CatSubCat".

You will create a conditional value list from Expenses to CatSubCat by CategoryID.

On country, display all the records in your IncExp table related by CountryID. You can filter this portal by Type (Expense or Income). You can also filter by Category.

[alejandro52]

Thanks alot for your reply. I will try what you said and i will come back.

[alejandro52]

I think i managed to do something. I created a Table named IncomeExpenses with fields Country, Type(income or expense), Category, SubCategory, Title, Sum(the total income or expense).

I also created a second and a third table named Category and SubCategory with fields CategoryName and subcategoryNanme(this two fields also populate a value list) and then a created a relationship with Category and subCategory of the IncomeExpenses table and also made a relationship with the country table linking field Country with the name of the countries.

Now in the country layout i have entered a portal of IncomeExpense. But from here i can't really understand how i'm going to sum the fields of each category of each country for the expenses. I also don't know how i'm going to show the categories of each country separately.

[bcooney]

Now you need a subsummary report based on your IncomeExpenses table.

Edited July 25, 2009 by Guest

[bcooney]

Also, you should be relating all the tables by unique numerical IDs, not text values (such as country name!). Therefore, your IncomeExpenses record would have foreign keys for a CountryID, CategoryID and SubCategoryID, as well as a primary key for the record itself.