Need help sorting on 2 categories.

[kangle]

This is my first database project which is a personal expense tracking system.

 

The underlying table structure is the basic record from a Transactions table with sub-records from an Items table in a portal. The match field is a unique TransID field in the Transactions table.

 

Transactions table

-----------------

TransID

TransDate

PaidFromAcct

PaidTo

TotalAmount (Sum of Items::Amount)

 

Items table

-----------

ItemID

TransID

ItemDesc

Category

Amount

 

Data entry works just fine, but what I am stuck with is how to create a Layout/Report by Category AND Month like the in following table [in Excel I can use Pivot Tables or SUMIF()]:

 

Category | JAN | FEB | MAR | APR | ... | TOTAL |

---------------------------------------------------------

Home

Auto

Food

Entertainment

Gifts

Travel

----------------------------------------------------------

TOTAL

 

I have referred to several books, none of which touch on this type of layout. Perhaps the answer is that I am stuck in a spreadsheet mentality, and DBs are much more flexible to create summaries on the fly. However, I like a concise table which is one page and simple for the spouse and I to refer to without the need to know how to "drive" the database.

 

Also, I'm sure there are a multitude of methods to accomplish this, each with various efficiencies. I would like to hear your ideas to get me started down the right path.

 

Thanks in advance.

[kangle]

I've posted this same question in 2 categories and although receiving many views there are no replies. Is it this is very difficult or impossible to do, already been answered elsewhere, or does the community ignore posts by newbies? I'm puzzled.

[Maarten Witberg]

does the community ignore posts by newbies?

not usually....

 

your problem however is complex to solve. I know a tabular layout is possible, but I have never tried one that has total values in the cells.

[LingoJango]

I'll have a stab at a suggestion: do it with relationships.

 

Have a table called categories and link it to your expenses. In addition to a primary key field and a CategoryName field, give it 13 calc fields - 1 per month, plus one to set the end of the last month - plus one global where you can set the start date manually. The suggested setup will allow a rolling display (so you can choose to start in May, for instance) for greater flexibility.

 

StartOne = Date ( Month ( GlobalStartDate ) ; 1 ; Year ( GlobalStartDate) )

StartTwo = Date ( Month ( GlobalStartDate ) +1 ; 1 ; Year ( GlobalStartDate) )

 

etc.

 

Then create thirteen relationships to the expenses table, setting the date of the expense to be greater than or equal to the relevant start date field, and less than (but NOT equal to) the following start date field.

 

Then add 13 calculated fields to calculate totals for each relationship.

 

Voilà! You can add as many categories as you like. The only requirement is that your expenses Category field never be empty (an easy method is to make it an autocalc and set it to Other if it is empty, unchecking the "do not replace field content" checkbox).

 

It's fairly static except for the dates, but it should do the trick, unless there's some requirement beyond what you described. From experience with accounting/bookkeeping hassles, I'd advise you to keep the flexibility of dates even if you don't think you'll need it. It doesn't carry any more overhead than a rigid Jan-Dec setup (AFAIK!) and you might find it useful at some point.

 

The column headers, by the way, would be the Start date fields, displaying only the month name.

[LingoJango]

Oops, should've reread the table description.

 

Guess you'd need a lookup field for TransDate in the Items table for this suggestion to work.

[Techphan]

My $0.02 worth...

 

1) Add this fields to your ITEMS table: MonthYear (calc). Three ITEMS fields are critical to populate the spreadsheet: MonthYear (column), Category (row), and of course the Amount.

 

2) For every cell in the spreadsheet you will need to define a corresponding field. So your "spreadsheet" in FM is just a table with the dates (month-year; columns) and your categories (rows).

 

Here are the 8 "rows:"

 

MonthYear

Home

Auto

Food

Entertainment

Gifts

Travel

Column Total

 

Then you will need 13 columns: 12 for MonthYear displays and 1 for Row Totals.

 

Then it becomes a matter of defining 8*13=104 fields and placing them appropriately on a layout for viewing and/or printing.

 

As LingoJango said above, you will have to have some other control-type fields that let you set a begin date to populate this table. I would imagine that the individual items will be dropped into the spreadsheet cells by a looping script that sets ItemBeginDate and (therefore) ItemEndDate, and looking at all of the ITEMS that fall between those dates and adding them to the appropriate spreadsheet cell.

[LingoJango]

I like my approach better 'cause it allows for more categories if you ever want to be more granular. Not to mention involving far fewer field definitions!

[Maarten Witberg]

