473,804 Members | 4,518 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

VBA Function to loop through records on one table to query another

23 New Member
Ok, this is a tough one. I need to query “tblRawData” where “fldID” equals “fldLoop” in “tblLoop” and append the results into “tblResults”. If I were to do this exclusively in SQL, it would look something like this:

INSERT INTO tblResults ( FldID, SRCTNE, SRYRNE, SRSER2, SRFMLY )
SELECT FldID, SRCTNE, SRYRNE, SRSER2, SRFMLY
FROM tblRawData, tblLoop
where tblRawData.FldI D = tblLoop.fldLoop ;

Also, you can see this illustrated in my attached database in the “qryNormal” object.

Here’s where is get’s extremely difficult. The real “tblRawData” that I’m querying has over 6 million records, AND they are NOT indexed. Unfortunately there’s nothing I can do about that since I’m linking to an AS/400 table via ODBC. So this means Access/Jet will rad all 6 million recods to find the ones I want, which means it will time out long before it finishes pulling the data. So joining the tables in a simple query, like illustrated above, is not going to be possible for me.

Since “tblLoop” contains the values that I want to limit my search to in “tblRawData”, I can manually copy one value from “tblLoop” and paste it in my “where clause” as criteria for just querying “tblRawData” without the joins. When I do this, results are returned in a matter of seconds. This is illustrated in the “qryManual” in my attached database or see SQL below:

INSERT INTO tblResults ( FldID, SRCTNE, SRYRNE, SRSER2, SRFMLY )
SELECT FldID, SRCTNE, SRYRNE, SRSER2, SRFMLY
FROM tblRawData
WHERE FldID='001799'


I suppose that’s not a big deal when I only have 12 records in “tblLoop” to compare. But my real tblLoop could have up to 100,000 records (which isn’t as bad as 6 million, ha!). So needless to say, I want to avoid manually running an append query 100,000 times.

I think the solution lies within some sort of VBA function that will loop through “tblLoop” for “qryManual’s” where clause. If you see the attached Excel document how I have it diagrammed, it will make more sense. Also, I believe this is an example of what I’m trying to accomplish (see below), but it’s someone else’s query and code. I’m not sure how to make it work for my purposes, or if it’s what I need at all. Thanks for any help on this!!!

SQL
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_PIO_DATA.Series, LaborRate([Plant]) AS Labor, [Piocount]*LTSLookup([Vehicle],[PioCode])*LaborRate([Plant]) AS LaborTotal
  2. FROM tbl_PIO_DATA
VBA
Expand|Select|Wrap|Line Numbers
  1. Function LaborRate(Plant)
  2.  
  3.     Dim db As DAO.Database
  4.     Dim rec As Recordset
  5.  
  6.     Set db = CurrentDb()
  7.     Set rec = db.OpenRecordset("Lookup_LaborRate", dbOpenDynaset)
  8.  
  9.     rec.MoveFirst
  10.   While rec.EOF <> True
  11.     If rec!Plant = Plant Then
  12.         LaborRate = rec!Labor_Rate
  13.         rec.MoveLast
  14.     End If
  15.     rec.MoveNext
  16.   Wend
  17.  
  18. End Function
  19.  
  20. Function LTSLookup(Vehicle, PIOCode)
  21.  
  22.     Dim db As DAO.Database
  23.     Dim rec As Recordset
  24.  
  25.     Set db = CurrentDb()
  26.     Set rec = db.OpenRecordset("2007_LABOR_TIME", dbOpenDynaset)
  27.  
  28.     rec.MoveFirst
  29.  
  30.   While rec.EOF <> True
  31.  
  32.     If PIOCode = rec!CODE Then
  33.          If rec![Vehicle Code] = "*" Then
  34.             LTSLookup = rec!LTS
  35.             rec.MoveLast
  36.          ElseIf rec![Vehicle Code] = Vehicle Then
  37.             LTSLookup = rec!LTS
  38.             rec.MoveLast
  39.          End If
  40.     End If
  41.     rec.MoveNext
  42.  
  43.   Wend
  44.  
  45.  
  46. End Function
  47.  
  48. Function LTSExclude(Vehicle, PIOCode)
  49.  
  50.     Dim db As DAO.Database
  51.     Dim rec As Recordset
  52.  
  53.     Set db = CurrentDb()
  54.     Set rec = db.OpenRecordset("2007_LABOR_TIME", dbOpenDynaset)
  55.  
  56.     rec.MoveFirst
  57.  
  58.   While rec.EOF <> True
  59.  
  60.     If PIOCode = rec!CODE Then
  61.          If rec![Vehicle Code] = "*" Then
  62.             LTSExclude = rec!Exclude
  63.             rec.MoveLast
  64.          ElseIf rec![Vehicle Code] = Vehicle Then
  65.             LTSExclude = rec!Exclude
  66.             rec.MoveLast
  67.          End If
  68.     End If
  69.     rec.MoveNext
  70.  
  71.   Wend
  72.  
  73.  
  74. End Function
