table.field value calculated based on query 
Author Message
 table.field value calculated based on query

is it possible to have the value of a field in a table [table1].[field1] be
based on a calculation of JOIN query result?

e.g.,

table1:
field1        filed2        filed3
$[calc]     part1        xyz
$[calc]     part1        abc
$[calc]     part2        xyz
$[calc]     part2        abc

table2:
field1        filed2         field3           filed4qty        field5cost
part1        xyz             attrib1              4                 $2.50
part1        xyz             attrib2              2                 $3.50
part2        xyz             attrib1              1                 $2.50
part2        xyz             attrib2              2                 $3.50
...

so the calced values for the table1 will be:

field1        filed2        filed3
$17.00     part1        xyz
$9.50       part1        abc
...

$17.00 is calced = (4 * $2.50) + (2 * $3.50)

the Kits table has 11,772 records, and KitsDetail table has 17,536 records

i've tried the following but there are no updates, the Command1_Click() is
called from a button click on a form.

Private Sub Command1_Click()

Dim cmd As ADODB.Command
Dim rst1 As ADODB.Recordset
Dim rstemp As ADODB.Recordset

Dim prm1 As ADODB.Parameter
'Dim prm2 As ADODB.Parameter

Set rst1 = New Recordset
'rst.CursorLocation = adUseClient

rst1.Open "Kits", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do While Not rst1.EOF

    Set cmd = New Command
    cmd.ActiveConnection = CurrentProject.Connection
    'cmd.CommandText = "select * from KitsDetail where KitsDetail.kit=? and
KitsDetail.field2=?"
    cmd.CommandText = "select * from KitsDetail where KitsDetail.kit=?"

    Command1.Caption = "-" & rst1.AbsolutePosition & " of " &
rst1.RecordCount & "-"

    ' Create Parameter Object.
    'first parameter.
    Set prm1 = cmd.CreateParameter(, adBSTR, adParamInput, 10)
    prm1.Value = rst1.Fields("KitAlt").Value
    'Debug.Print rst1.Fields("KitAlt").Value

    cmd.Parameters.Append prm1

    'second parameter.
    ''Set prm2 = cmd.CreateParameter(, adBSTR, adParamInput, 10)
    ''prm2.Value = rst1.Fields("field3").Value
    ''cmd.Parameters.Append prm2

    ' Open Recordset Object.
    Set rstemp = cmd.Execute()

    Dim sum As Single
    Dim temp As Single

    sum = 0
    temp = 0

    Do While Not rstemp.EOF

    temp = CInt(rstemp.Fields("price").Value) *
CInt(rstemp.Fields("qty").Value)
    sum = sum + temp

    rstemp.MoveNext

    Loop

    rst1.Fields("Price").Value = CCur(sum)

    Set prm1 = Nothing
    'Set prm2 = Nothing

    rst1.MoveNext
    rstemp.Close
    Set cmd = Nothing

Loop

rst1.Close
Command1.Caption = " ... done ... "

End Sub



Fri, 25 Nov 2005 12:11:35 GMT  
 table.field value calculated based on query


Quote:
> so the calced values for the table1 will be:

> field1        filed2        filed3
> $17.00        part1         xyz
> $9.50         part1         abc
> ...

> $17.00 is calced = (4 * $2.50) + (2 * $3.50)

This is a job for a SELECT query, not an update!

If the 17.00 is dependent on the current values in the other table, then
it's pretty obvious that you don't want to store it in a table anywhere. If
the cost of the component changes, then you'd want the total cost to
reflect the new value. You can't do that if it's stored on disk somewhere.

On the other hand, if you need the cost in table 1 to be fixed
historically, and for it not to be updated with changes in the components,
then you would need to give the user a way to calculate it at data editing
time: probably using something like a command button on the form.

Hope that helps

Tim F



Sat, 26 Nov 2005 01:34:15 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Can't sort result set on query based on calculated values

2. Newbie - assigning calculated value to table field

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

4. sorting records from 1 table into several based on a field value

5. Adding a field to a table based on CrossTab Query

6. Moving a field on a report, based on value of another field

7. line charts based on calculated fields...

8. Calculating Values in a Query

9. Newbie Question: Getting the Values from Calculated controls into a table

10. Recoding a calculated value to a table

11. calculating a field in a query...

12. Using Assigmnet values in a calculated field

 

 
Powered by phpBB® Forum Software