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 Alert !

Dear Folks,

First time here.

Have just installed a 120-day evaluation copy of MSSQL 2000.

In the 'blur' and confusion of installing this on my WinXP Laptop, I
forgot to record the password.

Finally, I got into the 'logins' tab on the Enterprise Manager, and
modified the 'sa' password to 'ivlem1', and I think it was recorded
properly.

I tried to test my installation with the following at the Command
Prompt:

osql /Usa /P ivlem1

It failed saying I wasn't 'Trusted'

I would appreciate any help offered !

TIA,

-Mel SmithDid you select "mixed" mode for authentication. I would test this
differently...fire up QA and use your SA account/passwd to account.
There are too many things that could go wrong with establishing a
connection using osql.

HTH

MJKulangara
http://sqladventures.blogspot.com|||Mel Smith (medsyntel@.aol.com) writes:
> Have just installed a 120-day evaluation copy of MSSQL 2000.
> In the 'blur' and confusion of installing this on my WinXP Laptop, I
> forgot to record the password.
> Finally, I got into the 'logins' tab on the Enterprise Manager, and
> modified the 'sa' password to 'ivlem1', and I think it was recorded
> properly.
> I tried to test my installation with the following at the Command
> Prompt:
> osql /Usa /P ivlem1
> It failed saying I wasn't 'Trusted'

SQL Server have to means of authentication: Windows authentication (a.k.a
Integrated Security or Trusted Connection) and SQL Server authentication.
Windows authentication is always there, SQL authentication is optional,
and usually referred to as "mixed mode".

Windows authentcation means that you log into SQL Server with your
Windows credentials. This is very simple, you just say "osql /E" and
you are on the air. No need for password, as you are already logged
into Windows.

By default, only Windows authentication is enabled. This is because
SQL Server authentication is less secure. However, many enable
SQL authentiation for various reasons. For instance, if you are a
developer you may work with Windows authentication as long as you
develop and have sysadmin rights. But when you need to test, you
want a low-priv user, and testing different users is much easier
with SQL authentication.

You can change authentication mode from Enterprise Manager. Right-
click the server, select Properties, and then use the Security tab.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland and MJK,

Thank you both for your advice !

Erland:

I tried "osql /E" and it worked ! (i.e., I got correct correct results
for the two queries: select @.@.servername and select @.@.version)

(now I'm going to go blind reading all there is on Books Online to get
myself up to speed )

btw, I'm fairly expert in xBase databases (my job is tech support for a
structural steel company that uses my software, but is now moving to MSSQL)
and I have to show that I'm up to the task to help them make that move. In
fact, they have purchased AccPac for Windows, and have installed MSSQL 2000.
But I'm at a remote location from the headquarters of this company and must
get used to MSSQL all by myself in the 'sunny south'.

Thanks again.

-Mel Smith
Mesa, Arizona, USA

Newbie - Report Design help.

Hi folks,
New to RS, but have this really annoying colleague b*tching about
coversheets and TPS reports. (Sorry, off-topic).
Anyways, I've been ranting about the wonders of SQL RS to him, and then
he turns up wanting me to actually do a real report for him. In the
sample below, I'm using the Northwind database.
Basic idea is: You select a year in a combobox and run the report.
The report then shows the quanties sold for each product for that year
in one column, and in the next column it shows the equivalent data for
the previous year.
Tables used in this sample: Orders, OrderDetails and Products.
Hope someone can help me. :-)
-McWawa, the next President of The United States of America.
---
Select Year: 1999 |
2000 |
2001 | <- these are in a combobox
2002 |
2003 |
Products | 2001* | 2000**
---
Produkt A | 145 | 84
Produkt B | 200 | 129
Produkt C | 101 | 88
.. | ... | ...
.. | ... | ...
Produkt Z | 140 | 31
* = The Year selected in the combobox
** = The Year BEFORE the on selected in the combobox
---What exactly do you need help with?
This is a fairly straightforward report. Just create a parameter to allow
the user to select a year, run your query based upon the parameter, and
populate your simple table with the results.
What specifically are you having a problem with?
"McWawa" <n@.dachanche.ever> wrote in message
news:uuStdiczGHA.3512@.TK2MSFTNGP04.phx.gbl...
> Hi folks,
> New to RS, but have this really annoying colleague b*tching about
> coversheets and TPS reports. (Sorry, off-topic).
> Anyways, I've been ranting about the wonders of SQL RS to him, and then he
> turns up wanting me to actually do a real report for him. In the sample
> below, I'm using the Northwind database.
> Basic idea is: You select a year in a combobox and run the report.
> The report then shows the quanties sold for each product for that year in
> one column, and in the next column it shows the equivalent data for the
> previous year.
> Tables used in this sample: Orders, OrderDetails and Products.
> Hope someone can help me. :-)
> -McWawa, the next President of The United States of America.
>
> ---
> Select Year: 1999 |
> 2000 |
> 2001 | <- these are in a combobox
> 2002 |
> 2003 |
>
> Products | 2001* | 2000**
> ---
> Produkt A | 145 | 84
> Produkt B | 200 | 129
> Produkt C | 101 | 88
> .. | ... | ...
> .. | ... | ...
> Produkt Z | 140 | 31
>
> * = The Year selected in the combobox
> ** = The Year BEFORE the on selected in the combobox
>
> ---|||> This is a fairly straightforward report. Just create a parameter to allow
> the user to select a year, run your query based upon the parameter, and
> populate your simple table with the results.
> What specifically are you having a problem with?
Well, I think my problems may be caused by my lack of tsql experience.
If you (or anyone) could point me in the right direction with the query, I
think I could piece the report together from that.
McWawa|||Solved it through my query. :-)