How to replace some values from another Query

[Ben_Entrew]

Dear all,

I would like to replace missing TRP information from Query LF with average TRP information coming from Query LF_Average.
Somehow it doesn't work.

Can someone please help me?

Thanks in advance.

Public Sub Replace()
Dim strSQL7 As String
Dim qdf7 As QueryDef

strSQL7 = "SELECT LF_QUERY.*, LF_AVERAGE.Average_TRP_EUR " & _
          " FROM LF_QUERY LEFT JOIN LF_AVERAGE ON LF_QUERY.[Product Class] = LF_AVERAGE.[Product Class] " & _
          " WHERE (TRP_2013.[new TRP 2013 in EUR]) Is Null"
           DoCmd.RunSQL "UPDATE TEMP SET [LF_QUERY.new TRP 2013 EUR] = [LF_AVERAGE.Average_TRP_EUR]"
    
    With CurrentDb
                
                Set qdf7 = CurrentDb.CreateQueryDef("FINAL_LF", strSQL7)
                .Close
    End With

End Sub

 

[pr2-eugin]

Seems like this is a Calculation, why do you wish to store that information? Calculated fields belong to Query not tables. Could you explain what is that you exactly want to do?

 

[Ben_Entrew]

Hi Paul,
yes it's a calculated field. Basically I got the following issue:

There are some products with no price yet, therefore I created a query which give me the average prices of the belonging product classes.
The missing prices of the product should be replaced by the average prices of the product class.
Hope I could clarify a little.

 

[pr2-eugin]

Still this is not really a great reason to store this information.

You can handle Null values in Queries and Forms using the Nz() function.

 

[Geotch]

You should bring the query with averages in the main query and use an iif statement to replace the nulls with the average. I just uploaded an example database for for a generic customer costs that does the same thing.

Search my user name here, can't get the link right now.

 

[Ben_Entrew]

Thanks for the hint Geotch. I created a Forecast Table with the missing TRP.
I tried the UPDATE command with SQL..
But it gives me an error: Operation must use a updateable query.
Any idea how I can resolve this?
Thanks in advance.

'Update missing TRP with average TRP for Forecast Table
Public Sub MyUpdate()
DoCmd.SetWarnings False
    
    DoCmd.RunSQL "UPDATE FORECAST LEFT JOIN LF_AVERAGE ON Forecast.[PRODUCT CLASS] = LF_AVERAGE.[PRODUCT CLASS] " & _
        "Set FORECAST.[new TRP 2013 in EUR] = LF_AVERAGE.[Average_TRP_EUR] " & _
        " WHERE FORECAST.[new TRP 2013 in EUR] = Null"
      
DoCmd.SetWarnings True

End Sub

 

[pr2-eugin]

Ben_Entrew, You have been warned ! I hate to say 'I told you so' in some other post in future.

Do not store calculations. Read the link in Post#2 again. See if it very essential that this needs to be stored, then think of UPDATE. Good Luck !

 

[Ben_Entrew]

Paul, what do you mean?

 

[Ben_Entrew]

Ok thanks Paul.

 

[pr2-eugin]

Try Is Null.. Not = Null. Also try the following code..

Public Sub MyUpdate()
    Dim rsObj As DAO.Recordset, tmpAvg As Double
    
    Set rsObj = CurrentDB.OpenRecordset("SELECT Forecast.[PRODUCT CLASS] FROM FORECAST " & _
                                        "WHERE FORECAST.[new TRP 2013 in EUR] Is Null GROUP BY Forecast.[PRODUCT CLASS];")
    
    Do While Not rsObj.EOF
        tmpAvg = Nz(DLookup("[Average_TRP_EUR]", "[LF_AVERAGE]", "[PRODUCT CLASS] = '" & rsObj.Fields(0) & "'"), 0)
        CurrentDB.Execute "UPDATE FORECAST SET FORECAST.[new TRP 2013 in  EUR] = " & tmpAvg & " WHERE Forecast.[PRODUCT CLASS] = '" &  rsObj.Fields(0) & "' AND FORECAST.[new TRP 2013 in  EUR] Is Null"
        rsObj.MoveNext
    Loop
    
    Set rsObj = Nothing
End Sub

 

[Ben_Entrew]

I really don't want to be a pest.
I created another table called AVERAGE_FORECAST_TRP.
There are no calculated fields left.

When I run the statement below. It runs through without any error,
but it doesn't update anything.

'Update missing TRP with average TRP for Forecast Table
Public Sub MyUpdate()
DoCmd.SetWarnings False
    
    DoCmd.RunSQL "UPDATE FORECAST LEFT JOIN Average_Forecast_TRP ON Forecast.[PRODUCT CLASS] = Average_Forecast_TRP.[PRODUCT CLASS] " & _
        " Set FORECAST.[new TRP 2013 in EUR] = Average_Forecast_TRP.[Average_TRP_EUR] " & _
        " WHERE FORECAST.[new TRP 2013 in EUR] = Null and FORECAST.[Time_Period] = '" & timeperiod & "'"
      
