top 10 report

[angbrav]

Hi everyone!

Firstly, I'd like to clarify that I'm not sure if this post fits in this section. If not, feel free to reallocated!

Secondly, I'm going to present my problem:

- My DB. I have a table of Clients. This table is linked to a Bills table in a 1...n relationship using the clientID. I am storing when the bill was generated.

- I'm trying to generate a report which shows a top 10 of clients for a given year. The clients should be sorted for the amount of money they spent. That amount would be generated as the sum of client's bills for the year chosen.

My main problem is how to sum the bills of a client for a given year.

Thank you in advance,

Manuel

[angbrav]

After 56 views and no reply, I guess what I'm trying to do is not possible... Could does anyone tell me if I should discard this idea?

[JerrySalem]

You need a relationship from clients to bills that includes the given year and client id. then a calc field in clients that sums the related bill totals.

[comment]

Another option is to find the bills for a given year and sort them by client, reordered by a summary field. Finally - and this is the more difficult part - omit the records in group #11 and beyond. See something similar here:

http://fmforums.com/forum/topic/69793-limiting-find-to-first-5-records/

[angbrav]

You need a relationship from clients to bills that includes the given year and client id. then a calc field in clients that sums the related bill totals.

Sorry, I don't know how to create that relationship.. I already have a relationship between them using the ClientID but I don't know how to add the year os the bill in that relationship. Would you mind being more specific?

[angbrav]

Another option is to find the bills for a given year and sort them by client, reordered by a summary field. Finally - and this is the more difficult part - omit the records in group #11 and beyond. See something similar here: http://fmforums.com/forum/topic/69793-limiting-find-to-first-5-records/

I tried your solution and it works! thank very much!