AnsweredAssumed Answered

Summing info from a selection of records and storing the info in another table, creating a new...

Question asked by Tessf on Oct 6, 2010
Latest reply on Oct 7, 2010 by philmodjunk


Summing info from a selection of records and storing the info in another table, creating a new record for each selection.


I want to find the sums of certain fields in Table A, where the database will return a separate sum for each of several descriptions listed in Tables A and B (and possibly C as well), where the descriptions will have matching and un-matching instances between A and B (and C). A separate but single record must be created in Table D for each description (of a given class, which classes may be defined differently in Tables A, B and C though something defined as p in table A will always be in the same class as something defined as q in Table B, though there is a class n in table A which has nothing to do with class m in table B), matched or unmatched, to record the given sums.

A new set of records must be created for every date that this exercise is done. These will be project "snapshots", taken as at a given cut-off date for billing purposes. So there will be a record for each description x as at date y, an new one as at date z etc. in Table D.

When I enter a date in Table Z, I want this function to rattle off automatically. I currently have to do it manually, by requesting the sums by description from table A, then from Table B etc, then compile them into a small report done in Excel. 

It is mind-numbing when I am required to do more than a few at a time. And anyway, this is what databases are for, no?

Is anyone out there willing to help coach me through this? I just can't seem to get my mind around how FmPro10 Adv. needs to have the things defined and the calculations defined or how to create several new records in a second table for one new record requested in another table... I seem to have gotten halfway through and now can't get past the block.

And going back to do this project by table by project and setting up an excell sheet will have to be done in the short term, but I can't handle that in the long-term. I would rather spend 200 hours on getting FmPro10 Adv. to do it than 10 on getting it done by hand, because by the time I am done doing it the slow way, I have to start again. 

And anyway, I can be sanctimonious about the future time-wastage to justify my attitude. ',;^)

In hope (that what I wrote is intelligible and that someone can help...) -