DoCmd.SetWarnings True

End Sub

 

[Ben_Entrew]

Paul, I tried your code.
It show me an error: Too few parameters.Expected 1.
And jumps to the CurrentDB.Execute line.

 

[pr2-eugin]

Replace the code with the following,

Public Sub MyUpdate()
    Dim rsObj As DAO.Recordset, tmpAvg As Double, tmpStr As String
    
    Set rsObj = CurrentDB.OpenRecordset("SELECT Forecast.[PRODUCT CLASS] FROM FORECAST " & _
                                        "WHERE FORECAST.[new TRP 2013 in EUR] Is Null GROUP BY Forecast.[PRODUCT CLASS];")
    
    Do While Not rsObj.EOF
        tmpAvg = Nz(DLookup("[Average_TRP_EUR]", "[LF_AVERAGE]", "[PRODUCT CLASS] = '" & rsObj.Fields(0) & "'"), 0)
        tmpStr = "UPDATE FORECAST SET FORECAST.[new TRP 2013 in  EUR] = " & tmpAvg & " WHERE Forecast.[PRODUCT CLASS] = '" &  rsObj.Fields(0) & "' AND FORECAST.[new TRP 2013 in  EUR] Is Null"

        [COLOR=Red][B]Debug.Print tmpStr[/B][/COLOR]

        CurrentDB.Execute tmpStr
        rsObj.MoveNext
    Loop
    
    Set rsObj = Nothing
End Sub

When it highlights the error. Go to the Immediate Window (Ctrl + G), and check what is the outcome in the immediate window. See if there is any syntax error. If in doubt, paste the result of the immediate window on here.

 

[Ben_Entrew]

Paul,
in the immediate window I get:
UPDATE FORECAST SET FORECAST.[new TRP 2013 in EUR] = 0 WHERE Forecast.[PRODUCT CLASS] = '' AND FORECAST.[new TRP 2013 in EUR] Is Null

I don't understand the part UPDATE FORECAST SET FORECAST.[new TRP 2013 in EUR] = 0.
It should only replace zero values of the Forecast table with that ones of the other
other table. The Product Class should be mapping key,occuring in both tables.I guess I'm too stupid to program anything.

 

[pr2-eugin]

By the looks of the Query, you seem to have Zero Length Strings for Product classes in the first place.

Show the SQL Query of the LF_AVERAGE. And what is the type of PRODUCT CLASS?

 

[Ben_Entrew]

Yes Paul the product id is blank when a trp price is not there in the LF_QUERY table. It looks ugly.
Here the LF_Query code:

'Create LF format and append into final Forecast table
Public Sub LF_Query()
Dim strSQL5 As String
Dim qdf5 As QueryDef
strSQL5 = "SELECT LF.ID, LF.PRODUCT_ID, TRP_2013.[Product Class], LF.Material, LF.[Material Description],LF.Customer, LF.[PAST], LF.[1 FC], LF.[2 FC], LF.[3 FC], LF.[4 FC], LF.[5 FC], LF.[6 FC], LF.[7 FC], LF.[8 FC],LF.[9 FC], LF.[10 FC], LF.[11 FC], LF.[12 FC], TRP_2013.[new TRP 2013 in EUR],TRP_2013.[old TRP 2013 in EUR], TRP_2013.Margin, LF.[TNS 1 FC], LF.[TNS 2 FC], LF.[TNS 3 FC], LF.[TNS 4 FC],LF.[TNS 5 FC], LF.[TNS 6 FC], LF.[TNS 7 FC], LF.[TNS 9 FC], LF.[TNS 10 FC], LF.[TNS 11 FC],LF.[TNS 12 FC], LF.Time_Period, LF.[TRY_EUR FX rate] " & _
         " FROM LF LEFT JOIN TRP_2013 ON (LF.PRODUCT_ID = TRP_2013.PRODUCT_ID) AND (LF.ID = TRP_2013.ID)"
                                
    With CurrentDb
                
                Set qdf5 = CurrentDb.CreateQueryDef("LF_QUERY", strSQL5)
                .Close
    End With
   DoCmd.RunSQL "INSERT INTO [Forecast] SELECT LF_QUERY.* FROM LF_QUERY"

End Sub

 

[Ben_Entrew]

Paul,

I will look how to sort out my basic data. It seems to be inconsistent,which makes my life difficult here.

Thanks for your help. This is a great forum here.

Regards,
Behzat

 

[Geotch]

I guess I'm too stupid to program anything.

You just need a good teacher/class. Until you start to see examples and work through things on your own, these can be confusing.

I always like to work backwards from a finished database. I hardly ever use VBA and SQL in Access.

Here is a sample database. It should open directly to the order form. If you enter the item or product class, tab off the field then double click on it, this database will enter average costs for you. Double click on the item and you'll get average cost of that item inserted, double click on product class and you'll get the average cost of the product class entered. Let me know if it doesn't work or if you have questions.