Jump to content
Sign in to follow this  

Sum fields of records with the same Date and create new Records "Date - SumOfFields"

Recommended Posts

Hi all, i'm very new of FMpro. I've created a complex db to organize all my activities from an old one created by a friend.

Now i need to collect the Sum of the money payed by clients day by day, grouped by Date.

So the fields i need are Two, Date and Price, of about 4000 records of the table "Days Archive".

Example records:

Date 24/01/2008 - Price 34,00

Date 24/01/2008 - Price 55,00

Date 25/01/2008 - Price 15,00

Date 25/01/2008 - Price 25,00


I need to create new records in new table/layout like:

Date 24/01/2008 - SumPrice 89,00

Date 25/01/2009 - SumPrice 40,00


I hope the example will be enough to understand.

I've posted here because i think i need to make a Script, and not a relation for tables. I'm not a programmer so, i've try to read many and many post here, but no one seems to solve this job.




Share this post

Link to post
Share on other sites

Why do you need to create new records in another table? A simple subsummary report should be sufficient for this.

Share this post

Link to post
Share on other sites

because in primis, i need to import this sums in another table/layout where i have more info and calculation for financial where each date have one record and the sum of many other fields.

thanks for the replay, i will try to understand how can i use subsummary for this purpose.


i've make a related table where i've 2 fileds.

Date (where i must import Date field from the other table)

Pirce (a calculation Sum(DaysArchive::Price)

Good, simple. Now i must make a script that import Date form the table, and group them in single records, because date are not consecutive, some time jump 2 or 3 days..

Edited by Guest

Share this post

Link to post
Share on other sites

In general, replicating data is best avoided. I don't know what exactly you need to do with it ("more info and calculation" is a rather vague description), but it's quite possible to do calculations involving summaries in the original table.

Share this post

Link to post
Share on other sites

Yes, probably i make a lot of mistake due to my beginner status ;)

I'll try to explain.

I've many tables, 2 of this are for managing cash, tax, in and out, payment , etc.

1) The first table has for every record the ID member, Date, Cash, Discount. So here i can have many records with same Date and same ID member but different Cash.

2) The second table has for every record the Date, SumOfCash, SumOfDiscount, etc. So here i can have just one record for every Date with the all the sums, then i can make calculation for Tax, payment for staff, services, difference from Pos to Cash, etc.

Since this work was made by hand, day after day, i have automate it with a "Close Day" script, so i can save every day new records from table 1 and Setting Fields in just one new record in table 2.

The problem is that i need to do it for the past year and last months because no one do it.

I need something that read the records in table 1, and save in a new record just one Date and the SumOhCash just for that Date.


Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  


Important Information

By using this site, you agree to our Terms of Use.