Finding records based on date

[MLink]

Hi everyone,

Before I ask this please bear in mind I am so far completely self-taught in FM and so there is a very good chance this is a very stupid question.

I have a table called Projects. I have a related table with invoices in it. I need to make a total of all the invoices from a project, in each of the 12 months. So one for February, one for March, etc.

However I can't figure out an easy way to do this. It's just a matter of something that goes through the table of invoices, checks the month, and if it's "1" adds it into the "Total Invoices for Jan" field, for example.

I'm sure there must be an incredibly easy way to do this, but the only way I can think of is filtering all the invoice data by creating 12 new instances of the Invoices table on the relationship graph and then matching them.

Can anybody help me out, or at least point me in the direction of the fundamental resources I am missing?

Thanks guys and gals, all replies welcome.

[Fitch]

It's actually not that easy to do this.

It's possible to do it using scripts and/or relationships as you suggest, but before you go that way, I suggest you start here:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html

[David Jondreau]

Wouldn't a Summary report work?

Create a calc field in the Invoices table that gives you a working Month/Year combo for the Invoice Date. Something like

= Right(0 & Month(Date);2) & Year(Date)

Then create a Summary field which is the Sum of your Invoice totals.

On a report layout, add a Subsummary field when sorted by your calc'd MonthYear field. Add your new Sum field Sort by MonthYear and Preview.