I'm doing a query report for a list of personnel with
single and/or multiple permits. The report page offers
the option of sending the data to a printable page. My
report print worked fine when it was listing every
permit, but I have been asked to output the report so
that permits are grouped under their owner's name,
with the name only displaying once per set of owned
permits.
I have succeeded in getting the query to output in the
requested format, but here's where this gets
embarassing: In order to make the printable report
display correctly, i.e., with the same-size page
margins, and grouped results not rolling over to the
next page (I can't use CSS) I used code for loops that
I found in a Macromedia forum. Code that I, um (cough,
cough) don't really understand. My goal is to have as
many complete groups of permits display on a page as
possible, with the next set going onto the next page,
but I think that some groups are printing out
incompletely. Plus the header is printing out halfway
down the page, and so on.
Here's the code for the page. I am hoping that someone
can explain to me just what, specifically, the "Loop
through the query" section of this code does, and if
it will accomplish what I want. Right now it seems
like that code is being ignored, because when I
changed the MaxRows value in that section, nothing
happened. But when I changed the MaxRows value in the
cfoutput query statement -- <cfoutput query =
"PermitSearch" group="SSN" startrow="#i#" MaxRows = 6>
-- which is not in the "Loop through the query"
section, the output changed. Do I need another loop
statement since I'm grouping results?
Hope this post is understandable . . .
Thanks,
Laura
==============================
Laura Schlee
DML Associates
cell 703-622-0363
ofc 202-685-1360
<!--- Get list of permits from database--->
<cfquery name="PermitSearch"
datasource="CMISPersonnel">
Select Personnel.SSN, Personnel.FirstName,
Personnel.MiddleInitial, Personnel.LastName,
Personnel.Zip, Personnel.SpaceNumber,
Vehicles.TagNumber, Vehicles.DODDecalNumber,
Vehicles.PermitNumber
from Personnel, Vehicles
where Personnel.PersonnelKey = Vehicles.PersonnelKey
<!--- Search by last name --->
<cfif Session.ParkingSearch.LastName is not "">
and LastName like
'%#Session.ParkingSearch.LastName#%'
</cfif>
<!--- Search by zip code --->
<cfif Session.ParkingSearch.Zip is not "">
and Zip = '#Session.ParkingSearch.Zip#'
</cfif>
<!--- Search by permit number --->
<cfif Session.ParkingSearch.PermitNumber is not "">
and PermitNumber =
'#Session.ParkingSearch.PermitNumber#'
</cfif>
<!--- Search by assigned parking space number --->
<cfif Session.ParkingSearch.SpaceNumber is not "">
and SpaceNumber =
'#Session.ParkingSearch.SpaceNumber#'
</cfif>
<!--- Search by car license plate --->
<cfif Session.ParkingSearch.TagNumber is not "">
and TagNumber = '#Session.ParkingSearch.TagNumber#'
</cfif>
<!--- Search by DOD decal number --->
<cfif Session.ParkingSearch.DODDecalNumber is not
"">
and DODDecalNumber =
'#Session.ParkingSearch.DODDecalNumber#'
</cfif>
order by LastName, FirstName, MiddleInitial, SSN
</cfquery>
<!-- Loop through the query results -->
<cfset RowsPerPage = 6>
<cfparam name=this.startRow default=1 type="numeric">
<cfset endRow = min(this.startRow + RowsPerPage - 1,
PermitSearch.RecordCount)>
<cfset startRowNext = endRow + 1>
<cfset startRowBack = this.startRow - rowsPerPage>
<cfset MaxRows = 6>
<cfparam name = "start" default = "1">
<cfloop from="#start#" to="#PermitSearch.RecordCount#"
step="#RowsPerPage#" index="i">
<!-- Display the results of the loop -->
<center>
<table width="75%" border="0">
<tr>
<td colspan="3"><div align="center">
<p> </p>
<p><font color="##000068" size="2"
face="Verdana"><b>Permit Search Results:
<cfoutput>#PermitSearch.RecordCount#</cfoutput>
permits found<br>
Use the print button on your browser to print
this report<br>
<a href="" to
permit search page</a></b></font></p>
</div>
<p> </p>
</td>
</tr>
<cfoutput query="PermitSearch" group="SSN"
startrow="#i#" MaxRows = 6>
<tr>
<td width="36%"><font color="##000068" size="2"
face="Verdana"><b>Name:</b></font>
<font color="##000068" size="2" face="Verdana">
#FirstName# #MiddleInitial#<br>
#LastName#</font>
</td>
<td width="31%"><br><font color="##000068"
size="2" face="Verdana"><b>SSN: </b></font>#SSN#</td>
<td width="33%"><br><font color="##000068"
size="2" face="Verdana"><b>Zip Code:</b></font>
<font color="##000068" size="2" face="Verdana">
#Zip#</font>
</td>
</tr>
<tr>
<td colspan="3">
<table width="100%">
<cfoutput>
<tr>
<td width="36%"> <br></td>
<td width="31%"><font color="##000068" size="2"
face="Verdana"><b>Permit Number:<br></b></font><font
color="##000068" size="2" face="Verdana">
#PermitNumber# </font></td>
<td width="33%"><font color="##000068" size="2"
face="Verdana"><b>Space Number:<br></b></font><font
color="##000068" size="2" face="Verdana">
#SpaceNumber#</font></td>
</tr>
<tr>
<td width="36%"> <br></td>
<td width="31%"><font color="##000068" size="2"
face="Verdana"><b>Tag Number:<br></b></font><font
color="##000068" size="2" face="Verdana"> #TagNumber#
</font></td>
<td width="33%"><font color="##000068" size="2"
face="Verdana"><b>Decal Number:<br></b></font><font
color="##000068" size="2" face="Verdana">
#DODDecalNumber#</font></td>
</tr>
<tr>
<td width="36%"> </td>
<td width="31%"> </td>
<td width="33%"> </td>
</tr>
</cfoutput>
</table>
</td>
</tr>
<cfset nextstart = start + MaxRows>
</cfoutput>
</cfloop>
</table>
__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
