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?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:

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');

:cool:
DISCLAIMER: This is just a suggestion due to the fact I know very little MS Access! :o

Newbie ? Please Help!

I'm a little new at this and need some help. My assignment is to modify the following function and do the following steps:
1.If ab_Table.a_col = xy_Table.a_col, then dosomething.
??How do I populate xy_Table
??How does ab_Table get populated in the code below
??How do I compare the ab_Table.a_col to xy_Table.a_col to see if they match
??Where should the code go in the function

/*This is what already exists, I need to know where to put my code.*/
tbl_rc_ab_Table Number :=0

TYPE ab_Table_rectab IS TABLE OF ab_Table %ROWTYPE
INDEX BY BINARY INTEGER

tbl_ab_Table_rectab ab_Table_rectab;

FUNCTION fCreate(p_arg IN OUT...)
n NUMBER :=0
IF tbl_ab_Table_rectab.COUNT > 0 THEN
FOR n in 1..tbl_ab_Table_rectab.COUNT LOOP
IF tbl_ab_Table_rectab.cola = p_arg THEN
tbl_ab_Table_rectab.colb :=1
END IF;
END LOOP;
tbl_rc_ab_Table = tbl_ab_Table_rectab.COUNT + 1;
tbl_ab_Table_rectab(tbl_rc_ab_Table).col_c :=0
END IF:Answers to your questions:
Both #1 and #2 - populate a physical table with the INSERT statement.
#3 - Use the equivalence operator to test variables of same type.
#4 - Well, the "record group" you declare is only initialized (but no
values are being populated in it), the arg val ("p_arg) is of both IN OUT
(only IN is needed) and all this loop is doing right now is making
"colb" equal to a value of 1 if "cola" equals the "p_arg".

First, I think you'll need to place values into this RG (using a Cursor
from the table). Once the RG has values, now you'll be able to
get something out of it with your FOR loop.

Newbie - Student - Help

I am working on an assignment that was due at midnight. The question is usi
ng the "Northwind database write the following SQL statements to be executed
against on of the tables within the "Northwind database"
a.) Alter a table and add a column
b.) Alter a table and add a constraint
c.) Alter a table and add an index
What I have so far is:
USE Northwind
ALTER TABLE Employees ADD COLUMN TerminationDate DateTime NULL
Server: Msg 156, Level 15, State 1, Line 3
I keep getting "Incorrect syntax near the keyword 'COLUMN'"
What am I doing wrong? If I can not get part 'a' then I will never get the
rest of the assignment.
Thanks in advance,
Deanna
UoP Student
deannac24@.cableone.netI am using SQL Query Analyzer
--
Deanna
UoP Student
deannac24@.cableone.net
"Deanna Cusic" <deannac24@.cableone.net> wrote in message news:11lh29913e9ngd
5@.corp.supernews.com...
I am working on an assignment that was due at midnight. The question is usi
ng the "Northwind database write the following SQL statements to be executed
against on of the tables within the "Northwind database"
a.) Alter a table and add a column
b.) Alter a table and add a constraint
c.) Alter a table and add an index
What I have so far is:
USE Northwind
ALTER TABLE Employees ADD COLUMN TerminationDate DateTime NULL
Server: Msg 156, Level 15, State 1, Line 3
I keep getting "Incorrect syntax near the keyword 'COLUMN'"
What am I doing wrong? If I can not get part 'a' then I will never get the
rest of the assignment.
Thanks in advance,
Deanna
UoP Student
deannac24@.cableone.net|||Deanna Cusic skrev:

> What I have so far is:
> USE Northwind
> ALTER TABLE Employees ADD COLUMN TerminationDate DateTime NULL
>
> Server: Msg 156, Level 15, State 1, Line 3
> I keep getting "Incorrect syntax near the keyword 'COLUMN'"
>
Have you checked Books online, the help that comes with SQL Server?
Using that you should be able to work the syntax out! Or check eg.
http://msdn.microsoft.com/library/d...
server2000.asp
if you don't have access to BOL.
Other than that, try losing the 'COLUMN' part.
/impslayer, aka Birger Johansson|||"impslayer" <impslayer@.hotmail.com> wrote in message
news:1129876626.682799.308040@.f14g2000cwb.googlegroups.com...
> Deanna Cusic skrev:
>
> Have you checked Books online, the help that comes with SQL Server?
> Using that you should be able to work the syntax out! Or check eg.
> http://msdn.microsoft.com/library/d...lserver2000.asp
> if you don't have access to BOL.
> Other than that, try losing the 'COLUMN' part.
> /impslayer, aka Birger Johansson
>
Thank you, all I did is take out 'COLUMN' and it works. I have been
wracking my brain for hours over this.
Deanna
UoP Student
deannac24@.cableone.net|||OK, thanks for your help so far but now I am stuck on the final one. I am
trying to add an index. So far I have:
use Northwind
ALTER TABLE Employees ADD [idxAddress] nvarchar(60), Address nvarchar(60)
the error I get is
Server: Msg 2705, level 16, State 4, Line 3
Column names in each table must be unique. Column name 'Address' in table
'Employees' is specified more than once.
I have tried this on many different fields including the primary keys and
still get this same error. I have even pulled up the data to check for
replication and did not find any. I am lost. I finally, thanks to help,
have part a and b, however I need part c. When I get that I can actually
get some sleep before the sun, and my children, get up ;-)
--
Deanna
"Deanna Cusic" <deannac24@.cableone.net> wrote in message
news:11lh4epilg52q9b@.corp.supernews.com...
> "impslayer" <impslayer@.hotmail.com> wrote in message
> news:1129876626.682799.308040@.f14g2000cwb.googlegroups.com...
> Thank you, all I did is take out 'COLUMN' and it works. I have been
> wracking my brain for hours over this.
> Deanna
> UoP Student
> deannac24@.cableone.net
>|||Deanna Cusic skrev:

> OK, thanks for your help so far but now I am stuck on the final one. I am
> trying to add an index. So far I have:
> use Northwind
> ALTER TABLE Employees ADD [idxAddress] nvarchar(60), Address nvarchar(60)
>
You should look for help on creating an index, not 'ALTER TABLE'...
The assignment question seemed to indicate an 'ALTER TABLE', but
you should really search for help on 'index' instead, that would
give you your desired answer!
Without Books online, you might check out:
http://msdn.microsoft.com/library/d...r />
_64l4.asp
/impslayer, aka Birger Johansson