Read values from a record and make an IN STATEMENT

[cberos]

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.

[gnawoncents]

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.

[twinnyfo]

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?

[cberos]

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.

Sub run_bid()
 
Dim rs As DAO.Recordset
Dim BID_NUM, Start, Finish, InStmt, mySQL As String
Set rs = CurrentDb.OpenRecordset("SELECT Distinct [BidNum] FROM 00001tbl_BID")
 
Start = "In('XXXXXX'"
Finish = ")"
 
If Not (rs.EOF And rs.BOF) Then
    Do Until rs.EOF
        BID_NUM = BID_NUM & ",'" & rs("BidNum") & "'"
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If
rs.Close
Set rs = Nothing
 
InStmt = Start & BID_NUM & Finish
 
mySQL = mySQL & "INSERT INTO 00002tbl_ACT ( [BidNum], [AcctNum], [AccountStartDate] ) "
mySQL = mySQL & "SELECT [BidTbl].[BidNum], [AcctTbl].[AcctNum], [AcctTbl].[AccountStartDate] "
mySQL = mySQL & "FROM [BidTbl] INNER JOIN [AcctTbl] ON [BidTbl].[ODBC Join Key] = [AcctTbl].[ODBC Join Key]"
mySQL = mySQL & "WHERE ((([BidTbl].[BidNum]) " & InStmt & ") "
mySQL = mySQL & "AND (([AcctTbl].[AccountStatus]) In ('A')));"
 
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True
 
End Sub

[twinnyfo]

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.

[cberos]

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.

[cberos]

Also, this is a monthly process for reporting purposes.

[twinnyfo]

I can only presume you have already tried this:

INSERT INTO 00002tbl_ACT ( [BidNum], [AcctNum], [AccountStartDate] ) 
SELECT [BidTbl].[BidNum], [AcctTbl].[AcctNum], [AcctTbl].[AccountStartDate] 
FROM ([BidTbl] INNER JOIN [AcctTbl] ON [BidTbl].[ODBC Join Key] = [AcctTbl].[ODBC Join Key]) 
INNER JOIN [00001tbl_BID] ON [BidTbl].[BidNum] = [00001tbl_BID].[BidNum] 
WHERE [AcctTbl].[AccountStatus]) = 'A';

It looks like your two tables 00001tbl_BID and 00002tbl_ACT are joinable....

[twinnyfo]

However, in the case that you cannot JOIN those two tables in such a way....

Sub run_bid()
On Error GoTo EH
    Dim strSQL      As String
    Dim db          As DAO.Database
    Dim RS          As DAO.Recordset
    Dim strBidNum   As String
    Dim strStart    As String
    Dim strFinish   As String
    Dim strInStmt   As String
    Dim mySQL       As String
 
    Set db = CurrentDb()
 
    'Clear the CopiedFlag
    strSQL = "SELECT CopiedFlag FROM 00001tbl_BID;"
    Set RS = db.OpenRecordset(strSQL, dbOpenDynaset)
    With RS
        If Not .RecordCount = 0 Then
            .MoveFirst
            Do While Not .EOF
                .Edit
                !CopiedFlag = False
                .Update
                .MoveNext
            Loop
        End If
        .Close
    End With
 
    'Select the first 50 records
    strSQL = "SELECT TOP 50 BidNum " & _
        "FROM 00001tbl_BID " & _
        "WHERE Not CopiedFlag;"
    Set RS = db.OpenRecordset(strSQL, dbOpenDynaset)
 
    'Create the IN() Statement
    Do While Not RS.RecordCount = 0
        strStart = "In('"
        strFinish = ")"
        strBidNum = ""
        With RS
            .MoveFirst
            Do While Not .EOF
                If strBidNum = "" Then
                    strBidNum = !BidNum & "'"
                Else
                    strBidNum = strBidNum & ",'" & !BidNum & "'"
                End If
                .Edit
                !CopiedFlag = True
                .Update
                .MoveNext
            Loop
            .Close
        End With
        Set RS = Nothing
 
        strInStmt = strStart & strBidNum & strFinish
 
        mySQL = "INSERT INTO 00002tbl_ACT " & _
            "( [BidNum], [AcctNum], [AccountStartDate] ) " & _
            "SELECT [BidTbl].[BidNum], [AcctTbl].[AcctNum], " & _
            "[AcctTbl].[AccountStartDate] " & _
            "FROM [BidTbl] INNER JOIN [AcctTbl] " & _
            "ON [BidTbl].[ODBC Join Key] = [AcctTbl].[ODBC Join Key] " & _
            "WHERE [BidTbl].[BidNum] " & strInStmt & _
            " AND [AcctTbl].[AccountStatus] = 'A';"
 
        With DoCmd
            .SetWarnings False
            .RunSQL mySQL
            .SetWarnings True
        End With
 
        Set RS = db.OpenRecordset(strSQL, dbOpenDynaset)
    Loop
 
    db.Close
    Set RS = Nothing
    Set db = Nothing
 
    Exit Sub
