locked
Need Help with Access 2007 SQL statements RRS feed

  • 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

     

    Thursday, November 27, 2008 8:38 PM

Answers

  • 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....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 28, 2008 8:51 AM

All replies

  • User-1199946673 posted

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

    Thursday, November 27, 2008 10:11 PM
  • 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.

    Thursday, November 27, 2008 10:23 PM
  • 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....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 28, 2008 8:51 AM