How to create reports with data from multiple tables?

[jeboy]

How do I create reports (using crystal reports) with data from multiple tables?
I created a Dataset with two tables in it and choosed that Dataset in the Database Expert wizard's Project Data->ADO.NET DataSets on the Add/Remove Database menu of the crystal report designer.
I drag some fields from that two tables to the crystal report designer.
Now, I create a query to the two tables then I fill the Dataset with rows from that query and set the CrystalReport.SetDataSource() to my Dataset, I also set the CrystalReportViewer.ReportSource to my CrystalReport. But the CrystalReportViewer didnt display the data.
It works when I query one table only.
Here's the code:
Dim Rpt As New CrystalReport1()

dsStudentAccount.Clear()
daStudentAccount.SelectCommand.CommandText = "SELECT * FROM student " & _
"INNER JOIN account ON student.StudentNumber = account.StudentNumber"
daStudentAccount.Fill(dsStudentAccount)
Rpt.SetDataSource(dsStudentAccount)
CrystalReportViewer1.ReportSource = Rpt

 

[Berry-tan]

I noticed that you're trying to join two tables. But before I make a suggestion...

What's the entity relationship between tables "Account" and "Student" -- does a student have more than one account or does an account belong to more than one student?

 

[DavidT_macktool]

Select command

Verify your select command text, it appears that you are not selecting anything from the accounts table.

 

[jeboy]

thats 1 is to many relationship

One student have several account

 

[jeboy]

This is what Im trying to do

How to create reports in crystal reports with multiple data from two interelated table?

e.g.
Table1: Customer
CustomerID Name
1 Jebb Domingo
2 Jennylyn Corpuz

Table2: Account
AccountID CustomerID Date Debit Credit
1 1 7/25 1000 0
2 1 7/30 0 500
3 2 10/10 1500 0
4 2 10/15 0 800

SAMPLE REPORT
Name: Jebb Domingo

Date Debit Credit
7/25 1000 0
7/90 0 500

------------------------------------------------------

Name: Jennylyn Corpuz

Date Debit Credit
10/10 1500 0
10/15 0 800


Note:Is this possible in crystal reports? If not, is there any way to do this. My purpose for this report is for printing batch of account.

I use Visual Studio .Net 2003, .Net Framework 1.1

 

[lingsn]

I think that's possible, and I think there are a few ways, take a look on the sample (1 of the example)

 

[DavidT_macktool]

SubReports

It is not necessary, but you could look into subReports.
Format your main report grouped by customer name.
On the detail line create a subreport based on Account.

Subreports can be a very usefull tool, and I have several posts in this forum that refer to subReports.

Good luck.

 

[jeboy]

Thank you very much guys! You are great help!