Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    1

    Unanswered: Newbie needs help with homework due today

    Hi Folks,
    Please be gentle as this newbie is in a beginners SQL class and is stuck on the homework assignment. I would be very grateful for any help I can get.

    System: MS Access2000

    Problem: To write an SQL statement that will write the results of a UNION query to a new table in my database.

    Where am I at? I have written the UNION query & it does return the results I expect. When I modify the query (by adding INTO Newtable) to write the result set to the new table, I get an error, "An action query cannot be used as a row source"

    Code I'm using:
    SELECT Employees_TBL.FirstName, Employees_TBL.LastName, JobTitle_TBL.JobTitle, Employees_TBL.Salary
    INTO Newtable
    FROM Employees_TBL, JobTitle_TBL
    WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode AND JobTitle_TBL.Status = 'Exempt'
    UNION
    SELECT Employees_TBL.FirstName, Employees_TBL.LastName, JobTitle_TBL.JobTitle, Employees_TBL.Salary
    FROM Employees_TBL, JobTitle_TBL
    WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode AND JobTitle_TBL.Status = 'Non-exempt';

    Additional Info: If I just do one part of the compound query, I can write records to Newtable with no problem.

    HEre is what my instructor says on the matter:
    I've had some questions about how to integrate the UNION query with the SELECT...INTO statement. So here's some syntax information. I hope it helps.

    In simple terms, the syntax for the SELECT ... INTO is

    SELECT fieldlist INTO newtablename FROM recordsource

    Where fieldlist has the list of new field names for your table. You will need to make sure that the recordsource returns the same number of fields.

    newtablename is the name you want the new table to have

    recordsource is a valid table or query that returns a valid recordset to match fieldlist. If you are using a query, then you would enclose the query in parentheses. The recordsource could be as complex as needed to get you the records you want to add. It could even be a UNION query!

    Example:

    SELECT ItemName, LunchPrice INTO LunchMenu
    FROM (SELECT EntreeName, ItemCost*2.5 FROM RecipeList WHERE LunchFlag=1)


    I seem to be having a problem with syntax because the query works without the INTO part and the writing of records works if I don't try to use the UNION SELECT statement.

    Any ideas???

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Just look more closely at the syntax definition/example:

    recordsource is a valid table or query that returns a valid recordset to match fieldlist. If you are using a query, then you would enclose the query in parentheses. The recordsource could be as complex as needed to get you the records you want to add. It could even be a UNION query!

    Example:

    SELECT ItemName, LunchPrice INTO LunchMenu
    FROM (SELECT EntreeName, ItemCost*2.5 FROM RecipeList WHERE LunchFlag=1)
    So, you could try this:

    Code:
    SELECT *  INTO Newtable
      FROM (
    SELECT Employees_TBL.FirstName, Employees_TBL.LastName
             , JobTitle_TBL.JobTitle, Employees_TBL.Salary
       FROM Employees_TBL, JobTitle_TBL
     WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode
        AND JobTitle_TBL.Status = 'Exempt'
    UNION 
    SELECT Employees_TBL.FirstName, Employees_TBL.LastName
             , JobTitle_TBL.JobTitle, Employees_TBL.Salary
       FROM Employees_TBL, JobTitle_TBL
     WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode
         AND JobTitle_TBL.Status = 'Non-exempt');

    DISCLAIMER: This is just a suggestion due to the fact I know very little MS Access!
    Last edited by LKBrwn_DBA; 06-10-04 at 12:58.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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