Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-23-2019, 01:02 PM   #1
Cerodalo
Newly Registered User
 
Join Date: Sep 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Cerodalo is on a distinguished road
Loop through a table to find value and us in SQL query

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

Cerodalo is offline   Reply With Quote
Old 09-23-2019, 01:45 PM   #2
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 765
Thanks: 3
Thanked 232 Times in 218 Posts
moke123 will become famous soon enough
Re: Loop through a table to find value and us in SQL query

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));"

Last edited by moke123; 09-23-2019 at 01:54 PM.
moke123 is offline   Reply With Quote
Old 09-23-2019, 02:24 PM   #3
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,047
Thanks: 20
Thanked 382 Times in 375 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Loop through a table to find value and us in SQL query

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?

Mark_ is offline   Reply With Quote
Old 09-23-2019, 03:17 PM   #4
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 300
Thanks: 491
Thanked 22 Times in 21 Posts
Tera is on a distinguished road
Re: Loop through a table to find value and us in SQL query

And what is the exact error message?
Tera is online now   Reply With Quote
Old 09-23-2019, 03:27 PM   #5
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,428
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Loop through a table to find value and us in SQL query

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

What is error message? Which line triggers error?

Consider:
Code:
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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 09-24-2019 at 11:33 AM.
June7 is offline   Reply With Quote
Old 09-24-2019, 02:52 AM   #6
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 765
Thanks: 3
Thanked 232 Times in 218 Posts
moke123 will become famous soon enough
Re: Loop through a table to find value and us in SQL query

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
Code:
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.
moke123 is offline   Reply With Quote
Old 09-24-2019, 03:37 AM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Loop through a table to find value and us in SQL query

the question is, what is sValue?
you declare it as string.
Code:
Dim sValue As String
then put some value on it. for you know, you can get Null value:
Code:
sValue = rs("Myfield")
does it contain a "fieldname" in it?
maybe, something like:
Code:
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));"

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-24-2019, 07:46 AM   #8
Cerodalo
Newly Registered User
 
Join Date: Sep 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Cerodalo is on a distinguished road
Re: Loop through a table to find value and us in SQL query

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.

Quote:
Originally Posted by moke123 View Post
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 is offline   Reply With Quote
Old 09-24-2019, 08:39 AM   #9
Cerodalo
Newly Registered User
 
Join Date: Sep 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Cerodalo is on a distinguished road
Re: Loop through a table to find value and us in SQL query

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
Cerodalo is offline   Reply With Quote
Old 09-24-2019, 11:32 AM   #10
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,428
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Loop through a table to find value and us in SQL query

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 09-24-2019 at 11:39 AM.
June7 is offline   Reply With Quote
Old 09-24-2019, 12:05 PM   #11
Cerodalo
Newly Registered User
 
Join Date: Sep 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Cerodalo is on a distinguished road
Re: Loop through a table to find value and us in SQL query

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.
Attached Files
File Type: zip Database2 (2).zip (83.5 KB, 8 views)
Cerodalo is offline   Reply With Quote
Old 09-24-2019, 01:29 PM   #12
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Loop through a table to find value and us in SQL query

You have serious table normalization problem.
here test this.
Attached Files
File Type: zip Database2 (2).zip (82.6 KB, 7 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-24-2019, 02:07 PM   #13
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,047
Thanks: 20
Thanked 382 Times in 375 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Loop through a table to find value and us in SQL query

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?

Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with DAO coding, find entry in table and edit loop. wakamoe Modules & VBA 7 09-27-2016 02:12 AM
Textbox value to loop through table and find match Miff3436 Modules & VBA 3 06-28-2015 06:30 PM
How to loop an update query on all records of a table macut Modules & VBA 3 09-02-2014 11:08 AM
Loop through a Table/Query and return the one or more values adi2011 Queries 5 03-10-2013 04:46 AM
VBA LOOP USING A QUERY & TABLE B&R Modules & VBA 2 09-20-2005 04:37 AM




All times are GMT -8. The time now is 07:28 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World