UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA Loop - Need A Qry To Run For Each Name In A Table Field, Any Version    
 
   
starleyrover
post May 19 2017, 08:18 AM
Post#1



Posts: 11
Joined: 24-April 17



Like me for instance. This discussion from 2012 is exactly what I'm looking for - I need to produce a random sample of 50 for each site code and export each to a separate Excel file (site codes are key fields in a separate table, but exist in the target). Thanks, Mark Sorry, this may need some explanation. I'm a newbie. I found a previous thread, and posted to it, when the op had found their own solution but had not seemingly posted it, even when asked to do so. An admin guru kindly posted my reply to that thread as a new topic.
This post has been edited by doctor9: May 19 2017, 09:22 AM
Go to the top of the page
 
doctor9
post May 19 2017, 09:34 AM
Post#2


UtterAccess Editor
Posts: 17,102
Joined: 29-March 05
From: Wisconsin


starleyrover,

> I need to produce a random sample of 50 for each site code and export each to a separate Excel file (site codes are key fields in a separate table, but exist in the target

You are trying to do three things:

1. Loop through a set of site codes
2. Create random samples of 50 somethings
3. Export the random samples of 50 somethings to Excel files

Based on the thread you replied to, you are asking for help with #1, is that correct? You'd perform #2 and #3 within the looping code. So, here's how you get started:

First, create a simple Select query that lists the unique site codes that you want to loop through. Once you've done that, copy the SQL code for that query onto the clipboard.

Second, create your subroutine along these lines:

CODE
Public Sub ExportRandomSomethingsForEachSiteCode()

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim strSQL As String
    
    Set dbs = CurrentDb
    
'   Query that lists each site code I need to use for exporting sets of 50 somethings
    Set strSQL = "SELECT intSiteCode FROM tblSiteCodes;"
    
    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveFirst
    
'   Loop through each site code
    While Not rst.EOF
        
'       Create an SQL statement that lists 50 somethings that are related to the
'       current record's site code.

'       Export the query defined by the SQL we just created.

        rst.MoveNext
    Wend
    
'   Cleanup
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing

End Sub


Don't worry about anything else at the moment - try to get just this bit working for now. Add a Debug.Print command inside the loop to verify that it's looping through the site codes properly. Once you've got that working, post what you have, and we'll move on to creating a random sample of 50 somethings based on the site code.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Jeff B.
post May 19 2017, 09:36 AM
Post#3


UtterAccess VIP
Posts: 9,694
Joined: 30-April 10
From: Pacific NorthWet


It would help us help you to have a description of your underlying data structure...

For instance, why do you need to examine "each field" in your table? (... or are you inspecting the values in a single field?)

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
starleyrover
post May 19 2017, 09:55 AM
Post#4



Posts: 11
Joined: 24-April 17



Dennis thanks for this, I had already started, roughly along these lines (but not for the export bit). It's nearly home time, but I'll let you know how I get on next week. Thanks again and have a good weekend. Mark
Go to the top of the page
 
starleyrover
post May 22 2017, 05:13 AM
Post#5



Posts: 11
Joined: 24-April 17



Hmmm, It's been a while since I have coded in VBA, and I'm quite rusty. I use two SQL strings (one for my source key, another for the records in the target query). My debug.print does not seem to be showing anything, and when I try to compile it gets stuck on the "Set strSQLkey =" (Compile error, Object required). There may also be a type error with my export. Ideally I'd like to use the clinic name and datestamp for the (Excel) file naming. Thanks for reading. Mark


CODE
Public Sub List50()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQLkey As String
Dim strSQLtarget As String
Set dbs = CurrentDb
Dim strFolder As String
Dim intI As Integer

' GUMCAD key field is a five character string
  
strFolder = "Q:\HPV\Projects - Post immunisation\MSM Implementation and Project Board\Uptake\Outputs\Tables\Export\"
Set strSQLkey = "SELECT gumcad FROM tlkpGUMCAD"
Debug.Print gumcad;
intI = 1
Set rst = dbs.OpenRecordset(strSQLkey)
rst.MoveFirst
    Do While Not rst.EOF
    strSQLtarget = "SELECT TOP 50 qtotGUMCADNoHPVCode.clinicname, " & _
     "qtotGUMCADNoHPVCode.patient_id, " & _
     "qtotGUMCADNoHPVCode.MinOfage, " & _
     " FROM qtotGUMCADNoHPVCode INNER JOIN on qtotGUMCADNoHPVCode!GUMCAD = '" & rsCurr!gumcad & "'"
     '" FROM qtotGUMCADNoHPVCode WHERE qtotGUMCADNoHPVCode!GUMCAD = rsCurr!GUMCAD"
      
  
   ' Let's worry about the random element until later (when decided)
    ' SELECT TOP 50 qtotGUMCADNoHPVCode.clinicname, qtotGUMCADNoHPVCode.patient_id, qtotGUMCADNoHPVCode.MinOfage, qtotGUMCADNoHPVCode.outcome
    ' FROM qtotGUMCADNoHPVCode INNER JOIN tlkpGUMCAD ON qtotGUMCADNoHPVCode.gumcad = tlkpGUMCAD.gumcad;

    'DoCmd.RunSQL strSQL
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strSQLtarget, strFolder + intI + ".xls", True
    ' + Format(Now, "yyyymmdd")
    rst.MoveNext
    intI = intI + 1
  Loop
'   Cleanup
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

End Sub
Go to the top of the page
 
doctor9
post May 22 2017, 08:26 AM
Post#6


UtterAccess Editor
Posts: 17,102
Joined: 29-March 05
From: Wisconsin


starleyrover,

Let's focus on doing one thing at a time to reduce frustration/confusion. Let's just start with this for now:

CODE
Public Sub List50()

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim strSQLkey As String
    Dim strSQLtarget As String
    Set dbs = CurrentDb
    Dim strFolder As String
    Dim intI As Integer
    
    strFolder = "Q:\HPV\Projects - Post immunisation\MSM Implementation and Project Board\Uptake\Outputs\Tables\Export\"
    
'   GUMCAD key field is a five character string
    Set strSQLkey = "SELECT gumcad FROM tlkpGUMCAD"
    
    
    intI = 1
    Set rst = dbs.OpenRecordset(strSQLkey)
    
    rst.MoveFirst
    Do While Not rst.EOF

        Debug.Print rst!gumcad;

'        strSQLtarget = "SELECT TOP 50 qtotGUMCADNoHPVCode.clinicname, " & _
         "qtotGUMCADNoHPVCode.patient_id, " & _
         "qtotGUMCADNoHPVCode.MinOfage, " & _
         " FROM qtotGUMCADNoHPVCode INNER JOIN on qtotGUMCADNoHPVCode!GUMCAD = '" & rsCurr!gumcad & "'"
         '" FROM qtotGUMCADNoHPVCode WHERE qtotGUMCADNoHPVCode!GUMCAD = rsCurr!GUMCAD"
          
      
       ' Let's worry about the random element until later (when decided)
        ' SELECT TOP 50 qtotGUMCADNoHPVCode.clinicname, qtotGUMCADNoHPVCode.patient_id, qtotGUMCADNoHPVCode.MinOfage, qtotGUMCADNoHPVCode.outcome
        ' FROM qtotGUMCADNoHPVCode INNER JOIN tlkpGUMCAD ON qtotGUMCADNoHPVCode.gumcad = tlkpGUMCAD.gumcad;
    
        'DoCmd.RunSQL strSQL
'        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strSQLtarget, strFolder + intI + ".xls", True
        ' + Format(Now, "yyyymmdd")
        rst.MoveNext
'        intI = intI + 1
      Loop
    
'   Cleanup
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing

End Sub


Does that display the correct gumcad values in the Immediate Window?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
River59
post May 22 2017, 08:50 AM
Post#7



Posts: 1,119
Joined: 7-April 10
From: Detroit, MI


doctor9, op stated that it errors (object required) on:
QUOTE
Set strSQLkey = "SELECT gumcad FROM tlkpGUMCAD"


He just needs: strSQLkey = "SELECT gumcad FROM tlkpGUMCAD"

Thought you may have missed this.


--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
starleyrover
post May 23 2017, 02:31 AM
Post#8



Posts: 11
Joined: 24-April 17



Dennis, thanks for this. The immediate window displays an unbroken string of all my GUMCAD values, obviously I was aiming for the first value (when I first enter the loop, and then the next, and so on). @River59, I agree, I had already changed this line to 'strSQLkey = "SELECT gumcad FROM tlkpGUMCAD" '.
Thanks for your patience.
Mark
Go to the top of the page
 
doctor9
post May 23 2017, 08:31 AM
Post#9


UtterAccess Editor
Posts: 17,102
Joined: 29-March 05
From: Wisconsin


Mark,

Okay, now that we've got the gumcad values working, let's move on to the query of random things. Your first attempt at writing the query will need a couple of tweaks. I'd recommend reading this suggestion from Leigh Purvis in a previous discussion where he shows how to randomize a query's contents. Try adapting that to your query to see if it works.

Basically, by selecting the Top 50 and ordering by a random number, you should be able to grab 50 random records. By inserting the use of the Randomize command as Leigh suggests, you won't get potentially repeating random numbers (i.e. if you roll the dice three times in your database and you get 2, 4 & 1 and then the next time you open the database and roll the dice three times you get 2, 4 & 1 again).

If you need help working with working with a User Defined Function, let me know.

To test it, add a Debug.Print strSQLtarget command (no trailing semicolon), then after the code runs, try copy/pasting the last three SQL statements into a new query to see the results to see if they are 50 records each time, and if they are random looking. Then run the subroutine again, and see if the last three SQL statements return DIFFERENT 50 records.

By the way, there's really no need to have separate variables for the two SQL strings. You can just use a generic strSQL variable for both. It won't cause any problems.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
starleyrover
post May 23 2017, 09:25 AM
Post#10



Posts: 11
Joined: 24-April 17



Dennis
Thanks for this. I have tackled the random element by creating a table from the query, adding a field and populating this new field with the expression int(n * RND(age) +1) where n is the total number or rows). This seems to give n random values and works fine when I sort on these random unique values (and nothing else).
Debug.Print rst!gumcad; is producing a concatenated string of my (five character) 26 site ids "5MX16R1K01R1K02RA7CQRBD05RBD48RCX70RD816RDZ20RGP75RGU46RJ121RJ122RJ517RM102
RM201RMK04RN325RN333RNZ02RQM60RQM61RQM62RTDEYRV3DVRW3MR" . I was thinking I need to isolate each site id, and move sequentially through the list, one by one. Am I wrong in thinking that my code isn't working because it never matches the long string above? It seems to me I need to isolate each site id but I may be missing something obvious.
Mark
Go to the top of the page
 
