Creating Calculated field in table 
Author Message
 Creating Calculated field in table

Can anyone help me?
I am trying to use the result of a calculation to update a
field in a table.  The database application is designed to
deduct various fees from a deposit, and then print a check
for the remainder after the fees have been deducted.  I
call the final amount the “Issue Amount”
I have been able to create queries that give me the
correct IssueAmount, but I cannot figure out how to get
that into the field on the table (I want it stored
permanently for record keeping purposes).  Whenever I try
to turn these queries into an update query, it says they
are not updateable.  Perhaps this is because the queries
perform calculations?
At any rate, could anyone help me figure this out.  If I
can’t do with action queries etc, is there a way with SQL
or VBA (or combo of them)?  
Thanks for helping – I’ll do my bit for others.




Sat, 18 Jun 2005 11:42:48 GMT  
 Creating Calculated field in table
Didn't you post this in another group, too?  If you must post to more than
one group, enter all groups on one "To:/Newsgroups:" line.  That way, an
answer to any one shows up in all.

Good luck

Jeff Boyce
<Access MVP>



Sat, 18 Jun 2005 19:56:24 GMT  
 Creating Calculated field in table
    Dim db As Database
    Dim rs As Object
    Dim vIndex As Long

    If IsNull(Me.txtAffectName) Then
        MsgBox "The Affect Name is a required
Field", , "Affect Name Missing"
        Exit Sub
    End If

    If IsNull(Me.txtAffectDescription) Then
        MsgBox "The Affect Description is a required
Field", , "Affect Description Missing"
        Exit Sub
    End If

    If (gvNewAffect = True) Then
        'save new main affect
        Set db = CurrentDb
        Set rs = db.OpenRecordset(Me.txtTableName)
        If rs.RecordCount > 0 Then
            rs.MoveLast
            vIndex = rs!fldWISubAffectID
        Else
            vIndex = 0
        End If

        rs.AddNew
            rs!fldWISubAffectID = vIndex + 1
            rs!fldWISubAffectName = Me.txtAffectName
            rs!fldWISubAffectDescription =
Me.txtAffectDescription
        rs.Update
    Else
        'save edited Sub Affect
        Set db = CurrentDb
        Set rs = db.OpenRecordset(Me.txtTableName)

        'find proper record
        If rs.RecordCount > 0 Then
            rs.MoveFirst
        End If

        Do While Not rs.EOF
            If Str(rs!fldWISubAffectID) = Str
(Me.txtWISubAffectID) Then
                rs.Edit
                    rs!fldWISubAffectName =
Me.txtAffectName
                    rs!fldWISubAffectDescription =
Me.txtAffectDescription
                rs.Update
                Exit Do
            Else
                rs.MoveNext
            End If
        Loop
    End If

    gvNewAffect = False

    Me.lstWISubAffects.Requery
'=====================================

if true add a new record
if not edit the fields in the matching txt box.

Hope this helps

BOBL



Sun, 19 Jun 2005 02:33:21 GMT  
 Creating Calculated field in table


Quote:
>Can anyone help me?
>I am trying to use the result of a calculation to update a
>field in a table.  

Generally a Very Bad Idea.

Quote:
>The database application is designed to
>deduct various fees from a deposit, and then print a check
>for the remainder after the fees have been deducted.  I
>call the final amount the &#8220;Issue Amount&#8221;
>I have been able to create queries that give me the
>correct IssueAmount, but I cannot figure out how to get
>that into the field on the table (I want it stored
>permanently for record keeping purposes).  

If you can recalculate it at any time, based on other fields in the
Table, there is NO NEED to store it, and in fact there's a big
downside: storing the value and storing the underlying values means
that it is possible to change either the Issue Amount in the table, or
to change any of the underlying fields, making the calculated value
invalid.

Quote:
>Whenever I try
>to turn these queries into an update query, it says they
>are not updateable.  Perhaps this is because the queries
>perform calculations?

Totals queries are never updateable, but calculated expressions not
involving totals should be; you can use the DSum() function to
calculate totals and update to that.

Quote:
>At any rate, could anyone help me figure this out.  If I
>can&#8217;t do with action queries etc, is there a way with SQL
>or VBA (or combo of them)?  

