A question on looping

# Thread: A question on looping

1. Junior Member
Join Date
Dec 1969
Posts
16

## A question on looping

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## There's no magic here...

If you need three levels of totals, you need three levels of "accumulators."<BR><BR>Simplified example:<BR><BR>&#060;%<BR>SQL = "SELECT District, Facility, medicaidAmount ...."<BR>Set RS = yourConn.Execute( SQL )<BR><BR>total_medicaid = 0<BR>district_medicaid = 0<BR>facility_medicaid = 0<BR><BR>curDistrict = ""<BR><BR>Do Until RS.EOF<BR> If RS("District") &#060;&#062; curDistrict Then<BR> ... change of district...output district header<BR> curDistrict = RS("District") &#039; change which is current<BR> curFacility = "" &#039; and of course we&#039;ll have new facility header<BR> End If<BR> If RS("Facility") &#060;&#062; curFacility Then<BR> ... change of facility...output facility header<BR> curFacility = RS("Facility") &#039; change which is current<BR> End If<BR> ... output info on one facility ...<BR> facility_medicaid = facility_medicaid + medicaidAmount &#039; accumulate<BR> ... other accumulation<BR><BR> &#039; here&#039;s the tricky part:<BR> RS.MOVENEXT <BR><BR> doFacilitySum = False<BR> doDistrictSum = False<BR> If RS.EOF Then<BR> doFacilitySum = True<BR> doDistrictSum = True<BR> ElseIf RS("District") &#060;&#062; curDistrict Then<BR> doFacilitySum = True<BR> doDistrictSum = True<BR> ElseIf RS("Facility") &#060;&#062; curFacility Then<BR> doFacilitySum = True<BR> End If<BR><BR> If doFacilitySum Then<BR> ... output a totals line for this facility ...<BR> &#039; then accumulate one level up<BR> district_medicaid = district_medicaid + facility_medicaid<BR> facility_medicaid = 0 &#039; reset for next facility<BR> End If<BR> If doDistrictSum Then<BR> ... output a totals line for this district ...<BR> &#039; then accumulate one level up<BR> total_medicaid = total_medicaid + district_medicaid<BR> district_medicaid = 0 &#039; reset for next district<BR> End If<BR>Loop<BR><BR>... output a totals line for all districts ...<BR><BR>%&#062;<BR><BR>

3. Junior Member
Join Date
Dec 1969
Posts
16

## Here's where the magic comes in......

I think I may have forgot to mention the hard part:<BR><BR>The loop you sent back here will give me a totals line for all instances of medicaid within the district. That is one part of it. However, the important part, is that I write one line per date with the totals. So, day 1 would have to be the totals for medicaid for all facilities in the district on that day only(Not the entire date range). Then day 2, all totals for day 2 only, all facilities in district. etc. etc.<BR><BR>The thing is, that is somewhat outside the loop function, since one loop would have to end and reset before the other started, and I have to accumulate data across those loops on a per day basis. Is this even possible?<BR><BR>Thanks,<BR>Steve

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Ever heard of arrays???

So it&#039;s easy enough.<BR><BR>How many days (max) would this report be for?<BR><BR>Say one year? <BR><BR>And then you want to know the first date to be reported. Say 1/1/2003, okay?<BR><BR>And then you do this. New stuff in yellow.<BR><BR>&#060;% <BR>[hl="yellow"]Dim dates_medicaid(365) &#039; big enough for a year[/hl] <BR>[hl="yellow"]CONST start_date = #1/1/2003#[/hl]<BR><BR>SQL = "SELECT District, Facility, medicaidAmount ...." <BR>Set RS = yourConn.Execute( SQL ) <BR><BR>total_medicaid = 0 <BR>district_medicaid = 0 <BR>facility_medicaid = 0 <BR><BR>curDistrict = "" <BR><BR>Do Until RS.EOF <BR> If RS("District") &#060;&#062; curDistrict Then <BR> ... change of district...output district header <BR> curDistrict = RS("District") &#039; change which is current <BR> curFacility = "" &#039; and of course we&#039;ll have new facility header <BR> End If <BR> If RS("Facility") &#060;&#062; curFacility Then <BR> ... change of facility...output facility header <BR> curFacility = RS("Facility") &#039; change which is current <BR> End If <BR> ... output info on one facility ... <BR> facility_medicaid = facility_medicaid + RS("medicaidAmount") &#039; accumulate <BR> ... other facility accumulations ...<BR>[hl="yellow"] curDate = DateValue( RS("reportDate") )<BR> offset = curDate - startDate &#039; or could use DateDiff function<BR> dates_medicaid(offset) = dates_medicaid(offset) + RS("medicaidAmount")[/hl]<BR><BR> &#039; here&#039;s the tricky part: <BR> RS.MOVENEXT <BR><BR> doFacilitySum = False <BR> doDistrictSum = False <BR> If RS.EOF Then <BR> doFacilitySum = True <BR> doDistrictSum = True <BR> ElseIf RS("District") &#060;&#062; curDistrict Then <BR> doFacilitySum = True <BR> doDistrictSum = True <BR> ElseIf RS("Facility") &#060;&#062; curFacility Then <BR> doFacilitySum = True <BR> End If <BR><BR> If doFacilitySum Then <BR> ... output a totals line for this facility ... <BR> &#039; then accumulate one level up <BR> district_medicaid = district_medicaid + facility_medicaid <BR> facility_medicaid = 0 &#039; reset for next facility <BR> End If <BR> If doDistrictSum Then <BR> ... output a totals line for this district ... <BR> &#039; then accumulate one level up <BR> total_medicaid = total_medicaid + district_medicaid <BR> district_medicaid = 0 &#039; reset for next district <BR> End If <BR>Loop <BR>... output a totals line for all districts ... <BR><BR>[hl="yellow"]&#039; **********************************<BR>&#039; output the totals by date<BR>&#039;<BR>For daynum = 0 To UBound(dates_medicaid) &#039; for every slot in the array<BR> theDate = CDate( start_date + daynum )<BR> Response.Write theDate & ": " & dates_medicaid(daynum) & "&#060;br/&#062;"<BR> ... do same for other dates_XXX arrays ...<BR>Next<BR>[/hl]<BR>%&#062; <BR><BR>I just realized that I gave you the code to get the by-date totals for all districts, not per district as you wanted.<BR><BR>So just move the code at the end there up to where the per-disctrict master total is displayed. Be sure to zero out the arrays after displaying the totals.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## WHY are you writing this in ASP??????