starleyrover
post May 23 2017, 09:30 AM
Post#11



Posts: 11
Joined: 24-April 17



Sorry, disregard that last post, I didn't read your reply in full. This is what happens when you try to do several things at once, apologies again, Mark
Go to the top of the page
 
starleyrover
post May 24 2017, 05:55 AM
Post#12



Posts: 11
Joined: 24-April 17



Dennis
Thanks for this. Your hints were invaluable, and I have made some progress To recap, I have tackled the random element by creating a table from the query, adding a field and populating this new field with the expression int(n * RND(age) +1) where n is the total number or rows). This seems to give n random values and works fine when I sort on these random unique values (and nothing else). I like to keep the SQL strings separate as I find it easier to unravel the code if I come back to it at a later point. I have got the code working and hope to recreate the steps I took so others might avoid pitfalls:
CODE
Public Sub List50()

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim qdfTemp As DAO.QueryDef
    Dim strSQL As String
    Dim strSQLtarget As String
    Set dbs = CurrentDb
    Dim strFull As String
    Dim strFolder As String
    strFolder = "Q:\HPV\Projects - Post immunisation\MSM Implementation and Project Board\Uptake\Outputs\Tables\Export\"
    ' dbs.QueryDefs.Delete "qryTemp"
    
'   GUMCAD key field is a five character string
    strSQL = "SELECT gumcad, clinicname FROM tlkpGUMCAD"
    
    Set rst = dbs.OpenRecordset(strSQL)
    
    rst.MoveFirst
    DoCmd.SetWarnings (WarningsOff)
    
    Do While Not rst.EOF
        'Debug.Print rst!gumcad
        strSQLtarget = "SELECT TOP 50 tblGUMCADNoHPVCode.gumcad, tblGUMCADNoHPVCode.clinicname, " & _
        "tblGUMCADNoHPVCode.patient_id, tblGUMCADNoHPVCode.MinOfage, tblGUMCADNoHPVCode.outcome " & _
        "FROM tblGUMCADNoHPVCode WHERE tblGUMCADNoHPVCode!GUMCAD = '" & rst!gumcad & "'" & _
        " ORDER BY tblGUMCADNoHPVCode.random DESC;"
        '
        'Debug.Print strSQLtarget
        'DoCmd.RunSQL strSQLtarget  needs to be an action query to run, add "into tblAudit " (before FROM...)
        Set qdfTemp = dbs.CreateQueryDef("qryTemp", strSQLtarget)
        strFull = strFolder & Trim(rst!clinicname) & ".xls"
        'Debug.Print strFull
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryTemp", strFull, True
        ' + Format(Now, "yyyymmdd")
        rst.MoveNext
        dbs.QueryDefs.Delete "qryTemp"
    Loop
    
