José, As Igor rightly points out, you are doing the "wrong" thing with this tool. A perfect database program, and SQLite approaches that in its simplicity, acts as simply an ultra-efficient device to store and retrieve your data. Everything else is up to you... like a perfect audio speaker that neither adds nor subtracts from the music, SQLite neither adds nor subtracts from the data.
You are trying to implement a "spreadsheet" in a database. A database deals with "sets" where no member of the set knows anything about any other member of that set. In fact, the set doesn't even have the concept of SORT ORDER or the number of elements in the set. An element doesn't know what is behind it or ahead of it. Figuring that out is up to you. A spreadsheet, on the other hand, is very much aware of the "location" of its elements. You can refer to them via ROW::COL combination. Well, you can always query your database, put them in an array of arrays or array of hashes (or whatever data structure you fancy) and then you have a scheme where elements are "aware" of each other. Perhaps you didn't want this "advice" and you simply wanted to implement this in the db itself (but, heck, advice is free). Well, if you want to do this _in_ the database, aggregate functions such as Count(), Sum() and SQL clauses such as GROUP BY and HAVING can give you what you want, but you certainly don't want those values in your table. A table should have only the unique attributes of a single element type. Any basic tutorial in normalization and table design will help you tremendously in figuring an efficient solution to your problem. ;-) Regards, On 3/15/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
Greetings! I have a system that links two or more records to one head record using a column called ProjID, where ProjID has the unique recNO of the head record. Anyway, what I would like to do is something like this... Imagine, recNo,ProjID,Invoice,Fund 1,1,, 2,1,10.00,30.00 3,1,20.00,60.00 4,1,100.00,600.00 5,5,, 6,5,8.33,20.00 7,5,1.00,5.00 ... ... recNo is unique. What I would like to do is to keep the head record (1 and 5, on this instance) updated as the other children values get updated. Updated means, the Invoice sum of all the child records on the Invoice column of the head record, and the same for the Fund column. This means that for head record 1, the database, after an update, should be, recNo,ProjID,Invoice,Fund 1,1,130.00,690.00 2,1,10.00,30.00 3,1,20.00,60.00 4,1,100.00,600.00 ... ... I know how to do this programmatically outside SQLite, but is there a an UPDATE command that I could use to update the Invoice or Fund column on the head record when on any of the children's mentioned column change? I have a lousy idea, which is to set the head record Invoice and Fund column to 0, and then run an add through all the records which have ProjID='1', for the first example, and after that assign the total to the ProjID record. But, is there an easier and more sophisticated way? It's probably something easy, but I am not THAT good yet, on SQL. Any help would be greatly appreciated. thanks, josé
-- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ --------------------------------------------------------------------- collaborate, communicate, compete ===================================================================== ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
