child to master computation
Scott Goossen
What's the best way to put the sum of a bunch of numbers from a child table
into a field in the master table from a form? I have been just putting a
summed field into the form that adds up the numbers from the child table for
that specific record and then coding it on departing the field or tableframe
to put the number from the editregion of that field into the right field in
the master table. It usually works, but seems a bit makeshift. Thanks
David Portwood
I don't know of an easy way to do this, which makes me wonder if you should
be doing it at all. More usual would be to put a field below your detail
tableframe which you define as the sum of one of the columns. No code
required.
"Scott Goossen" <scottg...@carlogenetics.com> wrote in message
news:41151fd2_1@cnews...
Scott Goossen
I haven't actually worked with this problem for awhile, but it seems to me
that the reason that I didn't do this was because of querying the said data
and also on reports, it seemed to create some problems, especially when
you'd have a bunch of these child tables summed up and then needing a sum on
them. For instance, I was creating a database for farmers, with each
agricultural field for each year being a master record. Then there would be
child tables with different types of expenses for their crop inputs. These
would all have to be summed up and come up with a net return/acre.
"David Portwood" <res0...@gte.net> wrote in message
news:41153369$1_3@cnews...
Steven Green
Scott Goossen wrote:
> For instance, I was creating a database for farmers, with each agricultural
> field for each year being a master record. Then there would be child tables
> with different types of expenses for their crop inputs.
that might be part of your problem.. your table design could be *much* simpler..
one master table would do the trick..
field *
year *
other fields..
you can easily query, filter, sort, etc. from that single table..
--
Steven Green - Waldorf Maryland USA
Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales - Corel CTech Paradox
---------------------------------------------------
Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Trading Cards and other Sports Memorabilia
---------------------------------------------------
Larry DiGiovanni
Scott Goossen wrote:
> What's the best way to put the sum of a bunch of numbers from a child
> table into a field in the master table from a form?
You want the sum of some child table column actually stored in a field of
the master table? That's a bad idea for a relational model.
Never tried this, but seems like the way you are doing it is the way I
would've come up with.
--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.
Michael Israel
If you have to do it your way, give the calculated field object property a
name such as fielda.
On the master table in the field that you want the calculated data (let's
call it myfield), on the arrive method on myfield:
;put in trapping and/or dialog code here then
myfield.value = fielda.value
Scott Goossen
The "other fields" need to be able to have more than one entry.
"Steven Green" <gre...@diamondsg.com> wrote in message
news:41156EDD...@diamondsg.com...
Dennis Santoro
Well, maybe you need to show us something about the structure. I suspect you
have normalization problems but we can't really tell. And as others have
said, storing a calc is a bad idea in almost all circumstances. Better to
calculate it when you need it so you know it is correctly calculated from the
current data and not an artifact of data that may have changed since the calc
was done.
Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits
since 1982
Scott Goossen
Well, you all are almost convincing me to try it again with just calculating
in the report or on the form instead of displaying an already calculated
field. I am almost convinced that there was a reason why I didn't do that,
but I'll try it again and when I find that reason, I'll maybe pester you all
again then! Thanks
Steven Green
Scott Goossen wrote:
> The "other fields" need to be able to have more than one entry.
not for each record, they don't..
you only need one master-detail set, not one for each thingie..
David Portwood
Sounds like you are talking about basic revenue and expenses as it applies
to farmers, in which case you should probably approach the matter from that
perspective, starting with a table identifying sources of revenue and
another table identifying sources of expense. One source of revenue will be
from crop sales, which would probably generate a corresponding detail table
in which, to get a return per acre, you would need to include an Acres
field.
"Scott Goossen" <scottg...@carlogenetics.com> wrote in message
news:41154e92$1_1@cnews...
Scott Goossen
Ok, so are you saying that ALL the detail records for the one master should
be in one table? This record
Pesticide Date Applied Equipment Used Cost Cost/Acre
Roundup 7/16/04 Floater $1500 $20
should be in the same table as
Fertilizer Blend Date Applied Equipment Used Nitrogen Applied
Potash Applied Cost Cost/acre
46-10-5-10 5/16/04 Air Seeder 100 pounds
30 pounds $3000 $40
etc, etc?
This, of course, doesn't include everything, but should give you an idea.
The detail tables, would of course, have the field name and the crop year,
as well as a few added fields.
"Steven Green" <gre...@diamondsg.com> wrote in message
news:4117D52D...@diamondsg.com...
Dennis Santoro
Maybe, but not with quite that structure. Depends on what the actual entity is.
I'd say that is stuff put on the field and could range from pesticide, to
fertilizer to seed. So most likely it should really be something more like
PutOnFld.db
PID long int key
FID long int, foreign key (from the field table this would be the field record
key, secondary index for linking)
SID long int (lookup from the Stuff you can put on a field table)
ApDate D
EID long int (lookup to the equipment table)
Cost $ (more likely this should be broken into units and cost per unit) (Cost
per acre should be calculated from the unit cost by the number of units and the
field acreage from the field table)
This would handle your examples as either the fertilizer blend would be more
fully described in the stuff to put on table or, more likely, fertilizer,
nitrogen and potash are 3 separate things you can put on and would be 3
separate records in the table. You are making it clearer you have a
normalization problem though. You might want to take a look at my Normalization
paper on our Paradox resources page (link in my signature) for more on that.
Steven Green
Scott Goossen wrote:
> Ok, so are you saying that ALL the detail records for the one master should
> be in one table?
just as Dennis said, yes I do.. you need to expand your concept.. again,
normalization helps a lot, once it's applied to this..
sure, there might be an extra field or two that might not be used in every
record.. but..
lookup table.. your crops.. corn, potatoes, etc..
lookup table.. your stuff (items).. fertilizer X, growth hormone Y, etc..
master.. crop, year.. details such as location, acres planted, etc..
detail.. one to many.. crop, year, item, amount, cost, etc..
Larry DiGiovanni
Scott Goossen wrote:
> Ok, so are you saying that ALL the detail records for the one master
> should be in one table? This record
You can have
Master
|
v
Detail (inclusive of all parameters of the different detail subtypes)
Or you can do
Master
|
v
Detail (supertype)
|
------------------------------------
| | |
Subtype1 Subtype2 ... SupertypeN
(eg Fertilizer) (eg Pesticide)
In the second arrangement, attributes common to all subtypes (from your
example, Date Applied, Cost, Cost/Acre, etc) would be the common stub,
with another attribute for subtype ID (Fertilizer, Pesticide, etc), then
each of the details would be captured in a subtype-specific table.
The things you can count, aggregate, etc all belong in the supertype
details table: date, cost, etc. Everything else belongs in the
subtype-specific table. This shouldn't impact rowcounts if
reporting/querying are handled properly. If it does impact rowcounts, you
need to rethink your supertype.
--
Larry DiGiovanni
Digico, Inc
Scott Goossen
and thanks to all the rest, too!
"Scott Goossen" <scottg...@carlogenetics.com> wrote in message
news:41151fd2_1@cnews...
>
Scott Goossen
Thank-you very much! That makes a lot of sense. I've never had any formal
instruction in this all; just learned from what others had done, and maybe
I've been offside on a few things.
Scott
"Larry DiGiovanni" <nospam@nospam> wrote in message news:4118ddc8_1@cnews...
Dennis Santoro
Scott Goossen wrote:
> (snip) I've never had any formal
> instruction in this all; just learned from what others had done, and maybe
> I've been offside on a few things.
Really do check out my Normalization paper and the one on Database basics then.
I think you will find them helpful in getting up to speed on the theory and
practice of databases and some of the best practices and common pitfalls in
Paradox itself.