How to modify a table

[Ben_Entrew]

Hi all,

I want to modify a table called FC_TEMP via adding some columns from another table called AVERAGE_TRP. Do I have to create a new query table and then insert the output into a new table?
The following code shows me : Cannot execute a selected query.
Is there a way to update this FC_TEMP table without creating another temporary table?
Thanks in advance.

Regards,
Ben

Public Sub Update()
Dim strSQL As String

strSQL = "SELECT FC_TEMP.*,AVERAGE_TRP.[Average_new_TRP_EUR],AVERAGE_TRP.[Average_old_TRP_EUR],AVERAGE_TRP.[Average_Margin] " & _
         " FROM FC_TEMP " & _
         " INNER JOIN AVERAGE_TRP ON FC_TEMP.[PRODUCT_ID] = AVERAGE_TRP.[PRODUCT_ID]"

CurrentDb.Execute strSQL

DoCmd.RunSQL "UPDATE FC_TEMP SET FC_TEMP.[new_TRP_2013_in_EUR] = FC_TEMP.[Average_new_TRP_EUR] " & _
             " WHERE FC_TEMP.[new_TRP_2013_in_EUR] is Null"
           
DoCmd.RunSQL "UPDATE FC_TEMP SET FC_TEMP.[old_TRP_2013_in_EUR] = FC_TEMP.[Average_old_TRP_EUR] " & _
             " WHERE FC_TEMP.[old_TRP_2013_in_EUR] is Null"
                     
DoCmd.RunSQL "UPDATE FC_TEMP SET FC_TEMP.[Margin] = FC_TEMP.[Average_Margin] " & _
             " WHERE FC_TEMP.[Margin] is Null"
                                                              
End Sub

 

[jdraw]

Tell us about the "business" requiring these fields.
It is not normally good practice to store averages (values you can calculate when needed).


You execute action queries, not Select or "read only" queries.

see http://msdn.microsoft.com/en-us/library/office/bb243015(v=office.12).aspx

 

[pr2-eugin]

Well this is where I recall my old POST !

The fields new_TRP_2013_in_EUR, old_TRP_2013_in_EUR, Margin all sound to me like they are threshold or statistic data, which all need to be in a table of its own and only needs to be joined when needed to be displayed or queried. Or maybe Calculated in Queries but not stored !

Sorry I could not be of help here ! Good luck !

EDIT: Thanks JD ! Someone to agree with me.

 

[Ben_Entrew]

Basically I want to replace missing TRP records with the average values of another table, I created before.
I took the avergares from a TRP table and created a AVERAGE TRP table out of it.
Is there a way to update the FC_TEMP table directly with the average values of the TRP table. Without to create this Average TRP table?

 

[Ben_Entrew]

I run this code before to store the average values into the Average_TRP table.
How can I avoid to store these Average values,
is there a way to combine this with one SQL in order to update the missing TRP in FC_TEMP with the average values of the TRP_2013 table?

'Creating average transfer prices out of the actual TRP data and store into AVERAGE_TRP

Public Sub Average_TRP()
Dim strSQL As String
strSQL = "SELECT TRP_2013.Customer, TRP_2013.[Product_ID], AVG(TRP_2013.[new_TRP_2013_in_EUR]) as [Average_new_TRP_EUR], " & _
         " AVG(TRP_2013.[old_TRP_2013_in_EUR]) as [Average_old_TRP_EUR],AVG(TRP_2013.[Margin]) as [Average_Margin] INTO AVERAGE_TRP " & _
         " FROM TRP_2013 " & _
         " GROUP BY TRP_2013.Customer, TRP_2013.[Product_ID]"

CurrentDb.Execute strSQL

End Sub

 

[Ben_Entrew]

Hi all,

is there a way to use this?
Can someone help me with the syntax?

DoCmd.RunSQL "UPDATE FC_TEMP SET FC_TEMP.[new_TRP_2013_in_EUR] = AVERAGE_TRP.[Average_new_TRP_EUR] " & _
             " FROM FC_TEMP " & _
             " INNER JOIN AVERAGE_TRP ON FC_TEMP.[PRODUCT_ID] = AVERAGE_TRP.[PRODUCT_ID] " & _
             " WHERE FC_TEMP.[new_TRP_2013_in_EUR] is Null"