423,321 Members | 1,167 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,321 IT Pros & Developers. It's quick & easy.

Read values from a record and make an IN STATEMENT

P: 6
Need some help. I have a table called "CPM" which contains one field called "ACTNUM". I'm trying to write a code that will loop through each record into an In Statement. Final out put would be... In ('ABCDE','FGHIKD','ZADET'). The In Statement would be inserted to a DoCmd.RunSQL. Any help would be appreciated.
Mar 4 '18 #1
Share this Question
Share on Google+
14 Replies


gnawoncents
100+
P: 212
Welcome to Bytes, cberos!

I've read your post a few times, but am not sure I fully understand what you're trying to do. If you are saying that the ACTNUM filed has records ABCDE, FGHIKD, and ZADET and you want to create a string with them all, then that's simple enough. Please explain in a little more detail and give an example of starting records and what your expected outcome is. Thank you.
Mar 5 '18 #2

twinnyfo
Expert Mod 2.5K+
P: 2,536
cberos,

If you are simply using all the values in that table, you coul achieve the same results using a JOIN between the two tables. perhaps I am missing something?
Mar 5 '18 #3

P: 6
Thanks for the responses but here's a little more info and maybe you can help. I was able to loop through the records but ran into problems if the IN STATEMENT got too big. The query was killed so i need to send them in groups of 50. Looking a the code below, i have over 1,000 bid numbers and need to see what customer(s) are attached to those bids. Our system does not allow direct JOIN. I either upload to the system (takes time) or have the bid num as an IN STATEMENT (limited in size). I need help in developing a loop where i can fetch 50 recoreds, put them in an IN STATEMENT, execute a query, and repeat that process till all bids are processed.

Expand|Select|Wrap|Line Numbers
  1. Sub run_bid()
  2.  
  3. Dim rs As DAO.Recordset
  4. Dim BID_NUM, Start, Finish, InStmt, mySQL As String
  5. Set rs = CurrentDb.OpenRecordset("SELECT Distinct [BidNum] FROM 00001tbl_BID")
  6.  
  7. Start = "In('XXXXXX'"
  8. Finish = ")"
  9.  
  10. If Not (rs.EOF And rs.BOF) Then
  11.     Do Until rs.EOF
  12.         BID_NUM = BID_NUM & ",'" & rs("BidNum") & "'"
  13.         rs.MoveNext
  14.     Loop
  15. Else
  16.     MsgBox "There are no records in the recordset."
  17. End If
  18. rs.Close
  19. Set rs = Nothing
  20.  
  21. InStmt = Start & BID_NUM & Finish
  22.  
  23. mySQL = mySQL & "INSERT INTO 00002tbl_ACT ( [BidNum], [AcctNum], [AccountStartDate] ) "
  24. mySQL = mySQL & "SELECT [BidTbl].[BidNum], [AcctTbl].[AcctNum], [AcctTbl].[AccountStartDate] "
  25. mySQL = mySQL & "FROM [BidTbl] INNER JOIN [AcctTbl] ON [BidTbl].[ODBC Join Key] = [AcctTbl].[ODBC Join Key]"
  26. mySQL = mySQL & "WHERE ((([BidTbl].[BidNum]) " & InStmt & ") "
  27. mySQL = mySQL & "AND (([AcctTbl].[AccountStatus]) In ('A')));"
  28.  
  29. DoCmd.SetWarnings False
  30. DoCmd.RunSQL mySQL
  31. DoCmd.SetWarnings True
  32.  
  33. End Sub
Mar 5 '18 #4

twinnyfo
Expert Mod 2.5K+
P: 2,536
First, please use the Code Tags when you post your code--that is a requirement of the forum.

Second, what exactly do you mean?:
Our system does not allow direct JOIN.
Are you using MS Access or a different application altogether? SQL queries function within the MS Access environment, and if so, creating a join "should" be an easy task--which would also solve your problem.

However, if you have no option but to generate your query this way, then one option I see is to add a Yes/No field to the Table 00001tbl_BID indicating whether or not those records have been accounted for in your IN() Statement.

Your SELECT statement in Line 5 (above) would contain a clause to use this criteria: WHERE Not [CopiedFlag].

Since you are pulling DISTINCT rows from that Table, you will have to pull the top 50 records (SELECT TOP 50 ...), then go back and update this flag for all records in the Table 00001tbl_BID which match those codes. BTW, it would be a better structure to simply have a Table listing BidNums, to reduce data redundancy, but that is another story.

