Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

Looping in reports?

Hi....i have 8 fields on a report that all display a name.  I want to be able to print a data report that shows each person name at the top of the page that still shows the 8 names in the main body.

Does anyone have an idea of how this is achieved?
0
andrewl77
Asked:
andrewl77
  • 3
1 Solution
 
abdulhameedsCommented:
my frend you can use two methods
you can insert group in the report to the name filed and put the name page header
and the  other fields on the details body so you can have the name with its details


method 2 :
you can create two tables (temp master ) and (temp detail) from sql statment you can handel the problem

i hope this points can help you

and you dont say what is the report envierment you use


regards
0
 
andrewl77Author Commented:
I am using vb's datareport designer.

I have had problems in setting up groups in vb before....the code pasted below is what is used to retreive the information....can you suggest the changes?  The fields are the fieldump1, fieldump2, fieldump3, boundaryump1, boundaryump2, boundaryump3, goalump1, goalump2.

Private Sub Command1_Click()
    Dim conn As ADODB.Connection
    Dim rs1 As ADODB.Recordset
   
    ' Open a connection.
    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
        & "SERVER=192.168.100.101;database=umas;uid=umas;pwd=umas"
    conn.Open

    ' Open the Recordset
    Set rs1 = conn.Execute("select fixtures.*, (select teamname from clublist where fixtures.hteamno = clublist.id) as hometeam, (select teamname from clublist where fixtures.ateamno = clublist.id) as awayteam, (Select gradename from competitiongrades where fixtures.gradecode = competitiongrades.id) as gradename, (select leaguename from competitions where fixtures.leagueno = competitions.leagueno) as leaguename, (select groundname from grounds where fixtures.venue = grounds.groundid) as groundname, (select groundstreet from grounds where fixtures.venue = grounds.groundid) as groundstreet, (select groundtown from grounds where fixtures.venue = grounds.groundid) as groundtown, (select groundmelways from grounds where fixtures.venue = grounds.groundid) as groundmelways," & _
    " (select fieldno from appointments where fixtures.gameno = appointments.gameno) as fieldno, (select fieldump1 from appointments where fixtures.gameno = appointments.gameno) as fieldump1val, (select displayname from umpire where umpire.umpireno = fieldump1val) as fieldump1, (select homeph from umpire where umpire.umpireno = fieldump1val) as fu1home, (select mobileph from umpire where umpire.umpireno = fieldump1val) as fu1mobile, (select fieldump2 from appointments where fixtures.gameno = appointments.gameno) as fieldump2val, (select displayname from umpire where umpire.umpireno = fieldump2val) as fieldump2, (select homeph from umpire where umpire.umpireno = fieldump2val) as fu2home," & _
    " (select mobileph from umpire where umpire.umpireno = fieldump2val) as fu2mobile, (select fieldump3 from appointments where fixtures.gameno = appointments.gameno) as fieldump3val, (select displayname from umpire where umpire.umpireno = fieldump3val) as fieldump3, (select homeph from umpire where umpire.umpireno = fieldump3val) as fu3home, (select mobileph from umpire where umpire.umpireno = fieldump3val) as fu3mobile, (select boundaryump1 from appointments where fixtures.gameno = appointments.gameno) as boundaryump1val, (select displayname from umpire where umpire.umpireno = boundaryump1val) as boundaryump1, (select homeph from umpire where umpire.umpireno = boundaryump1val) as bu1home," & _
    " (select mobileph from umpire where umpire.umpireno = boundaryump1val) as bu1mobile, (select boundaryump2 from appointments where fixtures.gameno = appointments.gameno) as boundaryump2val, (select displayname from umpire where umpire.umpireno = boundaryump2val) as boundaryump2, (select homeph from umpire where umpire.umpireno = boundaryump2val) as bu2home, (select mobileph from umpire where umpire.umpireno = boundaryump2val) as bu2mobile, (select boundaryump3 from appointments where fixtures.gameno = appointments.gameno) as boundaryump3val, (select displayname from umpire where umpire.umpireno = boundaryump3val) as boundaryump3, (select homeph from umpire where umpire.umpireno = boundaryump3val) as bu3home," & _
    " (select mobileph from umpire where umpire.umpireno = boundaryump3val) as bu3mobile, (select goalump1 from appointments where fixtures.gameno = appointments.gameno) as goalump1val, (select displayname from umpire where umpire.umpireno = goalump1val) as goalump1, (select homeph from umpire where umpire.umpireno = goalump1val) as gu1home, (select mobileph from umpire where umpire.umpireno = goalump1val) as gu1mobile, (select goalump2 from appointments where fixtures.gameno = appointments.gameno) as goalump2val, (select displayname from umpire where umpire.umpireno = goalump2val) as goalump2, (select homeph from umpire where umpire.umpireno = goalump2val) as gu2home, (select mobileph from umpire where umpire.umpireno = goalump2val) as gu2mobile," & _
    " (select town from umpire where umpire.umpireno = fieldump1val) as fu1sub, (select town from umpire where umpire.umpireno = fieldump2val) as fu2sub, (select town from umpire where umpire.umpireno = fieldump3val) as fu3sub, (select town from umpire where umpire.umpireno = boundaryump1val) as bu1sub,(select town from umpire where umpire.umpireno = boundaryump2val) as bu2sub, (select town from umpire where umpire.umpireno = boundaryump3val) as bu3sub, (select town from umpire where umpire.umpireno = goalump1val) as gu1sub, (select town from umpire where umpire.umpireno = goalump2val) as gu2sub, (select note from globalnotes where fixtures.gamedate >= globalnotes.start and fixtures.gamedate <= globalnotes.enddate) as globalnote" & _
    " from fixtures where fixtures.gamecompleted = 0 and fixtures.leagueno = " & (league.ItemData(league.ListIndex)) & " And fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & " group by fixtures.gameno order by hometeam", , adCmdText)
   
    ' Connect the Recordset to the DataReport.
    Set rptumpapp.DataSource = rs1
    rptumpapp.WindowState = vbMaximized
    rptumpapp.Show vbModal

    rs1.Close
    conn.Close
End Sub
0
 
andrewl77Author Commented:
I have been searching and trying some things to make these sections work.  I have set the sections up in my report and all appears on there.

I have changed the last part of my code to look like this:
    ' Connect the Recordset to the DataReport.
    Set rptumpapp1.DataSource = rs1
    rptumpapp1.WindowState = vbMaximized
    rptumpapp1.Sections("section2").Controls("Text29").DataField = "goalump2"
    rptumpapp1.Sections("section4").Controls("Text38").DataField = "goalump1"
    rptumpapp1.Sections("section6").Controls("Text39").DataField = "fieldump1"
    rptumpapp1.Sections("section8").Controls("Text40").DataField = "fieldump2"
    rptumpapp1.Sections("section10").Controls("Text41").DataField = "fieldump3"
    rptumpapp1.Sections("section12").Controls("Text42").DataField = "boundaryump1"
    rptumpapp1.Sections("section14").Controls("Text43").DataField = "boundaryump2"
    rptumpapp1.Sections("section16").Controls("Text44").DataField = "boundaryump3"
    rptumpapp1.Show vbModal

I am now getting an error that my report sections do not match my datasource....any ideas?
0
 
andrewl77Author Commented:
Accepted the comment as answer and created new topic for the section problem!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now