Sql Loops?
Jan 15, 2005
Is it possible to have sequal loops of some kinda? The basic setup of my DB is a table named "EC Faculty 2005", field name "Status", and the status field can be either "Approved" "Disapproved" and "In Process". There is another field called "Term Start Date" which holds months. I have been trying to create a SQL or some kind of query that will go through each month Jan, Feb, Mar, and so on, and count the number of Approved, disapproved, and in process for each month. This is what I have so far. Code:SELECT (SELECT Count([Status]) FROM [EC Faculty 2005] WHERE Status='Approved') AS Appr, (SELECT Count([Status]) FROM [EC Faculty 2005] WHERE Status='Disapproved') AS Disappr, (SELECT Count([Status]) FROM [EC Faculty 2005] WHERE Status='In Process') AS In Proc FROM [EC Faculty 2005]; That gives me a total count of the 3 statuses. Is there a way to loop this to count each status for each month? I hope thats not too confusing.
View Replies
Jul 18, 2005
I am attempting to create a form for the automated printing of a series of reports. The form is tied to a query that captures Territory ID, Sales Rep ID, # of Reps in that Territory, Total # of Territories. Once completed, the code behind the form should:
1)Print the cover page report (Salesrep Sales Analysis – Cover);
2)For each of the territories, print a report for each sales rep (Salesrep Sales Analysis - A Rep) and once all the reps for that territory have been printed, print a summary report for that territory (Salesrep Sales Analysis - A Territory;
3)Print a combined report for all sales reps (Salesrep Sales Analysis - ALL by Month); and finally
4)Print the final page report (Salesrep Sales Analysis - Territory Totals).
I created the following code to accomplish that, but the results are in error:
1)The first rep for the first territory is being repeated (doesn’t seem to move off that first record at the correct time);
2)The final rep for the first territory is being skipped, and the first summary report is printed;
3)The second territory ends 1 rep short and the code moves into the 3rd territory without printing the summary for the 2nd territory;
4)The 1st rep of the 3rd territory prints, then the territory summary prints, then the 3rd territory continues, again stopping short of the final rep before moving to the 4th territory;
5)The two reps of the 4th territory (only 2 for that one) print, then the summary report for the 5th territory prints without printing the 4th territory summary or the report for the single rep for the 5th territory;
6)All three of the reps for the 6th territory print, then the summary for the 7th territory prints;
7)Three of the four reps for the 7th territory print, then I get an error message “You can’t go to the specified record” which ends the routine before the summary for that 7th territory and the last two reports are printed.
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
' Set Variables for Rep Count, Territory Count, Page Number and Date/Time
TotalReps = Me.CountOfSO_Rep
RemainingReps = TotalReps
Me.RepCount = RemainingReps
TotalTerritories = Me.CountOfTerritory
RemainingTerritories = TotalTerritories
Me.TerritoryCount = RemainingTerritories
PageNumber = 2
Me.PageNumber = PageNumber
Me.myTime = Now()
' Turn off action/warning messages
DoCmd.SetWarnings False
' Print Report Cover Page
DoCmd.OpenReport "Salesrep Sales Analysis - Cover", acViewNormal
DoCmd.GoToRecord , , acNext
' Loop through all the Territories - 01, 02, 04, 05, 06, 08, 09
Do While RemainingTerritories >= 1
' Loop through all Reps for the Current Territory
Do While RemainingReps >= 1
DoCmd.OpenReport "Salesrep Sales Analysis - A Rep", acViewNormal
DoCmd.GoToRecord , , acNext
RemainingReps = RemainingReps - 1
Me.RepCount = RemainingReps
Me.PageNumber = PageNumber + 1
Me.Repaint
Loop
' End of Rep Loop
DoCmd.OpenReport "Salesrep Sales Analysis - A Territory", acViewNormal
DoCmd.GoToRecord , , acNext
TotalReps = Me.CountOfSO_Rep
RemainingReps = TotalReps
Me.RepCount = RemainingReps
RemainingTerritories = RemainingTerritories - 1
Me.TerritoryCount = RemainingTerritories
Me.PageNumber = PageNumber + 1
Me.Repaint
Loop
' End of Territory Loop
' Print Territory Totals Report for the Final Territory
DoCmd.OpenReport "Salesrep Sales Analysis - A Territory", acViewNormal
' Print Totals Report for All Reps Combined
DoCmd.OpenReport "Salesrep Sales Analysis - ALL by Month", acViewNormal
' Print Totals by Territory Report - Final Report Page
DoCmd.OpenReport "Salesrep Sales Analysis - Territory Totals", acViewNormal
' Notify User that All Reports Have Been Printed
Beep
MsgBox "Salesrep Sales Analysis Reports have been sent to the printer.", vbOKOnly, ""
End Sub
I’ve spent a couple of days looking at this, trying different things, and am not getting satisfactory results. Can anyone find what I’m doing wrong here? Thanks very much for any help you can provide!
View 5 Replies
View Related
Oct 23, 2004
I was just wondering if anyone can help me?
I have successfully paged through a recordset on my page using the .recordCount, etc. methods, the only thing is I have 2 independent recordsets looping on the page, which when there was not paging involved this worked fine, however now I need to span the results of BOTH of these across pages, I just cannot seem to figure out how! I have one set of records paging fine, but the other either messes the page up, or shows on the first page of results and no more of the other recordset, etc.... so yeah that is basically the problem I am hitting, so I was just wondering is there any examples or ways you could tell me to do this??
Any help that can be offerered would be very much appreciated ).
View 3 Replies
View Related
Nov 19, 2014
Having problem with loops. The inner loop updates a table. The outer loop pulls the record number from the "tblChangeOrderTable_Edit_Count" and is assigned to strRecordID . The inner loop uses strRecordID to find the right record. I keep getting errors like (Object variable or With Block variable not set.)
Code:
Private Sub btnClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim db As Database, rs As Recordset, rs1 As Recordset2
Dim Criteria As String
Dim strAns1 As String
Dim strAns2 As String
Dim strCount1 As String
Dim strAns3 As String
[code]....
View 12 Replies
View Related
Apr 17, 2014
The idea is that I have a table with products. It is joined to another table that has each products ID and then a series of fields that correspond to each month of this year (so 12 fields). These fields contain how many of each product sold in that month. However some products did not appear in inventory until a few months into the year, so they have zero's for those months in which they didn't exist yet.
What I need to do is find the first month that each product went on sale, and pass that field back to my main table to do calculations with.
First I tried to do with with a query, but I ran into a road block and realized that maybe a query wasn't best as I likely needed a loop. So I started writing a function at that point... but it is obviously non functional.
As an example, if I had a product like this:
Product: X
SalesID: 1111
Price: 9.99
Month 1: 0
Month 2: 0
Month 3: 1582
Month 4: 2790
Month 5: 4501
Month 6: 4210
Then the idea of this function would be to look at Month 1, see if it contained a zero. if it did, move to month 2. If it doesn't, then send whatever that value is to a new field in the database.
So in the new table, I would have:
Product: X
SalesID: 1111
Price: 9.99
Month 1: 1582
Month 2: 2790
Month 3: 4501
Month 4: 4210
View 3 Replies
View Related
Jul 8, 2015
I am running an export function from a module1 that contains a loop nested within a second loop. Each loop is running through items in a separate combobox on a single form1. The outside loop goes through combo1 items and the inner loop goes through combo2 items.
My issue is that the value of combo1 determines what items are available in combo2 (values are tied to tables). I can get the combo2 values to update when a user changes the values in combo1 (using requery in the afterupdate property of the combo1). However, I do not want a user to change the values, and the code module1 is ignoring the requery. How to force the combobox to requery through code in a module?
View 8 Replies
View Related
Nov 22, 2014
I'm trying to create a compound interest form using loops and the following formula:
TotalVariable = TotalVariable + (TotalVariable * (RateVariable / 100 / 12))
I'm not sure whats wrong with my code and it's not working!
View 14 Replies
View Related