Then, you would loop through your data until all records have been copied. Each time you run this procedure, you would clear [CopiedFlag] on all records to reset your project.

I hope this makes sense. Please ask if you have additional questions.
Mar 5 '18 #5

P: 6
I'm using MS Access and linking tables from an Oracle database. All the joins are done behind the scenes and direct links are not allowed. i can create a bid table with unique values. The Distinct is not necessary. I added that code in case i was given duplicate data. how would the code look like if i do that? Feel free to adjust the code. i can adjust the local tables.
Mar 5 '18 #6

P: 6
Also, this is a monthly process for reporting purposes.
Mar 5 '18 #7

twinnyfo
Expert Mod 2.5K+
P: 2,536
I can only presume you have already tried this:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO 00002tbl_ACT ( [BidNum], [AcctNum], [AccountStartDate] ) 
  2. SELECT [BidTbl].[BidNum], [AcctTbl].[AcctNum], [AcctTbl].[AccountStartDate] 
  3. FROM ([BidTbl] INNER JOIN [AcctTbl] ON [BidTbl].[ODBC Join Key] = [AcctTbl].[ODBC Join Key]) 
  4. INNER JOIN [00001tbl_BID] ON [BidTbl].[BidNum] = [00001tbl_BID].[BidNum] 
  5. WHERE [AcctTbl].[AccountStatus]) = 'A';
It looks like your two tables 00001tbl_BID and 00002tbl_ACT are joinable....
Mar 5 '18 #8

twinnyfo
Expert Mod 2.5K+
P: 2,536
However, in the case that you cannot JOIN those two tables in such a way....

Expand|Select|Wrap|Line Numbers
  1. Sub run_bid()
  2. On Error GoTo EH
  3.     Dim strSQL      As String
  4.     Dim db          As DAO.Database
  5.     Dim RS          As DAO.Recordset
  6.     Dim strBidNum   As String
  7.     Dim strStart    As String
  8.     Dim strFinish   As String
  9.     Dim strInStmt   As String
  10.     Dim mySQL       As String
  11.  
  12.     Set db = CurrentDb()
  13.  
  14.     'Clear the CopiedFlag
  15.     strSQL = "SELECT CopiedFlag FROM 00001tbl_BID;"
  16.     Set RS = db.OpenRecordset(strSQL, dbOpenDynaset)
  17.     With RS
  18.         If Not .RecordCount = 0 Then
  19.             .MoveFirst
  20.             Do While Not .EOF
  21.                 .Edit
  22.                 !CopiedFlag = False
  23.                 .Update
  24.                 .MoveNext
  25.             Loop
  26.         End If
  27.         .Close
  28.     End With
  29.  
  30.     'Select the first 50 records
  31.     strSQL = "SELECT TOP 50 BidNum " & _
  32.         "FROM 00001tbl_BID " & _
  33.         "WHERE Not CopiedFlag;"
  34.     Set RS = db.OpenRecordset(strSQL, dbOpenDynaset)
  35.  
  36.     'Create the IN() Statement
  37.     Do While Not RS.RecordCount = 0
  38.         strStart = "In('"
  39.         strFinish = ")"
  40.         strBidNum = ""
  41.         With RS
  42.             .MoveFirst
  43.             Do While Not .EOF
  44.                 If strBidNum = "" Then
  45.                     strBidNum = !BidNum & "'"
  46.                 Else
  47.                     strBidNum = strBidNum & ",'" & !BidNum & "'"
  48.                 End If
  49.                 .Edit
  50.                 !CopiedFlag = True
  51.                 .Update
  52.                 .MoveNext
  53.             Loop
  54.             .Close
  55.         End With
  56.         Set RS = Nothing
  57.  
  58.         strInStmt = strStart & strBidNum & strFinish
  59.  
  60.         mySQL = "INSERT INTO 00002tbl_ACT " & _
  61.             "( [BidNum], [AcctNum], [AccountStartDate] ) " & _
  62.             "SELECT [BidTbl].[BidNum], [AcctTbl].[AcctNum], " & _
  63.             "[AcctTbl].[AccountStartDate] " & _
  64.             "FROM [BidTbl] INNER JOIN [AcctTbl] " & _
  65.             "ON [BidTbl].[ODBC Join Key] = [AcctTbl].[ODBC Join Key] " & _
  66.             "WHERE [BidTbl].[BidNum] " & strInStmt & _
  67.             " AND [AcctTbl].[AccountStatus] = 'A';"
  68.  
  69.         With DoCmd
  70.             .SetWarnings False
  71.             .RunSQL mySQL
  72.             .SetWarnings True
  73.         End With
  74.  
  75.         Set RS = db.OpenRecordset(strSQL, dbOpenDynaset)
  76.     Loop
  77.  
  78.     db.Close
  79.     Set RS = Nothing
  80.     Set db = Nothing
  81.  
  82.     Exit Sub
  83. EH:
  84.     MsgBox "There was an error copying the records!  " & _
  85.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  86.     Exit Sub
  87. End Sub
