Summing fields based on Date

[markfmf]

Dear Forum members

I am trying to grasp the following problem/concept and have been wondering increasingly how I can best achieve it.

I’ll try and explain as best as I can.

e.g I have a table called expenses which lists all the expenses as at a certain date. Then I have a table called Income, which lists all the sources of Income as at specific dates.

Table 1: Expenses

Fields:

Exp_ID -- Date ---Description -- $ Amount

E1 -- 30/11/2008 – Charges – 10

E2 – 30/11/2008 – Fees – 20

E3 – 30/11/2008 – Internet Fees – 5

E4 – 31/12/2008 – Internet Fees – 10

Table 2: Income

Fields:

Inc_ID -- Date ---Description -- $ Amount

I1 -- 30/11/2008 – Sale of Product 1 – 20

I2 – 30/11/2008 – Sale of Product 2 – 30

I3 – 31/12/2008 – Sale of Product 1 – 20

Now I would like a table that displays a portal on another table (lets call it the Amalgamated Table of ALL expenses and incomes) that gives the SUM of Income – Sum of Expenses as at each specific date. Such that I end up with the following portal view:

Date ---Description -- $ Amount

30/11/2008 – Profit – 20 (= sum income as at 30/11/2008 - sum of expenses as at 30/11/2008)

31/12/2008 – Profit – 10

How can I best achieve this?

Thank you

Mark

Edited May 28, 2009 by Guest

[Søren Dyhr]

Expenses and Income should not be in two separate tables, should be in one - lets call it transactions, where a field or the sign tells which is what - from there is it smooth sailing in this direction:

Why? Because filemaker doesn't supports "UNIONS" as found in SQL.

--sd

[markfmf]

This is a great disappointment that FM does not support Unions.

[Søren Dyhr]

When would shared attributes not be lumped into a common table structure? When the normalization not quite is there yet!

As one being on the Filemaker vessel for some 14 years ... is it clearly not felt like a serious restraint. Similar did I initially with the absence of script triggers I have learned in other applications - as a clear omission. Today are they part of both mySQL and Filemaker ... but it's disguised blessing/curse since it in my humble opinion is the wrong way to develop a database, since you ever so much makes something to remedy inadequacies.

However must you have felt the same way with other tools as well, that categorisations vs. classifications not always are to be followed by whims. What really have happened to Lotus Approach I do not know, but a lot of migrators are drifting to these shores - with a strong will to make the tool behave tool they wish.

You could have been allured by these sirens songs, in shape of hefty marketing ... but be honest to youself. When ever there is a flat learning curve is there a price to pay - if forgetting that whatever you do must a measure of METHOD be applied.

We have all been at least once or twice fooled here - to ignore the "blueprint" before heading into the practicalities of building a database, with this tool at hand can you easily produce a hefty quantity of suburban sprawl, however is there a point in normalization and planning of the structure on the quality side of matters, such as less spaghetti scripting.

Let turn back to your problem, what is it in your table structure that makes you in want of a union ... do you not believe that either searches or relational structure can make a proper divide for which is which? What prevents you from lunping these two tables into one, which exceptions is it in your imagination that can't be handled?

--sd