I'm working on a relationship between two tables in the same file. One contains records of expenses, the other shows statistics of these expenses.

Some of the fields in the expense file is:

Username - text

Year - number

Month - number

Type - text

Description - text

Cost size - number

Sum cost size - statistic sum of cost size

In the stats table some fields are:

Username - text

Year - number

Month - number

Type1 - text (global)

Type2 - text (global)

Type3 - text (global)

Type4 - text (global)

Type5 - text (global)

In the type field in expenses you write food, petrol, rent etc.

The type fields in the stats table is based on a valuelist from the type field in the expense table.

You choose which expense types you want to get stats of. There are relationships matching user-user, year-year, month-month and type-type1-5.

Here comes the problem: When you have filled the type 1-5 fields and got stats of 5 different expenses I want one field to show how big part the expenses not choosen is.

I created a relationship matching user-user, year-year, month-month and then not matching type with type 1-5 (using the "overwritten" "=". I thought this would make me get the sum of the expenses which not were type 1-5 but no expenses "got through" the relationship.

Why?

Each table should have an key field with an ID number of some sort -- a simple serial number will suffice. This is the field used for relationships.

Whay are the Type fields global? And why is there more than one?

On the stats layout there are 5 fields (type 1-5) which the user types in by himself. For example:

Type 1 - Rent

Type 2 - Amortization

Type 3 - Food

Type 4 - Clothes

Type 5 - Health care

Then 5 "copies" of the summary field from the expense table are placed on the layout, each based on one of the 5 different relationships.

Then I want one field who summarizes all the other costs registered in the expense table (telephone bill, entertainment, furniture and whatever the user chooses to register).

There are 12 records/year in the month table. I want to go from record to record in the month table, viewing the stats, without having to re-type the type 1-5 fields. Thats why they are global.

The questions seems easy to frame when they are in my head, but often gets a bit unclear when I'm trying to type them down. I'm sorry for that.

Hi, David. I am going out on a limb because i cannot test this, but i believe the issue is that you are relating a text to a global. If you have an eqaulity join, this relationship would return all records whether they match or not. Thus, if your relationship is Expenses::Type = Stats::Type1, the contents of either field are irrelevant--this relationship is ALWAYS valid.

Now here's the part i don't know because i'm not using v7 yet: Does the above imply that the relationship Expenses::Type != Stats::Type1 (not equal) is always INVALID? I would suspect that is the case. Thus, no matter what you have in Type, Type1, Type2, ... , Type5 you will not get a result.

Hopefully someone using v7 will verify this or set me straight.

Jerry

But how can I work around this?

It has to be quite common that someone wants to get a sum of "the rest".

I don't see any reason for global fields, but I don't think that is your problem.

Your relationship will have to look something like this:

user = user

year = year

month = month

type != type1

type != type2

type != type3

type != type4

type != type5

Use the Sum function with this relationship to get the total of all others.

Happy Birthday, Ralph!

Thanks!

Earlier I used summary fields and tried to get the stats through relationships. My relationships looked exactly like the one you wrote. Now I tried to use the sum(xxx::xx) fields places in the stats table with the same relationships as earlier. Still doesn't work, nothing gets through the relationships.

If I didn't use global fields, wouldn't I have to type text in each stats record (in the fields 1-5):

Actually you only need one record in the stats table.

The relationships work for me see the attachment.

Could you attach the file in another compressed format?

OK here it is zippped.

Expense.fp7.zip