Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Smile Unanswered: SQL Subquery in access 2007

    Hi i am quite new to access and SQL, i am trying to master subqueries in access 2007, but even this query keeps coming up with Synrax error in the FROM Cause. i have looked at lots of web sites about subqueries and they all seem to be similar in syntax to this.
    I started with more complex ones and they all fail at the same point, syntax in FROM clause. ite driving me round the bend.
    i want to find 12 grader called jordan by finding first jordan then filtering for 12th grade.

    SELECT [sub query trial].[name]
    FROM [sub query trial]
    WHERE ((([sub query trial].[name])="Jordan"));

    this is simple data table
    ID name Grade
    1025 John 12
    1101 Haley 10
    1247 Alexis 11
    1304 Jordan 12
    1316 Austin 11
    1381 Tiffany 9
    1468 Kris 10
    1501 Jessica 11
    1510 Jordan 9
    1641 Brittany 10
    1661 Logan 12
    1689 Gabriel 9
    1709 Cassandra 9
    1782 Andrew 10
    1911 Gabriel 11
    1934 Kyle 12

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You posted your question in the MySQL Forum, which is for a server based database engine called MySQL.

    Your question seems to center around the client based Microsoft Access tool, and its associated Jet file handler.

    I'll move your thread/question so that it can get responses from the Microsoft Access users.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Although I don't understand why you would use a subquery for solving such a simple problem:
    Code:
    SELECT Tbl_data.ID, Tbl_data.name, Tbl_data.Grade
    FROM Tbl_data
    WHERE (Tbl_data.name = 'jordan') AND (Tbl_data.Grade = 12);
    You can try:
    Code:
    SELECT a.ID, a.name, a.Grade
    FROM ( SELECT Tbl_Data.ID, Tbl_Data.name, Tbl_Data.Grade 
             FROM Tbl_Data 
            WHERE Tbl_data.name= 'jordan' ) AS a
    WHERE a.Grade = 12;
    Note: Tbl_data is the name of the table in my examples.
    Have a nice day!

  4. #4
    Join Date
    Feb 2013
    Posts
    3

    Simple query

    Hi
    thanks for the response, yes the query is simple and there are other ways but i am trying to get my head around the syntax to make nested queries in a more complex manor.
    for example i have to find the average rating of a movie then adjust the realese year by 25 years for those movies with an avearge rating of 4 or more.
    i have the average rating query, and i have an update query sorted but i cannot get them to work in the same query. my rational was to find the movies with the high average rating and then modify only those records.
    query looks like this.

    UPDATE Movie LEFT JOIN rating ON Movie.Mid = rating.Mid SET Movie.[year] = "year"="year"+25

    (SELECT Movie.Title, Avg(rating.Stars) AS AvgOfStars, Movie.Year
    FROM Movie LEFT JOIN rating ON Movie.Mid = rating.Mid
    GROUP BY Movie.Title, Movie.Year
    HAVING (((((Avg([rating].[Stars]))))>3.9)));


    Table lay outs are like this
    Movie
    mID title year director

    Reviewer
    rID name

    Rating
    rID mID stars ratingDate
    thank you in advance for any advice, guidance you can offer.

    Paul

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A LEFT JOIN query doest not make sense here as all rows are returned from the table on the left of the relationship, i.e. you would be updating all rows.
    The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
    See: SQL LEFT JOIN Keyword

    If the rows you want to select can be identified by a query that does not have a one-to-one relationship with the table to be updated (e.g. a query that uses a GROUP BY clause), and provided that the original table has an Identity column (usually the primary key) and that the query can return values from that column (Identity values), you can always use a condition such as:
    Code:
    WHERE [Identity Column] IN ( SELECT [Identity Column] FROM... )
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •