Loop through a table to find value and us in SQL query (1 Viewer)

[Cerodalo]

So after years of getting great value from the site I've been stumped and need to post my first question! Thanks for all the advice in advance and sorry of the questions seems overly simple.

I'm trying to grab a name in the ProductsXref table and use it in a query that appends records to a table. I tried to use this code below but it doesn't work and I get an error. I'm struggling to know if I'm stepping through the rows AND if the SQL code will work. Thanks!


Dim rs As DAO.Recordset
Dim sSQL As String
Dim aSQL As String
Dim sValue As String

sSQL = "SELECT * FROM ProductsXref"
Set rs = CurrentDb.OpenRecordset(sSQL)

DoCmd.SetWarnings False
rs.MoveFirst

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
While (Not rs.EOF)
sValue = rs("Myfield")
'sSQL = "SELECT V2.customer_id, V2.customer_name, V2." & sValue & ", svalue AS Expr1 FROM V2 WHERE (((V2." & sValue & ")>0));"
DoCmd.RunSQL "INSERT INTO V2_Amend ( customer_id, customer_name, svalue, Expr1 )SELECT V2.customer_id, V2.customer_name, V2." & sValue & ", svalue AS Expr1 FROM V2 WHERE (((V2." & sValue & ")>0));"
rs.MoveNext
Wend
End If

rs.Close
Set rs = Nothing
End Sub

 

[moke123]

Add some debug.prints to the Sql strings before you execute them so you can see what they resolve to.

what is the specific error ?

the parts in red look problematic to me.

DoCmd.RunSQL "INSERT INTO V2_Amend ( customer_id, customer_name, svalue, Expr1 )SELECT V2.customer_id, V2.customer_name, V2." & sValue & ", svalue AS Expr1 FROM V2 WHERE (((V2." & sValue & ")>0));"

 

[Mark_]

As important, what is the advantage to you of having duplicate data? Is the 2nd table not one that can be related to the original?

 

[deletedT]

And what is the exact error message?

 

[June7]

Please post code between CODE tags to retain indentation and readability.

What is error message? Which line triggers error?

Consider:

While (Not rs.EOF)
    sValue = rs("Myfield")
    DoCmd.RunSQL "INSERT INTO V2_Amend ( customer_id, customer_name, " & sValue & ") " & _
           "SELECT customer_id, customer_name, " & sValue & " FROM V2 WHERE " & sValue & ">0;"
    rs.MoveNext
Wend

If field names have spaces or punctuation/special characters, need to use [] delimiters.

 

[moke123]

Another thing I just noticed is you are setting warnings to false and not re-setting them back to true. You should use the .Execute method to avoid having to do this.

Pretty safe to say that your Sql strings are not resolving to what you think they are.
To test your Sql you would do something like

strSql = "Select * from someTable where SomeField = " & SomeVariable
Debug.Print strSql
currentDB.Execute strSql,dbfailOnError

It would be helpful if you showed us your table structures and explain what you want to copy between tables.

 

[arnelgp]

the question is, what is sValue?
you declare it as string.

Dim sValue As String

then put some value on it. for you know, you can get Null value:

sValue = rs("Myfield")

does it contain a "fieldname" in it?
maybe, something like:

DoCmd.RunSQL "INSERT INTO V2_Amend (customer_id, customer_name, " & 
     svalue & ") SELECT V2.customer_id, V2.customer_name, V2." & sValue & " FROM V2 WHERE (((V2." & sValue & ")>0));"

 

[Cerodalo]

That is a big part of my issue I think. I don't know the correct format for the field references. Also, I want the field reference to also be a label in the query, which is usually set with "label" inverted commas.

Add some debug.prints to the Sql strings before you execute them so you can see what they resolve to.

what is the specific error ?

the parts in red look problematic to me.

DoCmd.RunSQL "INSERT INTO V2_Amend ( customer_id, customer_name, svalue, Expr1 )SELECT V2.customer_id, V2.customer_name, V2." & sValue & ", svalue AS Expr1 FROM V2 WHERE (((V2." & sValue & ")>0));"

 

[Cerodalo]

I think I know my problem/limitation.

I'm trying to use a record value from a table as a reference to a column name in a query (see bold below). I don't know if this is possible and if so how to make it work.

INSERT INTO V2_Amend ( customer_id, customer_name, Defendant_Watch_46 )
SELECT V2.customer_id, V2.customer_name, V2.Defendant_Watch_46
FROM V2
WHERE (((V2.Defendant_Watch_46)>0));

I'm trying to get the record value from the following code;

DoCmd.SetWarnings False
rs.MoveFirst
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
While (Not rs.EOF)
Let sValue = rs("Myfield") <--this is the record value
DoCmd.RunSQL "QUERY HERE" <-- SQL above to be used here
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing

 

[June7]

Did you see post #5?

A field name like Defendant_Watch_46 makes me suspect this is not a normalized data structure. Are there multiple similar name fields: Defendant_Watch_1, Defendant_Watch_2, etc.?

Seems there will be a lot of empty fields with this structure as code appears to be creating new records where only 3 fields out of how many receive data and one of those fields changes with each iteration.

Suggest you provide db for analysis. Follow instructions at bottom of my post.

 

[Cerodalo]

Yes, thanks, did see reply 5.

Yes, there are a lot of columns with similar naming conventions. It comes from a file pulled from a billing system that is provided in the format.

Once you get the DB you'll see the V2 table which is the main table. In essence I'm trying to figure out which customers are running which products, which are provided as column names and values in the subsequent fields.

 

[arnelgp]

You have serious table normalization problem.
here test this.

 

[Mark_]

To help us aid you better, is your intent to import the excel sheets into temporary tables then move them to a normalized set of tables? If yes, is this a one time import or will you need to do it again?