How to group by project code

[gaby78]

I am a novice and have the following scenario:

Parent table with following fields: Invoice number (primary key), Customer, Project Code, Expense Total (calculated field: sum of Amount field in child table)

Child Table: Inv Nbr (foreign key), Expense type (by value list), Expense Amount

Relationship: Inv nrb in parent table = inv nbr in child table

A project may have one expense-invoice to a dozen+ of invoices.

My problem is as follows: I want to be able to total invoices (eg total of Expense Total field in Parent table) by project code to show a cost per project. In other words, I need a list that shows invoices grouped and totaled by project number, and also be able to filter this list to show only projects with a total project cost that exceeds a certain amount (say, $50,000).

I tried with summary reports, even tried the Getsummary function, but with no results. I couldn't use the summary field or getsummary field in find mode to filter the list.

Help highly appreciated

[mr_vodka]

You can createa Self Join in your first table keying on Project Code. Then in a calc field, you can use the Sum() function to get a total of the total costs for that project number.

As for the filtering by number over 50,000, you can further narrow your results by adding an additional criteria in your relationship. It should be something like from a global field or global calc field > 50,000

[gaby78]

Thanks John. In this case don't I need a portal in the Invoice table to show related records from the Invoice2 table?

[mr_vodka]

Well you can have a seperate layout representing Invoices displaying information from your Invoices 2 table.