Please post the SQL of your query, and a *good solid* reason why you
should violate the basic principle that derived data should not be
stored (it's a rule that is subject to exceptions; this might be one)
and someone will doubtless be able to suggest a solution.

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public



Sun, 19 Jun 2005 05:00:18 GMT  
 Creating Calculated field in table
Here&#8217;s the select Query SQL.  This results in showing the
total of all fees (a totaled calculation from another
query) and the total deposit amount.  I need to subtract
the total fees for each deposit from each respective
deposit and place the result in the IssueAmount field
SELECT Checks.Status, Checks.DepositAmount,
FeeTotals.SumOfFeeAmount, IssueAmount
FROM Checks RIGHT JOIN FeeTotals ON Checks.SSN =
FeeTotals.SSN;
I&#8217;m not sure how to send a copy of the QBE pane with this.


Quote:
>-----Original Message-----
>On Mon, 30 Dec 2002 19:42:48 -0800, "Tom Hilpert"


Quote:

>>Can anyone help me?
>>I am trying to use the result of a calculation to update
a
>>field in a table.  

>Generally a Very Bad Idea.

>>The database application is designed to
>>deduct various fees from a deposit, and then print a
check
>>for the remainder after the fees have been deducted.  I
>>call the final amount the &#8220;Issue Amount&#8221;
>>I have been able to create queries that give me the
>>correct IssueAmount, but I cannot figure out how to get
>>that into the field on the table (I want it stored
>>permanently for record keeping purposes).  

>If you can recalculate it at any time, based on other
fields in the
>Table, there is NO NEED to store it, and in fact there's
a big
>downside: storing the value and storing the underlying
values means
>that it is possible to change either the Issue Amount in
the table, or
>to change any of the underlying fields, making the
calculated value
>invalid.

>>Whenever I try
>>to turn these queries into an update query, it says they
>>are not updateable.  Perhaps this is because the queries
>>perform calculations?

>Totals queries are never updateable, but calculated
expressions not
>involving totals should be; you can use the DSum()
function to
>calculate totals and update to that.

>>At any rate, could anyone help me figure this out.  If I
>>can&#8217;t do with action queries etc, is there a way with
SQL
>>or VBA (or combo of them)?  

>Please post the SQL of your query, and a *good solid*
reason why you
>should violate the basic principle that derived data
should not be
>stored (it's a rule that is subject to exceptions; this
might be one)
>and someone will doubtless be able to suggest a solution.

>                  John W. Vinson[MVP]    
>    Come for live chats every Tuesday and Thursday
>http://go.compuserve.com/msdevapps?loc=us&access=public
>.



Sun, 19 Jun 2005 06:07:04 GMT  
 Creating Calculated field in table

Quote:

>Here&#8217;s the select Query SQL.  This results in showing the
>total of all fees (a totaled calculation from another
>query) and the total deposit amount.  I need to subtract
>the total fees for each deposit from each respective
>deposit and place the result in the IssueAmount field

Again...

I don't know how to make this clearer.

Storing the IssueAmount field in your table

   I S    N O T    N E C E S S A R Y
   I S    N O T    W I S E
   W I L L   C A U S E   D A T A    C O R R U P T I O N

If you want to see the IssueAmount on a printed check or a report...
*CALCULATE IT* by basing the Report on this query.

If you want to find out a historical IssueAmount... *recalculate it*.

Are you *absolutely certain* that you want to do this? If so, read on.

Quote:

>SELECT Checks.Status, Checks.DepositAmount,
>FeeTotals.SumOfFeeAmount, IssueAmount
>FROM Checks RIGHT JOIN FeeTotals ON Checks.SSN =
>FeeTotals.SSN;

>I&#8217;m not sure how to send a copy of the QBE pane with this.

The SQL is the real query. The QBE grid is just a tool to create SQL;
trust me, I can read the SQL faster than I can interpret a screenshot
of the grid.

A Totals query - or any query including a Totals query - will never be
updateable. The getaround is to use the DSum() function.  Although I
think that storing this data WILL get you in trouble... if you are
willing to ignore good advice and do so anyway, you can run an Update
query updating IssueAmount a DSum expression doing the same sum as
your FeeTotals query. I'm *guessing* - note that I can't see that
query nor your tables - this would be something like

[DepositAmount] - DSum("[FeeAmount]", "[<some table I don't know]",
"[SSN] = '" & [Checks].[SSN] & "'")

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public



Sun, 19 Jun 2005 08:25:51 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Create new fields in a table based off of fields in another table

2. Create new field in existing table exactly like field in second table

3. table.field value calculated based on query

4. Newbie - assigning calculated value to table field

5. calculating fields from 2 tables

6. calculating fields from two tables

7. Creating calculated field on report

8. Create a group on a calculated field - won't work

9. Create table from a combined field in another table

10. create table query creates strange fields

11. create table query creates strange fields

12. updating fields in table from field in another table

 

 
Powered by phpBB® Forum Software