Relationship with Dates

[macrat]

I've got a database for my checking account like a regular ledger. I have a calculation called Amount that is Credit - Debit and a summary called Balance which is a running total of Amount.

I want to know the balance at the end of each month to put in another db. So in this db I have a self-relationship of Month and Year for an EndDate calculation of Max(self-related db::Date). This correctly gives me the last date of any given month.

And that's where I am completely stumped. No matter how I relate my new db to this checking db, I either get nothing at all, Credit - Debit for one particular date, or Credit - Debit for a particular month instead of the total balance up to that date. Lord, I hope this makes sense!

Could someone tell me what relationship I should be using between the two so that my second db will show the same amount in my field, Balance, as my checking db has?

Thanks for any insight,

Rat.

[transpower]

Your second table apparently has just 24 fields: the ending date of each month and the balance at that date--these need to be calculations which are summary totals up to a certain date. I assume you have a date for each credit and debit in the first table. If so, then just sum by using the Month function.

[macrat]

Would you mind spelling that out for me? I'm using Month and Year cause my first table has several years so I'm thinking I'm following you. In the second table I have the calculation

Sum (table1::Amount)

Is that what you mean? When I do that, I get the sum just for that month, a total of Credit - Debit instead of what the balance is up to that point. In other words, I want to know how much I have in the bank at the end of each month but what it gives me is what activity there was for just that one month.

Thanks for helping me with this and forgive my obtuseness.

[Ender]

Rat,

It would be helpful to know what version of FMP you are running. Perhaps you could update your profile?

[macrat]

I'm using 7 and have updated my profile

[Ender]

If you have a table of MonthYears, where records represent each Month-Year that you want to track, you can use regular date fields (Start Date, End Date) with relationship ranges to your Checking table's Date field.

Something like:

Checking::Date >= MonthYear::StartDate

AND Checking::Date <= MonthYear::EndDate

You may also want to include a Status field as part of this relationship to exclude incomplete items.

Through this relationship, you can see just those Checking records that fall within the date range for that month. This could be used with some sort of Sum(Checking::Amount) function.

To get a running balance, you can use a similar relationship to just show those records less than or equal to the Month-Year's End Date.

If this is for a printed report, you could try a different method of Sub-Summarizing by Month_Year, and showing the Amount for each month and a Running Total Balance.

One thing to note: These are dynamic approaches. But I think in accounting, the practice is to leave past balances alone. Just calculate the balance at the end of that month, and store it in a different table.

[macrat]

Ok, that's how I did it and it works just fine. The trick was sub-summarizing and then totaling. Thanks for all your help.