'   Cleanup
    DoCmd.SetWarnings (WarningsOn)
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing

End Sub


I needed to turn the query into an action ( make table) to pass it to the docmd.runsql. The query then ran.

So I added the strSQLtarget to the Excel export line. The code then stopped at the DoCmd.TransferSpreadsheet (run time error `7871`'The table name you entered doesn't follow microsoft access object-naming rules'. In solve the problem, I found that I couldn't have a SQL statement as the source, looking elsewhere there was a suggestion here http://www.UtterAccess.com/forum/lofiversi...ex.php/f58.html . I followed this and added a queryDef to handle the dynamic SQL. I then had error 3323 (cannot have an action query as record source) - so I simply removed the action part (into tbl) from the SQL, and it seems to be working like a champ (though I fear I need to manually remove qryTemp before I run it).

Thanks to all for help and patience

Mark
Go to the top of the page
 
starleyrover
post May 25 2017, 07:21 AM
Post#13



Posts: 11
Joined: 24-April 17



The requirement has evolved: I need to open an existing Excel file and export the data to a given range (A7:E56), then save the Excel file with the clinic name (and ideally a record count suffix). The existing Excel file has some columns where entry is controlled (F:G). I'd also like to go to the next iteration if my query encounters zero records. I have already found various suggestions here (and elsewhere) but have yet to get them to work. Thanks for reading, Mark
Go to the top of the page
 
doctor9
post May 25 2017, 08:27 AM
Post#14


UtterAccess Editor
Posts: 17,102
Joined: 29-March 05
From: Wisconsin


starleyrover,

Okay, instead of exporting the data, we're going to need to open the existing workbook, and determine which worksheet we're writing data to. We'll still need that strSQLtarget string, but now we're going to open it as a Recordset object, and loop through each row, writing data to cells on the worksheet as we go. Do you have any experience with Recordsets or automating Excel from within Access VBA?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
starleyrover
post May 25 2017, 08:28 AM
Post#15



Posts: 11
Joined: 24-April 17



Sorry have just learnt that I do not need to implement the previous requests, so please disregard my last message. (I wish they'd make their mind up). Mark
Go to the top of the page
 
starleyrover
post May 25 2017, 08:34 AM
Post#16



Posts: 11
Joined: 24-April 17



Dennis, I was writing my apology as you were responding. The answer to both of your questions is no, but I have seen your other messages on this topic and had an idea that a different tack was needed. However I now need to get on with with something else, and will pick this up again if and when needed. Thanks again for your guidance, I did get places yesterday morning. Mark
Go to the top of the page
 
starleyrover
post May 25 2017, 08:34 AM
Post#17



Posts: 11
Joined: 24-April 17



Dennis, I was writing my apology as you were responding. The answer to both of your questions is no, but I have seen your other messages on this topic and had an idea that a different tack was needed. However I now need to get on with with something else, and will pick this up again if and when needed. Thanks again for your guidance, I did get places yesterday morning. Mark
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th May 2017 - 02:59 AM