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: 3
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,076
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,691
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: 3
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 Today, 05:13 AM
Post#5



Posts: 3
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 Today, 08:26 AM
Post#6


UtterAccess Editor
Posts: 17,076
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 Today, 08:50 AM
Post#7



Posts: 1,114
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
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd May 2017 - 08:11 PM