Attached Files
File Type: zip Example Files.zip (251.5 KB, 367 views)
Apr 6 '10 #1
7 10276
Lysander
344 Recognized Expert Contributor
You don't have to do it manually, you could open tblLoop as a recordset and set up a loop
Expand|Select|Wrap|Line Numbers
  1. While not rsLoop.EOF
  2.   strSQL="INSERT INTO tblResults ( FldID, SRCTNE, SRYRNE, SRSER2, SRFMLY )"
  3.   strSQL=strSQL & " SELECT FldID, SRCTNE, SRYRNE, SRSER2, SRFMLY
  4. FROM tblRawData"
  5.   strSQL=strSQL & " WHERE FldID='" & rsLoop!fldLoop & "'
  6.  db.execute(strSQL);dbfailonerror
  7.  rsLoop.movenext
  8. WEND
  9.  
or something like that.
Apr 6 '10 #2
KPR1977
23 New Member
Lysander, thanks! I bet this will work. I created a function for it below but I'm getting an error on this line of code:
db.execute(strS QL);dbfailonerr or

And it's asking me to define rsLoop (maybe because I have Option Explicit declared???). Anyhow, I'm a beginner with VBA, so I'm probably asking a real dumb question. Thanks for any ideas on how to make this work... =)
Expand|Select|Wrap|Line Numbers
  1. Public Function TestLoop()
  2.  
  3. While Not rsLoop.EOF
  4.   strSQL = "INSERT INTO tblResults ( FldID, SRCTNE, SRYRNE, SRSER2, SRFMLY )"
  5.   strSQL = strSQL & " SELECT FldID, SRCTNE, SRYRNE, SRSER2, SRFMLY " & _
  6.                     "FROM tblRawData"
  7.   strSQL = strSQL & " WHERE FldID='" & rsLoop!fldLoop & "' "
  8.  
  9.  db.execute(strSQL); dbfailonerror
  10.  
  11.  rsLoop.MoveNext
  12. Wend
  13.  
  14.  
  15. End Function
Apr 6 '10 #3
Lysander
344 Recognized Expert Contributor
Wow, fast response, give a few seconds to check it out.

ok, me bad, I did not give all the info, did not realise you were new to VBA

oops, that should be a comma, not a semi colon. I'll constuct the full function, actually, it should be a sub as it is not returning a value, in Acces and post it in a few minutes

AND ALWAYS HAVE OPTION EXPLICT DECLARED, it can be a lifesaver.
Apr 6 '10 #4
Lysander
344 Recognized Expert Contributor
ok, this compliles in Access 2003 but of course, I cant test it as I dont have those tables

Expand|Select|Wrap|Line Numbers
  1. Public Function TestLoop()
  2. On Error GoTo TestLoop_Err
  3. Dim rsLoop As Recordset
  4. Dim strSQL As String
  5. Dim db As Database
  6.  
  7. Set db = CurrentDb
  8. Set rsLoop = db.OpenRecordset("Select * from tblLoop;")
  9.  
  10. rsLoop.MoveFirst
  11. While Not rsLoop.EOF
  12.   strSQL = "INSERT INTO tblResults ( FldID, SRCTNE, SRYRNE, SRSER2, SRFMLY )"
  13.   strSQL = strSQL & " SELECT FldID, SRCTNE, SRYRNE, SRSER2, SRFMLY FROM tblRawData"
  14.   strSQL = strSQL & " WHERE FldID='" & rsLoop!fldLoop & "' "
  15.  
  16.   db.Execute (strSQL), dbFailOnError
  17.  
  18.  rsLoop.MoveNext
  19. Wend
  20. rsLoop.Close
  21. Set rsLoop = Nothing
  22. Set db = Nothing
  23.  
  24. TestLoop_Exit:
  25.    Exit Function
  26. TestLoop_Err:
  27.    MsgBox Err.Description & " in TestLoop"
  28.    Resume TestLoop_Exit
  29. End Function
  30.  
  31.  
