A Derived Field

[Garret]

I'd like to insert a derived field into this situation

tblShipments (Parent table)
Shipment_No
Shipment_Size


tblShipment_Inspection (Child table)
Shipment_No (connected field)
Item_Dimension
(part of the item being inspected to see if it is "good")
Number_Out_Tolerance
(number of items in the shipment that are bad for this
dimension)


I'd like to create the field "Der_Percent_Good" that calculates the
percentage of good items in the shipment.
(1 - Number_Out_Tolerance/Shipment_Size)*100.


How would I do this since the tables are related by Shipment_No, but
the actual size of the shipment is in the parent table but the
derived
field would be in the child table.

Thanks!

 

[IT-1957]

If you wnat a calculated field, you need to do a query. You can even do and
UPDATE query if you would like to save the data permanently.

 

[Roger Carlson]

My question is why you feel you need to store this calculated value.
Although there are good reasons to do so, in the majorities of cases, if the
value can be calculated, you shouldn't store it, just calculate it in a
query, form, or report whenever you need it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Cheese_whiz]

Hi Garret,

Generally speaking, you don't store 'calculated' fields in a table. What
you do is just plug your formula into a control on your form or report where
you want to see the calculation. That way, when you need to see it or print
it, it's there.....but you don't waste space storing it in a table.

There are exceptions in situations, for example, where you have a value in
your calculation that will change over time. If you don't store the
calculation results in those cases, then when you go back and look at the
calculations for old records they run the calculation 'on the fly' using the
NEW value of the input in your calculation INSTEAD of the value AT THE TIME.

Hope that helps,

CW

 

[Garret]

Thanks for all the replies. What I wanted was a "percent good" field
for each dimension of the item.
For example, 100 of item X comes in and is inspected for height,
length, width, etc.
3 out of 100 are bad on height (so 1 - 3/100), 4 out of 100 are bad on
length (1-4/100), etc.

I discovered that I could use a query to just add the Shipment_Size
field and Number_Out_Tolerance field and do my calculations there with
a derived field. You are correct in saying that the values do not
need to be stored.

Hope this helps anyone in the future who has a similar situation.

 

[Susie DBA [MSFT]]

WRONG ANSWER

SQL Server supports calculated fields, you can even persist them; or
index them-- I mean--- Acccess Data Projects doesn't require an update
query to do simple math

 

[Susie DBA [MSFT]]

Roger

performance, performance, performance
have you ever heard of it?

If you had, you'd be using calculated fields with SQL Server and
Access Data Proejct

i"m so sorry that your retard database doesn't support calculated
fields
it just means that you guys should choose a new database

 

[Tony Toews [MVP]]

WRONG ANSWER

SQL Server supports calculated fields, you can even persist them; or
index them-- I mean--- Acccess Data Projects doesn't require an update
query to do simple math

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Susie DBA [MSFT]]

note that tony; and a lot of the other MVPs around here are blindly
PRO-JET because they don't understand some of the benefits of using
Access Data Projects

is it my fault that MS censors my posts?

I'll call myself whatever I want, kids

Tony should be ignored, he is a big fat lazy MDB sloth