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

[ganchan]

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

etc.

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

etc.

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.

Thankyou,

regards.

G.

[mr_vodka]

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

[ganchan]

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.

Edit:

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 April 28, 2009 by Guest

[comment]

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.

[ganchan]

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.

thankyou.

[ganchan]

Done with a very simple script, thankyou all.