Keep in mind that through code is not tested, so you may want to loop through step-by-step. I've made minor changes to your code variables to get you inthe practice of better naming conventions, as well as adding Error Handling (another good practice).

Hope this hepps!
Mar 5 '18 #9

Rabbit
Expert Mod 10K+
P: 12,248
I don't understand. Why isn't a join allowed in your system? Oracle SQL supports joins. Is this some sort of weird DBA restriction? I don't understand why they would impose such a restriction.

And if you can't use explicit join syntax, have you tried the implicit join syntax?

Even if your admins aren't allowing you to use join syntax, have you tried a subquery?
Mar 5 '18 #10

twinnyfo
Expert Mod 2.5K+
P: 2,536
You know, Rabbit just reminded me of something he taught me a while back, but I got so caught up in the OP's original process that I forgot about it. The WHERE clause for the Insert Query should be as simple as this:

Expand|Select|Wrap|Line Numbers
  1. WHERE [BidTbl].[BidNum] IN (SELECT BidNum FROM 00001tbl_BID;) AND [AcctTbl].[AccountStatus] = 'A';
This will limit all BidNums to those in the Table.

Sometimes I am so blind!
Mar 5 '18 #11

P: 6
keeps stopping after line 21. i added the "CopiedFlag" field in the table.
Mar 5 '18 #12

twinnyfo
Expert Mod 2.5K+
P: 2,536
Look to Post #11 for a better solution. You should not have to create an IN statement at all. This should take care of all aspects of your problem without a CopiedFlag in the table.

I will, however, fix my code, as I forgot a line or two..... It's tough when I can't test your code myself.
Mar 5 '18 #13

P: 6
Almost there. thanks for the code. it did help out and i was able to get a work-around. i'm sure we can make it efficient but take a look and lets see if we can improve the code. And yes, our IT dept. is very strict on data access. i was working on another query but same concept.