SURELY this isn&#039;t something that needs updating every 13 seconds!<BR><BR>Why not just produce the report from VB or from some good report generating program? And then just publish the results as HTML if that&#039;s what you need?<BR><BR>And thinking about it more, I think I would probably *not* use the array accumulation stuff for the totals-by-day. I&#039;d just use a separate query.<BR><BR>SELECT District, DateReported, SUM(Medicaid) AS dates_medicaid, SUM(...)<BR>FROM ...<BR>GROUP BY District, DateReported<BR>ORDER BY District, DateReported<BR><BR>And now, in the main code, when you reach the point of reporting the totals for the district, you grab the relevant records from this query and display them as the by-date for the district.<BR><BR>But again, why in ASP??? This is going to generate *BIG* reports, and showing big reports online is not usually the best of ideas.<BR><BR>

6. Junior Member
Join Date
Dec 1969
Posts
16

## RE: WHY are you writing this in ASP??????

Are you recommending here I create a seperate Recordset for every date in the array? this is fed by a date range on the search page. typically reports will not be more than 30 days at a time, and no more than 30 facilities. There are only 6 districts, so the reports really aren&#039;t that big. I tried the array idea and got lost in it, really doesn&#039;t make sense to me(Not experienced enough in this I suppose)<BR><BR>I&#039;m not familiar with these report generators and This is on a very limiting budget.

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## No, no...

*TWO* recordsets. Total.<BR><BR>One that is producing the RS to be used in the code I orignally showed. You use it to get the detail lines for the facilities in each district and for all districts. Just like I showed.<BR><BR>But then the *second* recordset does what I suggested:<BR><BR>SQL = "SELECT District, DateReported, Sum(medicaid) AS sum_medicaid, ... " _<BR> & " FROM table" -<BR> & " WHERE DateReported BETWEEN #4/1/2003# AND #4/3/2003#" _<BR> & " GROUP BY District, DateReported"<BR>Set RS2 = conn.Execute( SQL )<BR><BR>You can put as many SUM(xxx) AS sum_xxx in there as you need/want.<BR><BR>Then this table will look like this <BR><BR>District -- DateReported -- sum_medicaid -- sum_xxx<BR> 1 -- 4/1/2003 -- 3487.88 -- 192.95<BR> 1 -- 4/2/2003 -- 1922.12 -- 0.00<BR> 1 -- 4/3/2003 -- 1.95 -- 223.87<BR> 2 -- 4/1/2003 -- etc.<BR><BR>See? The same kind of table you are already working with, but instead of one line per facility per day, you get one line *per day* per district, with all the sums in place!<BR><BR>So after dumping out the district sum from the other table, you just dump out all the record from this table for the matching district number!<BR><BR>In other words, if you just did the totals for district 1, you would do this:<BR> Do While True &#039; forever loop, because...<BR> If RS2.EOF Then Exit Do &#039; we jump out for two reasons<BR> If RS2("District") &#060;&#062; curDistrict Then Exit Do<BR> ... here you just dump one day&#039;s sums for the curDistrict! ...<BR> RS2.MoveNext<BR> Loop<BR><BR>Simple as that.<BR><BR>Much easier than keeping the sums in arrays.<BR><BR>

8. Junior Member
Join Date
Dec 1969
Posts
16

## Aggregate function Problems

I tried the theory here, and it works perfect as a query in access. giving me exactly the data I need. now I know exactly what I need to do to get this done(and I promise this is the last leg of the code :-) ) <BR>However, when I try the code theory above, I get an error:<BR><BR>&#060;i&#062;[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression &#039;strDate&#039; as part of an aggregate function.&#060;/i&#062;<BR><BR>this is my SQL statement stripped down to only what is needed.<BR><BR>RS.Source = "SELECT strDate, SUM(Private) AS sum_private, SUM(Medicaid) AS sum_medicaid, SUM(Medicare) AS sum_medicare, SUM(Veterans) AS sum_veterans, SUM(Hospice) AS sum_hospice, SUM(Other) AS sum_other, SUM(Medicaid_Hospice) AS sum_medicaid_hospice, DistrictID, SUM(Totals) AS sum_totals FROM WKLYCensus_TBL WHERE DistrictID IN (" & selDistrict & ") AND strDate Between #" & Date1 & "# AND #" & Date2 & "# ORDER BY DistrictID ASC"<BR><BR><BR><BR>it appears to me since strDate has no aggregate command on it, it errors out. Do you have any Ideas, or is there a special way to handle a date when it is part of an aggregate function?<BR><BR>Thanks,<BR>Steve

9. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: Aggregate function Problems

ORDER BY DistrictID ASC, strdate

10. Junior Member
Join Date
Dec 1969
Posts
16

## RE: Aggregate function Problems

I added the additional sort and it still errors with the same response from before.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•