How would you approach this layout?

[kangle]

I am new to Filemaker Pro, but have a number of years experience with spreadsheets, macros, and programming. I am trying to do something that is likely very easy for someone experienced with databases. 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.

[Jack Rodgers]

You will also need to add year to your thinking unless of course you want a running total of the months including all years.

 

There are several approaches and this is just one of them.

 

I would add a calculated field for each month and the calculation would be:

 

If month(date) = 1, amount, 0 ) /January

If month(date) = 2, amount, 0 ) /February

..

if month(date) = 12, amount, 0) /December

 

Thus the amount would show up in the appropriate month calc field and you can do a report by category and year and the sums would show up.