Creating Data Reports from Joined Tables (more than 2 tables) in Visual Basic

[Ted1]

How would i relate more than 2 related tables in creating data reports?Is there such way to do this?

 

[Train]

jeeze this has been a while since ive done VB reports, but heres some info

the VB 6 report plugin is FULL of bugs, and MS even admits it, they should have never dropped Crystal reports. They are actaully dropping it in VB 7 (AKA Visual Studio .NET edition)

Anyways, youll need to figure out some SQL to link up two tables, make that SQL statement as a command in the data environment, then point your report to that command.

lets say you have a table called customers(fields: ID, Name, Address), and a table called sales(Fields: ID, CustomerID, ProductID), and you want to list all the customers and the sales for each one. heres the SQL:

Select Customers.*, Sales.ID, Sales.ProductID
From Customers, Sales
Where Customers.ID = Sales.CustomerID
Group By Customers.ID

you will get a recordset that looks like this:

1 | Joe Customer | 123 Main | 356
1 | Joe Customer | 123 Main | 225
1 | Joe Customer | 123 Main | 987
2 | Mike Nobody | 235 Central | 223
2 | Mike Nobody | 235 Central| 356

make sure you set your report to have a group by, so that it only shows each customer once, but lists the products they bought under each one

 

[Ted1]

Thanks, Train