Need Help with Access 2007 SQL statements

Question

User-1938039701 posted

Hi

I never really had to write any statements for access before but now I am in a situations where I probably will have to do it. What I am trying to do is I am trying to retreive some data from my access database through datasets but I have to join my tables together.

 I am not sure really how to write the sql so I can access the data out of access.

This is how I would write one of my statements for sql sever 2005

 Select Students.FirstName, Students.LastName, Marks.AchievedMark
FROM Students
FULL JOIN Marks
ON Students.StudentID = Marks.StudentID

but every time I try to do this it does not work

 I get some error like this "IErrorInfo.GetDescription failed with E_FAIL(0x80005005)"

So I am not sure if they use different keywords.

So I need a list of all the sql stuff I can do with access and examples of it(especially the joins and nested joins since the statement I have will have to be joined to another table).

 

Thanks

 

Answer 1

User-1199946673 posted

Select Students.FirstName, Students.LastName, Marks.AchievedMark
FROM Students
INNER JOIN Marks
ON Students.StudentID = Marks.StudentID

Answer 2

User-1938039701 posted

 Ah thanks.

 I have another question how do you do place holders for access?

like I want to filter data based on what parameter name is passed in.

So I have this

 

SELECT     Students.StudentID, Students.FirstName, Students.LastName, Marks.AssignmentNum, Marks.AchievedMark, Assignments.TotalMarks,
                      Assignments.Weight
FROM         ((Students INNER JOIN
                      Marks ON Students.StudentID = Marks.StudentID) INNER JOIN
                      Assignments ON Marks.AssignmentNum = Assignments.AssignmentNum)
WHERE     (Students.StudentID = '@StudentID')

but I am not sure if it is the @StudentID since when I try to test out the data it does not ask me to input the parameter. So I am not sure if I am doing something wrong.

Answer 3

User-1199946673 posted

if you place the paramter betwwen quotes, it becomes a string, so skip th quotes!

Read this , be aware that OleDb parameter are not recognized by name, but bij their position so they must be added in the same order they appear in the SQL....