Apr 6 '10 #5
KPR1977
23 New Member
I need to tell you the same thing I've told ADezii...please extend your hand and give yourself a great big firm handshake of appreciation from myself!!! Your code works great!!! It took about 15 minutes for it to run through 40,000 records in tblLoop. Thanks again!!! =)
Apr 6 '10 #6
Lysander
344 Recognized Expert Contributor
Glad it worked and that I could be of help, its what this site is all about:)
Apr 6 '10 #7
KPR1977
23 New Member
I love this site!!!!
Apr 6 '10 #8

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

Similar topics

3
2490
by: John Pastrovick | last post by:
I use a function, myrandomPIN (), to generate random PIN numbers. The following sql query updates records with the SAME PIN number but. I want to generate DIFFERENT pin numbers for every record. The function is ok but I can't figure out how to run it individually for each record. In other words I do not know the correct syntax to use UPDATE in a loop (if necessary) so that a different call to the function is done every time or ecah...
12
2411
by: jason | last post by:
Access 2000: I have a customer-inventory table I need to loop through and compile a list of all the inventory items the customer is tracking. The problem I am finding is that a simple loop will pull out the customer details each time their is an inventory item listed....I need to get the customer out ONCE and list his items....is there an elegant way to do this: Here is the table: Cust-Invent Table
20
2772
by: Steve Jorgensen | last post by:
Hi all, I've just finished almost all of what has turned out to be a real bear of a project. It has to import data from a monthly spreadsheet export from another program, and convert that into normalized data. The task is made more difficult by the fact that the structure itself can vary from month to month (in well defined ways). So, I used the SQL-centric approach, taking vertical stripes at a time so that, for instance, for each...
6
8167
by: Sven Pran | last post by:
Probably the answer is there just in front of me only awaiting me to discover it, but: 1: I want to build a query that returns all records in one table for which there is no successful "join" in another table but I have not found what the field criteria should look like? 2: And if/when I succeed I should further like to build a new record (with
4
2977
by: Kurt | last post by:
I'm using the fConcatChild function posted at http://www.mvps.org/access/modules/mdl0004.htm to return a field from the Many table of a 1:M relationship into a concatenated string. The function puts everything in a comma separated format. In my case, it returns a list of authors: Borman, W., Oppler, S., White, L., I'd like to change the function so the last record in the string is preceded by an ampersand instead of a comma, as in:
52
6360
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible variations(combination of fields), - then act on each group in some way ...eg ProcessRs (oRs as RecordSet)... the following query will get me the distinct groups
5
2418
by: marcsirois | last post by:
I have an SQL Query that looks like this. I simplified it for the purpose of this example, but it's still a runnable query SELECT TOP 1 FundedPositions.PositionNumber AS , (select top 1 assign_i from dbo_assign a where a.scenario_i=1 and a.assign_i = TestMyFunction(FundedPositions.PositionNumber))AS
8
6506
by: SaltyBoat | last post by:
Needing to import and parse data from a large PDF file into an Access 2002 table: I start by converted the PDF file to a html file. Then I read this html text file, line by line, into a table using a code loop and an INSERT INTO query. About 800,000 records of raw text. Later, I can then loop through and parse these 800,000 strings into usable data using more code. The problem I have is that the conversion of the text file, using a...
11
3689
by: shriil | last post by:
Hi I have this database that calculates and stores the incentive amount earned by employees of a particular department. Each record is entered by entering the Date, Shift (morn, eve, or night) and the 'employee name'. There is another table which assigns an ID to the Shifts, i.e. 1,2 and 3 for morn, eve & night shifts respectively. From the mother table, the incentive is calculated datewise for each employee as per his shift duty. In...
1
4782
by: nrtyme | last post by:
Hello, I need to add several new records to Table2 from Table1. Table2 contains a field called that needs to be the previous maximum value of incremented by 1. Below is my code but i keep getting the error "MyDatabase" can't find the field '|' referred to in your expression. Please help, I am going crazy here :)!
0
9579
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10332
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10320
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10077
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9150
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7620
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5521
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5651
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2991
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.