How to replace some values from another Query

B

Ben_Entrew

Registered User.
Local time
Today, 05:11
Joined
Dec 3, 2013
Messages
177
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.

Code: 
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
 
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.
 
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.
 
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.
 
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.

Code: 
'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
 
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 !
 
Try Is Null.. Not = Null. Also try the following code..
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
 
Last edited:
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.

Code: 
'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
 
Paul, I tried your code.
It show me an error: Too few parameters.Expected 1.
And jumps to the CurrentDB.Execute line.
 
Replace the code with the following,
Code: 
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.
 
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.
 
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?
 
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:
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
 
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
 
I guess I'm too stupid to program anything.
Click to expand...

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.
 

Attachments

You must log in or register to reply here.

Similar threads

H
How to insert some data from Excel spreadsheet and the other data from Access combo boxes
Replies
6
Views
452
Pat Hartman
P
N
Data source changed from a table to a query
Replies
13
Views
490
Pat Hartman
P
D
Solved Populate Form & Associated Table with Values from another Table
Replies
5
Views
574
Pat Hartman
P
oxicottin
How to stop form from closing
Replies
7
Views
1,171
Pat Hartman
P
K
DLookup to return different value on MoveNext
Replies
5
Views
328
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom