Catch-all Join statement

[vb_dba]

You would expose all fields from all 5 tables through the view, so that the users could select which fields they wanted to include on the report. Say I wanted to create a view on 2 tables:

Create View vw_Report
As

Select t1.recID
     , t1.fname
     , t1.lname
     , t2.dept_id
     , t2.dept_name
From table1 t1 Inner Join table2 t2 On t1.dept_id = t2.dept_id

Then in your calling application, the users could select any of the fields listed in the Select clause of the view and you could build your report statement like:

Dim sSql As String
 
sSql = "Select lname, dept_name From vw_Report Where lname ='blah'"

For your report, you would need to list all fields from all five tables so that they are exposed via the view for your end users. The View is only there to eliminate the need to perform all of the table joins in your client application.