EH:
    MsgBox "There was an error copying the records!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
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!

[Rabbit]

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?

[twinnyfo]

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:

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!

[cberos]

keeps stopping after line 21. i added the "CopiedFlag" field in the table.

[twinnyfo]

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.

[cberos]

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.

Sub run_account()
On Error GoTo EH
    Dim strSQL      As String
    Dim db          As DAO.Database
    Dim RS          As DAO.Recordset
    Dim strBidNum   As String
    Dim strStart    As String
    Dim strFinish   As String
    Dim strInStmt   As String
    Dim mySQL       As String
    Dim mySQL2      As String
 
    Set db = CurrentDb()
 
    'Select the first 50 records from table that resides at local database
    strSQL = "SELECT TOP 50 [Account Num] " & _
        "FROM 00002tbl_ACT " & _
        "Where Flag = 'N';"
 
    Set RS = db.OpenRecordset(strSQL, dbOpenDynaset)
 
    'Create the IN() Statement
    Do While Not RS.RecordCount = 0
        strStart = "In('"
        strFinish = ")"
        strBidNum = ""
        With RS
            .MoveFirst
            Do While Not .EOF
                If strBidNum = "" Then
                    strBidNum = ![Account Num] & "'"
                Else
                    strBidNum = strBidNum & ",'" & ![Account Num] & "'"
                End If
                .MoveNext
            Loop
            .Close
        End With
        Set RS = Nothing
 
        strInStmt = strStart & strBidNum & strFinish
 
        'Query where tables are linked via ODBC. Does not allow direct link. Each table has a "ODBC Join Key" for behind the scene linkage
        'Data queried are inserted to a local table
        'Each ODBC table is has a field called ODBC Join Key. It's not necessary to understand the relationship between tables.
        mySQL = "INSERT INTO 00002tbl_VNR ( [Account Num], [Account Start], [Service Type], [Year], [Month], " & _
                "[Num Days], [Car Quantity], [Net Amt] ) " & _
                "SELECT [- Account Table].[Account Num], [- Account Table].[Account Start], [- Product Table].[Service Type], " & _
                "Time.Year, Time.Month, Time.[Num Days], [- Car Table].[Car Quantity], [- Reveneu Table].[Net Amt] " & _
                "FROM (([- Account Table] INNER JOIN [- Revenue Table] ON [- Account Table].[ODBC Join Key] = [- Revenue Table].[ODBC Join Key]) " & _
                "INNER JOIN ([Time] INNER JOIN [- CAR Table] ON Time.[ODBC Join Key] = [- Car Table].[ODBC Join Key]) " & _
                "ON [- Revenue Table].[ODBC Join Key] = Time.[ODBC Join Key]) INNER JOIN [- Product Table] " & _
                "ON [- Car Table].[ODBC Join Key] = [- Product Table].[ODBC Join Key] " & _
                "WHERE ((([- Account Basic Detail].[Account Num]) " & strInStmt & ")" & _
                "AND ((Time.Year) In ('2016','2017','2018')));"
 
        '(1 of 2) Query to update local table to flag accounts used
        'local table has two fields...the acount number and a Y/N field called Flag
        'Current account in the in() stmt is flipped to Y
        mySQL2 = "UPDATE 00002tbl_ACT SET [00002tbl_ACT].Flag = 'Y' " & _
                 "WHERE ((([00002tbl_ACT].[Account Num]) " & strInStmt & "));"
 
 
        With DoCmd
            .SetWarnings False
            .RunSQL mySQL
            .RunSQL mySQL2
            .SetWarnings True
        End With
 
        '(2 of 2) Select the next top 50 accounts that <> Y
        strSQL = "SELECT TOP 50 [Account Num] " & _
        "FROM 00002tbl_ACT " & _
        "Where Flag = 'N';"
 
        Set RS = db.OpenRecordset(strSQL, dbOpenDynaset)
    Loop
 
    db.Close
    Set RS = Nothing
    Set db = Nothing
    Exit Sub
EH:
    MsgBox "There was an error copying the records!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

[twinnyfo]

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:

Sub run_bid()
On Error GoTo EH
    Dim mySQL       As String
 
    mySQL = "INSERT INTO 00002tbl_ACT " & _
        "( [BidNum], [AcctNum], [AccountStartDate] ) " & _
        "SELECT [BidTbl].[BidNum], [AcctTbl].[AcctNum], " & _
        "[AcctTbl].[AccountStartDate] " & _
        "FROM [BidTbl] INNER JOIN [AcctTbl] " & _
        "ON [BidTbl].[ODBC Join Key] = [AcctTbl].[ODBC Join Key] " & _
        "WHERE [BidTbl].[BidNum] IN " & _
            "(SELECT BidNum FROM 00001tbl_BID;) " & _
            "AND [AcctTbl].[AccountStatus] = 'A';"
 
    With DoCmd
        .SetWarnings False
        .RunSQL mySQL
        .SetWarnings True
    End With
 
    Exit Sub
EH:
    MsgBox "There was an error copying the records!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub