Queries :: Use A Loop In A Query?

Jul 17, 2014

I have a query that includes fields from T_employee and fields from T_courses, which is courses taught by each employee per semester and T_ Additional, which is additional hours added to employees for each semester. The query groups by employee and has a calculation of the teachers total hours per course based on teaching hours and factors for marking and preparation and adds the additional hours from T_additional.

Each teacher cannot have more than 44 hours, so there are additional calculations that calculate the consequence of subtracting one course from the total hours. The record that is closest to 44 without going over is optimal. In some cases, subtracting one course is not enough to get the hours below 44 and an additional course will need to be subtracted and perhaps a 3rd an so on.

I would like to evaluate which course is the most optimal to subtract and if it is not enough, then to loop through and subtract the next course until the hours are below 44, but as close to 44 as possible.Would a Do loop be what I need? I need to output the total number of hours that were subtracted and the number of courses.

I tried creating a query based on this one, grouping by employee and under the NetHours in the totals row, the max nethours <44. This works but there are still a few records where the nethours is >44. I would like to use a Where in the Totals row and in the Criteria somehow indicate that I want the largest number that is less than 44.

View Replies


ADVERTISEMENT

Queries :: Simple Loop Through Excel Cells

Feb 25, 2014

I have been trying to understand which method to use for looping through excel cells and storing these into access tables. However, I am having difficulty with so little knowledge in vba.

I have lets say two tables (rows for each table are not fixed) in a worksheet and I want to loop through these rows and store each tables (PROJECT PLAN 1 and PROJECT PLAN 2) in a separate table in access.how to loop through the PROJECT PLAN 1 and PROJECT PLAN 2 in excel and store these in table1 and table2 in access

I have these in excel

B3 I have "PROJECT PLAN 1"
B4 COMPANY | C4 DESCRIPTION | D4 TIME
B5 Google | C5 aaa | D5 10
B6 Microsoft | C6 bbb | D6 11
B7 IBM | C7 ccc | D7 12

next row 8 is blank row and columns

B9 I have "PROJECT PLAN 2"
B10 COMPANY | C10 DESCRIPTION | D10 TIME
B11 Google | C11 aaa | D11 10
B12 Microsoft | C12 bbb | D12 11
B13 B14(merge cell) IBM | C13 ccc | D13 12
B13 B14(merge cell) IBM | C14 ccc | D14 12

This is the code I have so far:

Code:
Dim objXL As Object
Dim xlSht As Object
Dim xlWB As Object

[code]....

View 6 Replies View Related

Queries :: Loop To Create New Tables With Unique Field Values

Sep 20, 2013

I have a table - (Table A) that has 2 fields X and Y. I would like to write a query or script to make two new tables based on the unique values found in field X. In other words, all data where field X = 1 would be written to a new table called "1" and all data wehre field X =2 would be written to a new table called "2".I would like this done automatically.

Table A
Field X Field Y
1 a
1 b
1 c
1 d
2 a
2 b
2 c

View 5 Replies View Related

Insert Query Loop??

Feb 20, 2006

Hi I am creating a stock database and I have come across this problem:

I have a stock table, and I have successfully creted a query to insert a record with values selected in a form:

EG. In the form you can input a part no, comments and the quantity you want to add. I have created the stock system so that each individual item of stock is one record in the stock table.

What I can't seem to do is use the quantity field in the form to dictate how many times the form details is entered into the stock table.??

HELP

View 1 Replies View Related

Modules & VBA :: Run A Loop Through Each Record In Query?

Apr 30, 2015

Is it possible to run a loop through each record in query, but refresh the query in between loops?

The query I am looping needs to be updated before moving to the next record.

t can't figure out how to get the query to refresh before looping.

View 3 Replies View Related

General :: Loop Through Query For Information

Jun 18, 2014

I am using Access 2010. I have a database that on a form uses a multiselect listbox. That part works just fine. The list box is for selecting additional people to email. Now I have had no luck with returning just the email address that are in a hidden column (the persons actual name is seen and "selected"). The names come from a separate table and is used as a forgien key. On that same table are the indivuals email addresses. What I did was loop through to get all of the ID numbers I am getting from the list box (the ID numbers are stored in the table that the form is based on).

Once I have all of the ID Numbers I thought that maybe there was a way to retrieve all of the email address associated with the ID Numbers. This is what I have so far. I know that AllQuery returns the first email address from the list box. I just have no idea if the query is returning more than one record, or if it is how to then go to the next record. I have tried a few things with little to no success.

Code:
Dim ListItem As Variant
Dim AllItems As String
Dim AllQuery As String
For Each ListItem In Me.EmailAdditionEgineers.ItemsSelected
AllItems = AllItems & Me.EmailAdditionEgineers.ItemData(ListItem) & " or "
Next ListItem
AllItems = Left(AllItems, Len(AllItems) - 3)
AllQuery = DLookup("EmailAddress", "AdditionalEmailRequestQuery", "[ID] = " & AllItems) & ";"

View 11 Replies View Related

Forms :: Loop For Getting Information From Query Not Working

Sep 17, 2014

I am working on setting up a loop that will get information from a query. I can handle the rest of the code once I get the primary piece of information. Which at this point I can not get. I am trying to get to the next record of my query. For some reason I am stuck on just the first record and it is repeated the exact number of times equal to the number of records in the query. Here is what I have so far.

Code:
Dim FSO As Object
Dim FromPath As String
Dim FromPathCheck As String
Dim ToPath As String
Dim FinalDestination As String

[Code]....

Yes I do know that I am doubled up on the

Code:
rs.MoveNext

The reason is because with out this added rs it goes into an infinite loop. Need generating each value in the TRNumber field in the query.

View 7 Replies View Related

Modules & VBA :: How To Loop Update Query On All Records Of A Table

Sep 2, 2014

I've set a database which has a table in which there are 2 fields "Account" and "Total Accounts". I want to have the amount of total summation of accounts in "Total Accounts" field of each record, which is the result of summation of "Account" values in all previous records till the current one. In order to do this purpose, I copied the value of "Amount" field of each record into "Total Accounts" field of the same record, at first. Then, I tried to add the amount of "Total Accounts" field of every record with just the amount of "Total Accounts" of previous one to earn the actual total amount of that record. I found that I need a VBA loop to do this query for all records (except first record) and so I code it as below, but it has the Run-time error '424' : Object required and it seems that I am in a mistake in definition of strSQL variable:

Code:
Private Sub doDataSegm_Click()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset("Table1", dbOpenTable)

[Code] .....

View 3 Replies View Related

Modules & VBA :: Loop Through Query Results To Output Emails

Jan 29, 2015

I have a query that randomly selects records from my table:

SELECT TOP 6 *
FROM SM_Import
ORDER BY rnd(INT(NOW*id)-NOW*id);

I would like to loop through those results, and create emails

I can get one email formed (displayed) with the code below but don't know how to get both working together to display the (6) emails:

Private Sub Command3_Click()
Dim ol As Object
Dim CaSubject As String

[Code].....

View 4 Replies View Related

Modules & VBA :: Loop Through Table Or Query And Export To Excel

Jan 25, 2014

I am trying to export a table in access based on a unique field called Group_Name. Say my table has 100 records. 10 of those records belong to Group1, 10 belong to Group 2, and so on. What I want to do is export those groups individually to an excel file and have that file named somting like Group1_Premium Detail Report.

Here is the Code I have so far. I have a feeling I am close. When I run the code it does not seem to like strrsql2

Dim strsql As String
Dim strsql2 As String
Dim strfilename As String
Dim strpath As String
strpath = "C:UsersDesktopHome"

[Code] ....

View 6 Replies View Related

Modules & VBA :: Financial Database - Loop Query Until Empty

Oct 4, 2013

I'm an accountant and I'd like to build my own personal financial database in Access. One thing I like about MS Money is the 'Forecast Cashflow' feature, which is based on a schedule of recurring transactions, amounts, next due date, and frequency. I want to replicate this.

So, I created a dummy database for the sake of learning the code behind this process. I've got 2 tables (but no microphone.

1) tbl_InitialPoint (which is my schedule of recurring transactions, amounts, frequency (in days))
2) tbl_Register (where I want forecasted transactions to wind up)

I've got 2 saved queries:

1) qry_MaxDate (looks for any transaction @ 'tbl_InitialPoint' and finds the last date of that transaction in 'tbl_Register'
SELECT MAX(tbl_Register.PostDate) AS LastDate, tbl_InitialPoint.Description
FROM tbl_InitialPoint INNER JOIN tbl_Register ON tbl_InitialPoint.Description = tbl_Register.Description
GROUP BY tbl_InitialPoint.Description;

2) qry_InsertTransactions (populates 'tbl_Register')
INSERT INTO tbl_Register ( PostDate, Description, Amount )
SELECT qry_MaxDate.LastDate + tbl_InitialPoint.Frequency AS DateSeries, tbl_InitialPoint.Description, tbl_InitialPoint.Amount
FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description
WHERE qry_MaxDate.LastDate + tbl_InitialPoint.Frequency <= [Forms]![HomePage]![DateHorizon];

And I've got a form called 'HomePage' that has

1) A textbox where I input a date horizon (this is where I want the eventual 'Forecast Cashflow' to stop
2) A button to start the process

It seems to me that qry_InsertTransactions needs to be run over and over (looped) until the query is empty. And it seems to me that this can only be done via VBA (not sure if I could structure my SQL to avoid VBA in this case, but I need/want to learn the vba to accomplish this).

I'm having trouble getting VBA to run qry_InsertTransactions a single time. I'm pretty sure if I could get the thing to run once I could get it to run through a loop. So, here's what I came up with.

Public Sub InsertTransactionsBttn_Click()
Dim db As Database
Dim qdfNew As QueryDef
Dim qdfInsertTransactions As QueryDef
Set db = CurrentDb()

[Code] ....

View 11 Replies View Related

Modules & VBA :: Loop Fields Of Crosstab To Create Query

Nov 22, 2014

the below code works fine if i run a crosstab on a whole table however if i pass criteria to the crosstab e.g between [forms]![form]![txtstartdate] and [forms]![form]![enddate] it fails and returns null for every field? even though if set docmd.runquery "qryReductionByPhysician_Crosstab" this opens with the relevant data?

Code:
Set db = CurrentDb
Set qdf = db.QueryDefs("qryReductionByPhysician_Crosstab")
indexx = 0

[code].....

View 7 Replies View Related

Modules & VBA :: Loop Through A Query To Show Results On Form

Jun 28, 2015

I basically want to Loop through a query (or if not possible trough a table) and show the results in my form. For each record in the table I am doing additional checks when loading the form and showing that result accordingly. I do not want to do these checks in additional queries - I think it is more efficient doing it in the form directly. Here is the code I have:

Dim dbsSR As DAO.Database
Dim rstValQry As DAO.Recordset
Set dbsSR = CurrentDb
Set rstValQry = dbsSR.OpenRecordset("qry_val_tbl_ind_rec-rev_import")
With rstValQry
While (Not .EOF)

[Code] ....

The Loop seems to work because in debug it is going through it 3 times (the number of records I have in the query), but the result is always the same - it seems the above code is not checking / refreshing based on the individual query records.

View 14 Replies View Related

Modules & VBA :: Query Loop - Unable To Convert To Form Reference

Sep 9, 2013

I have a form with 10 combo-boxes. Once user selects a value in CB1- CB2 becomes visible and active. I am trying to run a dynamic query- where the selection of the combo-box is used to select a column from my table called "dbo_animals"

To elaborate: CB1 contains the following values

Code : elephant, giraffe, bufffalo, tiger, lion

Once the user selects elephant in CB1, CB2 becomes active and I select tiger next. So the query becomes like. The process can go on and the user can select up to 10 animals

Code : **SELECT elephant, tiger FROM dbo_animals**

Problem: I am able to create the query with string manipulation- Unfortunately due to the way the loop through controls is set up- The query is unable to convert the text into a reference. If I hard code it as

tempquery = "SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals" MsgBox(tempquery)

It looks like

Code : SELECT Elephant from dbo_animals

This is how I want. But, since i am looping through CB controls, I have it set up as

Code : tempquery = "SELECT" & " [Form]![Animal Finder]![CB" & i & "] FROM dbo_animals"

and this shows up as

Code : SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals

Thereby giving me an error saying that the reference is not valid and asking me for a parameter value.

This makes sense, since it is unable to evaluate the text as a reference.

How do I correct the text into a reference? Or How do I build a query with adjusting columns based off selections from a combo-box and loop through the comboboxes in the form while auto-updating the query?

View 4 Replies View Related

Modules & VBA :: Access 2010 / Loop Through Query To Assign A Value To A New Field?

Aug 16, 2013

Using Access 2010: I have a query with four fields: ORG_NAME, PERS_NAME_LAST, CountOfORG_NAME, and BdMbrCount. There are a couple hundred companies in the database with 1-7 people associated with each company. I need to number each person so that they have a number, 1-7 in the MemberCount field of my query.

I have my query connected to VBA code.

I have experimented with code that I have found on the forum, just to see if I could get something to work and I am getting “Undefined function ‘BdMbrCount’ in expression. I am trying to pass [ORG_NAME],[PERS_NAME_LAST] to my function and assign the value of BdMbrCount to a new field in my query, BdMbrCount.

Code:
Function BdMbrNumber()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourBdMbrsRRecognizedQry")
'Set rst = CurrentDb.OpenRecordset("SELECT [ORG_Name],[PERS_NAME_LAST],[CountofORG_NAME], [BdMbrCount] FROM YourBdMbrsRRecognizedQry")
Dim ORG_NAME As String

[code]....

View 14 Replies View Related

Modules & VBA :: Loop Through Query Results To Extract Email Addresses

Jun 27, 2013

I am trying to loop through a query results to extract email addresses from a query result.

I have the following code

NameCriteria = Forms![MainMenu]![tbl_Course_Details]![Course_Name].Value
DateCriteria = Forms![MainMenu]![tbl_Course_Details]![Course Date].Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String

[Code] .....

If i run this in access as a query then it returns results but when i run in there then it says no current Record and i cant work out why?

Someone on another post said that sql uses US date format so i tried converting to us date and still no results?

View 6 Replies View Related

Modules & VBA :: Loop Through Values In ComboBox / Set Query Criteria / Export To Excel

Jul 24, 2014

I am a relative newbie to VBA, and not very familiar with loops, but I need to add a loop to my function that exports a query with criteria contained in a bound ComboBox on a form. I've gotten my code to work fine without the loop, but I would like to export one file for each item "Team_ID" contained in the ComboBox without the user having to manually select and re-run the function each time. Here is what my code currently looks like:

Code:

Option Compare Database
Option Explicit
Public Function CreateQCChartsforReports() As Boolean
Dim qdf As DAO.QueryDef
Dim strSQLStatic As String
Dim BookName As String
Dim BookName2 As String
Dim intCounter As Integer
Dim cboCode As ComboBox

[code]....

View 1 Replies View Related

Modules & VBA :: Loop - Append Query To Work Referring To Quantity Value For One Record

Jul 29, 2015

I have a part table summary with various quantities 1-1000 and want to create a new table where part number repeats with quantity of 1 corresponding back to the sum. If part 123456=20 then this would repeat 20 x and each record Qty=1

Start with summary
--================
Part Qty
111000 2
222000 3

End result all Qty=1
--================
Part Qty
111000 1
111000 1
222000 1
222000 1
222000 1
--================

I stared with a loop and was able to get an append query to work referring to the quantity value (3) for one record from tbl_temp to tbl_main, but not really sure how to advance through many records.

For n = 1 To [Forms]![MainScreen]![Text7]
DoCmd.OpenQuery "qry_Update_Qty"
'DoCmd.GoToRecord , , acNewRec
Next n

maybe a do while or some other approach?

View 3 Replies View Related

Loop

Mar 14, 2007

Hello all, hope that you are having a Great Day!

I hopeing that someone could help me with a Loop problem. I am trying to create a directory on each name change. My code works for the first name, however, when I am trying to Loop through the table, it isn't working.

Here is my code

'MkDir "H:NewPrivateFolder"

On Error GoTo Err_cmbMkDir_Click

Dim DirName As String
Dim response As String
Dim folder As String


folder = "H:Projects"


'Forms("form1").Controls("list1").MultiSelect = 2
'DLookup("Officers_Name", "tblOfficers")
DirName = folder & DLookup("officers_name", "tblofficers")


Do
If Dir(DirName, vbDirectory) = "" Then


If MsgBox("OK to create folder!", vbOKCancel) = vbOK Then

MkDir DirName

Else
MsgBox "Create folder cancelled. Folder not created."
Exit Sub
End If
Else
MsgBox "The folder already exists..." & Chr(10) & "Please check the directories using Windows Explorer.", vbOKOnly
Exit Sub
End If
'response = MsgBox(DirName, vbOKOnly)

Loop

Exit_cmbMkDir_Click:
Exit Sub

Err_cmbMkDir_Click:
MsgBox Err.Description
Resume Exit_cmbMkDir_Click


End Sub

View 13 Replies View Related

While Loop Help

Jul 11, 2007

hello,

Let's say I have 10 variables (ID1, ID2, .........,ID10)
In java, I can use a while loop as follows to go through each variable

For example:
i=1
j=1
while (j<=10)
{
ID+i = 0 //this is some type of initialization of each var to 0
i = i + 1
j = J + 1
}
What would be the equivalent code in VBA, especially for "ID+i" ?? I use the '&' symbol but it concatenates everything meaning in the first loop goes to ID1 (which is correct), the second loop goes to ID12 (which is NOT correct), the third loop goes to ID13 (which is NOT correct either), and so on.

Thank you very much

View 2 Replies View Related

Do . . . Loop

Aug 4, 2005

I have a form that has a command button that requeries to the next days events. I want to skip days where there are no events. I created the Do Loop below but need it to stop after 10 days and return a MsgBox that says there are no further events.

I tried Do Until EOF but since the query is empty is fails. I have searched and read some other sources and did not find anything on limiting a loop the way that I need it. Or at least the way I understand it.

Any Ideas?

Forms!frmmain!txtSortdate = Forms!frmmain!txtSortdate + 1
If DCount("*", "qryflypages_old") <= 0 Then
Do
Forms!frmmain!txtSortdate = Forms!frmmain!txtSortdate + 1
Requery
Repaint
Loop
Else
Requery
Repaint
End If



Thanks RichB

View 1 Replies View Related

For Loop

Nov 15, 2005

Hi,

I have a question

email1 = "......@hotmail.com"
email2 = ".......@yahoo.com"
email3..
...

For i = 1 to num
msgbox email & i
next i

I run like this code, but it only show the numbers, it cannot be shown the email address.

For i = 1 to num
msgbox email & cstr(i)
next i

I tried it, but it's still NOT work

For i = 1 to num
msgbox cstr(email & cstr(i))
next i

Not work, too.

What's wrong with it?

