SQL Statement using Inner Join 2

[programma5]

I'm trying to write a recordset that will fill in data from 4 different tables into 1 flex grid. I need to use the inner join, but i don't think i have the correct sytax down. The sql is below. Any help would be much appreciated.

JS

strSQL = "select * from ((tblPerson inner join tblEnrollment2000 on tblPerson.EmployeeID = tblEnrollment2000.EmployeeID) inner join (tblEnrollment2000 inner join tblOfferings2000 on tblEnrollment2000.OfferingID = tblOfferings2000.OfferingID) inner join (tblOfferings2000 on tblSeminar.SeminarID = tblOfferings2000))"

 

[dvannoy]

What database are you using?

 

[programma5]

Microsoft Access 7.0

 

[SonOfEmidec1100]

Two probs - duplicate tables referenced and the final field .name is missing. I think this should be correct, you may need some brackets in access.

select * from tblPerson
inner join tblEnrollment2000 on tblPerson.EmployeeID = tblEnrollment2000.EmployeeID
inner join tblOfferings2000 on tblEnrollment2000.OfferingID = tblOfferings2000.OfferingID
inner join tblSeminar on tblSeminar.SeminarID = tblOfferings2000.SeminarID

 

[CCLINT]

"...WHERE DateField = " & Format$(Text1.Text,"\'mm-dd-yyyy\'&quot

In the future, please start a new thread for a off topic question, and not a post to an existing thread....

 

[programma5]

I tried the SQL and it was still screwed up. I tried it without brackets and with. This is the sql statement i have:

strSQL = "select * from ((tblPerson inner join tblEnrollment2000 on tblPerson.EmployeeID = tblEnrollment2000.EmployeeID) inner join (tblOfferings2000 on tblEnrollment2000.OfferingID = tblOfferings2000.OfferingID) inner join (tblSeminar on tblSeminar.SeminarID = tblOfferings2000.SeminarID))"


I appreciate the try SonofEmidec.

 

[SonOfEmidec1100]

I belive this is the correct syntax with brackets, but check for typos in field names

SELECT *
FROM (tblPerson INNER JOIN tblEnrollment2000 ON tblPerson.EmployeeID = tblEnrollment2000.EmployeeID) INNER JOIN (tblOfferings2000 INNER JOIN tblSeminar ON tblOfferings2000.SeminarID = tblSeminar.SeminarID) ON tblEnrollment2000.OfferingID = tblOfferings2000.OfferingID;

 

[CCLINT]

You will need to use a nested join if the relation between the tables are something like:


tblPerson tblEnrollment2000
EmployeeID===>EmployeeID tblOfferings2000
OfferingID=======>OfferingID tblSeminar
SeminarID=======>SeminarID

Here is the syntax for a nested JOIN:

SELECT Felds
FROM Table1 INNER JOIN
(Table2 INNER JOIN [( ]Table3
[INNER JOIN [( ]Tablex [INNER JOIN ...)]
ON Table3.Field3 CompareOperator Tablex.Fieldx)]
ON Table2.Field2 CompareOperator Table3.Field3)
ON Table1.Field1 CompareOperator Table2.Field2

 

[programma5]

How would I be able to pick and choose specific fields from the tables? The inner join worked perfectly and i thank you all for the help, i just need to file it down a bit.

Thanks

JS

 

[CCLINT]

Instead of
SELECT *

use

SELECT Table1.Field1, Table5.Field6

or

SELECT Table1.*, Table5.Field6