Expand|Select|Wrap|Line Numbers
  1. Sub run_account()
  2. On Error GoTo EH
  3.     Dim strSQL      As String
  4.     Dim db          As DAO.Database
  5.     Dim RS          As DAO.Recordset
  6.     Dim strBidNum   As String
  7.     Dim strStart    As String
  8.     Dim strFinish   As String
  9.     Dim strInStmt   As String
  10.     Dim mySQL       As String
  11.     Dim mySQL2      As String
  12.  
  13.     Set db = CurrentDb()
  14.  
  15.     'Select the first 50 records from table that resides at local database
  16.     strSQL = "SELECT TOP 50 [Account Num] " & _
  17.         "FROM 00002tbl_ACT " & _
  18.         "Where Flag = 'N';"
  19.  
  20.     Set RS = db.OpenRecordset(strSQL, dbOpenDynaset)
  21.  
  22.     'Create the IN() Statement
  23.     Do While Not RS.RecordCount = 0
  24.         strStart = "In('"
  25.         strFinish = ")"
  26.         strBidNum = ""
  27.         With RS
  28.             .MoveFirst
  29.             Do While Not .EOF
  30.                 If strBidNum = "" Then
  31.                     strBidNum = ![Account Num] & "'"
  32.                 Else
  33.                     strBidNum = strBidNum & ",'" & ![Account Num] & "'"
  34.                 End If
  35.                 .MoveNext
  36.             Loop
  37.             .Close
  38.         End With
  39.         Set RS = Nothing
  40.  
  41.         strInStmt = strStart & strBidNum & strFinish
  42.  
  43.         'Query where tables are linked via ODBC. Does not allow direct link. Each table has a "ODBC Join Key" for behind the scene linkage
  44.         'Data queried are inserted to a local table
  45.         'Each ODBC table is has a field called ODBC Join Key. It's not necessary to understand the relationship between tables.
  46.         mySQL = "INSERT INTO 00002tbl_VNR ( [Account Num], [Account Start], [Service Type], [Year], [Month], " & _
  47.                 "[Num Days], [Car Quantity], [Net Amt] ) " & _
  48.                 "SELECT [- Account Table].[Account Num], [- Account Table].[Account Start], [- Product Table].[Service Type], " & _
  49.                 "Time.Year, Time.Month, Time.[Num Days], [- Car Table].[Car Quantity], [- Reveneu Table].[Net Amt] " & _
  50.                 "FROM (([- Account Table] INNER JOIN [- Revenue Table] ON [- Account Table].[ODBC Join Key] = [- Revenue Table].[ODBC Join Key]) " & _
  51.                 "INNER JOIN ([Time] INNER JOIN [- CAR Table] ON Time.[ODBC Join Key] = [- Car Table].[ODBC Join Key]) " & _
  52.                 "ON [- Revenue Table].[ODBC Join Key] = Time.[ODBC Join Key]) INNER JOIN [- Product Table] " & _
  53.                 "ON [- Car Table].[ODBC Join Key] = [- Product Table].[ODBC Join Key] " & _
  54.                 "WHERE ((([- Account Basic Detail].[Account Num]) " & strInStmt & ")" & _
  55.                 "AND ((Time.Year) In ('2016','2017','2018')));"
  56.  
  57.         '(1 of 2) Query to update local table to flag accounts used
  58.         'local table has two fields...the acount number and a Y/N field called Flag
  59.         'Current account in the in() stmt is flipped to Y
  60.         mySQL2 = "UPDATE 00002tbl_ACT SET [00002tbl_ACT].Flag = 'Y' " & _
  61.                  "WHERE ((([00002tbl_ACT].[Account Num]) " & strInStmt & "));"
  62.  
  63.  
  64.         With DoCmd
  65.             .SetWarnings False
  66.             .RunSQL mySQL
  67.             .RunSQL mySQL2
  68.             .SetWarnings True
  69.         End With
  70.  
  71.         '(2 of 2) Select the next top 50 accounts that <> Y
  72.         strSQL = "SELECT TOP 50 [Account Num] " & _
  73.         "FROM 00002tbl_ACT " & _
  74.         "Where Flag = 'N';"
  75.  
  76.         Set RS = db.OpenRecordset(strSQL, dbOpenDynaset)
  77.     Loop
  78.  
  79.     db.Close
  80.     Set RS = Nothing
  81.     Set db = Nothing
  82.     Exit Sub
  83. EH:
  84.     MsgBox "There was an error copying the records!  " & _
  85.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  86.     Exit Sub
  87. End Sub
Mar 5 '18 #14

twinnyfo
Expert Mod 2.5K+
P: 2,536
Cberos,

An elaborate construction is not needed. The only code needed is to build your SQL string using the WHERE clause found in Post #11. Execute that code and it should run as desired.

Have you tried that method yet?

This should be the extent of your code:

Expand|Select|Wrap|Line Numbers
  1. Sub run_bid()
  2. On Error GoTo EH
  3.     Dim mySQL       As String
  4.  
  5.     mySQL = "INSERT INTO 00002tbl_ACT " & _
  6.         "( [BidNum], [AcctNum], [AccountStartDate] ) " & _
  7.         "SELECT [BidTbl].[BidNum], [AcctTbl].[AcctNum], " & _
  8.         "[AcctTbl].[AccountStartDate] " & _
  9.         "FROM [BidTbl] INNER JOIN [AcctTbl] " & _
  10.         "ON [BidTbl].[ODBC Join Key] = [AcctTbl].[ODBC Join Key] " & _
  11.         "WHERE [BidTbl].[BidNum] IN " & _
  12.             "(SELECT BidNum FROM 00001tbl_BID;) " & _
  13.             "AND [AcctTbl].[AccountStatus] = 'A';"
  14.  
  15.     With DoCmd
  16.         .SetWarnings False
  17.         .RunSQL mySQL
  18.         .SetWarnings True
  19.     End With
  20.  
  21.     Exit Sub
  22. EH:
  23.     MsgBox "There was an error copying the records!  " & _
  24.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  25.     Exit Sub
  26. End Sub
Mar 6 '18 #15

Post your reply

Sign in to post your reply or Sign up for a free account.