nice couple a cents Tecphan... one thing to consider is you've fixed the number of possible categories. You could go the way of a separate CategoryMonths table where you generate a report of the past six months or year or whatever. Referencing the totals would then be a matter of matching YearMonth and Category to the items table I think, for each of the four or six or whatever amount of months you want in the report.

The column totals would then be part of another MonthlyReports table... I'm telling you this requires more brainbends than I am capable of generating right now having the 'flu... I'd settle down and use simple monthlies on screen in a portal and use summary layouts for printed overview. 's quite enough work to make a smooth experience of for the casual user/developer.

[Maarten Witberg]

oh yes Lingo, i see you've been thinking along the same lines, I missed your post and I almost doubled it. I'm not saying either of us has it solved though.

[LingoJango]

Drink plenty of fluids (no genever, though) and rest. You have my permission to take a break! smiley-wink

[kangle]

Thanks for all the responses thus far. I will take another stab at it using the ideas above.

 

Thank you.

[Maarten Witberg]

I'll take that to heart Lingo. Actually I'm feeling better already.

 

kangle, if you need more help please let us know.

[kangle]

I've been proceeding with the method LingoJango presented, but am stuck on a few points.

 

To update, I have 3 tables now:

 

Transactions table

--------------------

TransID (match field w/ Items table)

TransDate

PaidFrom

PaidTo

TotalAmount (Sum of Items::Amount)

 

Items table

------------

ItemID

TransID (match field w/ Transactions table)

ItemDesc

Category (match field w/ Categories table)

Amount

ItemTransDate (LookUp field from Transactions::TransDate)

 

Categories table

------------------

CategoryName (match field)

GridStartDate (Global Starting Date)

Month01StartDate (Calc field based on global GridStartDate)

Month02StartDate

Month03StartDate

...

Month12StartDate

CategorySum [Calc field = Sum(TransItems:Amount)]

 

Now what I am unsure about is how to create the 13 relationships described by LingoJango. Do I need 13 table occurrences of the Categories table, in each making the relationship between the appropriate month fields using the rules described. I have tried this but it does not seem to sum correctly.

 

JL says, "Then add 13 calculated fields to calculate totals for each relationship." I believe the CategorySum field I have in the Categories table above is not what was intended. More detail on this calculation field would help.

 

Thank you.

[LingoJango]

You left out one global calc date field: Month13StartDate or Month12EndDate (you need it for the last relationship; for symmetry of relationships and calcs, I'd go with Month13StartDate). Drop the CategorySum field or change it and make it one of the 12 number calc fields as described below.

 

You need 12 occurrences of the Items table, which you then link to the Categories table (or some occurrence thereof).

 

The 12 calculated number fields would then be

 

Month01Total = Sum ( Month01Items::Amount)

Month02Total = Sum ( Month02Items::Amount)

...

 

Your layout would be a list view based on the Categories table, or you could also have a portal in a layout based on a table with a universal relationship to Categories. Go for the list view for now, if you're not sure - less work.

[LingoJango]

Sorry, of course you do need a CategoryTotal field. That can be

 

CategoryTotal = Sum ( Month01Items::Amount) + Sum ( Month02Items::Amount) + (...) + (Sum ( Month12Items::Amount)

[LingoJango]

Sorry, I may not have made it clear that all the calculated DATE fields might as well be globals.

[kangle]

I have it working now! Thanks for your generosity of time it helping me get on track. I especially like how the dates are now dynamic so come end of year or anytime I wish, I can timeshift the displayed grid without any effort.

 

I am now trying to get the column totals calculated and displayed along the bottom. Is this possible in the list view?

 

kangle

[LingoJango]

Yes. Set up one summary field for every Month total field, and put it in a footer part of your list layout (you may need to create the footer part).

 

That way if you want to do an analysis that excludes mileage you just omit mileage and get the totals.

[kangle]

This project is coming along nicely with the guidance I have received here. I have 1 little thing that I cannot achieve. I want a checkbox with no caption and if checked I want to store a value of 1 in the field, if unchecked, 0. It seems I have to create a checkbox set with a value list then drag the field boundaries to hide the text. Is there a more direct way? Furthermore, when showing this field in a portal, one more checkbox always appears than there are records in the portal. Is there a way to only show the checkbox once some other field in that record has been created?

 

Thank you.

[Maarten Witberg]

Is there a more direct way?

no, this is the way to do that. you need however just the value 1 since 0 and "null" (empty field) will give the same result in logic operations.

 

Is there a way to only show the checkbox once some other field in that record has been created?

This happens if you have selected "allow creation of related records in this table" (TO graph dialog). If you unselect this option, the box will disappear. It will however, oblige you to script creation of portal records. Fwiw, I personally think that makes for a smoother user experience.