Please let me know, thanks.

View 2 Replies View Related

Need Help With A Do Loop Containing EOF

Jun 19, 2006

I'm having a problem with my form where everytime I try importing a file into my database, I received an "overflow" message. It appears the problem is somewhere in my Do loop. The following shows what my code looks like right now:

Open txtCSVFileToUse For Input As #1
lvRandNum = Int((1000000 * Rnd) + 1)
lvINTERNAL_ID = lvRandNum
lpCntr = 1
Do While Not (EOF(1))

If lpCntr = 1 Then lpCntr = lpCntr + 1


Input #1, csvPro, csvJobName, csvJobNumber, csvShipper, csvConsignee, csvBillTo, csvServiceCode, csvCopies, csvWeight, csvUniqueContainerID, csvManifest, csvDueDate, csvTareWt, csvClass, csvVersion
With objRS
.AddNew
.Fields("K95_ID") = lvINTERNAL_ID
.Fields("K95_INTERNAL_ID") = lvINTERNAL_ID




.Fields("K95_PRONUM") = csvPro
.Fields("K95_JOBNAME_VERSION") = csvJobName
.Fields("K95_JOBID_MOTHER_PALLET") = csvJobNumber
.Fields("K95_SHIPPER") = csvShipper
.Fields("K95_CONSIGNEE") = csvConsignee
.Fields("K95_BILL_TO") = csvBillTo
.Fields("K95_TARIFF") = csvServiceCode
.Fields("K95_NUMBER_OF_COPIES") = csvCopies
.Fields("K95_WEIGHT") = csvWeight
.Fields("K95_UNIQUECONTAINERID_MPAL") = csvUniqueContainerID
.Fields("K95_MANIFESTNUM") = csvManifest
.Fields("K95_DUE_DATE") = csvDueDate
.Fields("K95_TARE_WT") = csvTareWt
.Fields("K95_CLASS") = csvClass
.Fields("K95_VERSION") = csvVersion
If EOF(1) = True Then Exit Do
lpCntr = lpCntr + 1
End With
SkipLine:
lpCntr = lpCntr + 1
Loop

I'm thinking it has something to do with with my counter and/or my EOF stuff. Any help would be much appreciated.

View 3 Replies View Related

Run SQL In VBA With A Loop?

Jun 8, 2006

Hi,

I am runnig a query via VBA where I have a variable defined for the criteria value.
However I do want to run this query several times for several values for the the variable.
These values are stored in a table.

Is it now possible to make sort of loop function in VBA that picks the first value in the table as criteria value, runs the query and then picks the second value in the table as criteria value, runs the query, etc, until all values have been picked?

Thanks,

Gurkentopf

View 6 Replies View Related

Loop Examples

Jan 11, 2006

Does anybody have any examples of loop, which will find data in a query and then show it as one line (string), listing all the data in one row.

Thanks inadvance

Alastair

View 2 Replies View Related

Loop Recordset

Jul 25, 2006

Hi folks,

I have created a recordset and what i want to do is check the field value "SiteRAG" to see if it matches some criteria and if so do some action where the field "SiteID" = the same as a label on a form.

Please see CAPS in middle of code:

Dim db As Object
Dim rs As Object
Dim intCount As Integer, intRecordCount As Integer, intID As Integer
Dim strSQL As String
Dim strRAG As Long
Dim fldItem As Field

strSQL = "SELECT tblSite.SiteID, tblSite.SiteRAG, tblSite.Active " _
& " FROM tblSite " _
& " WHERE (((tblSite.Active)=Yes));"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)


intRecordCount = 0
rs.MoveFirst

CHECK THE FIRST ROW FIELD SiteRAG & IF = TO CRITERIA THEN
DO SOMETHING BASED ON A LABEL MATCHING THE SiteID
ELSE
MOVE TO NEXT ROW

Loop
rs.Close
db.Close


Thanks for any help
Mark

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved