Jump to content

Relationship with Dates


This topic is 7205 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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,


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7205 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.