NEED HELP WITH SQL STATEMENT 1

[TNN]

In one SQL statement I am trying to select all the individual amounts in the column of a table and the sum of those amounts.
I can do this with two separate statements but not all in one statement. Is Possible????

Thank You, anybody
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[tlbroadbent]

Tom,

Are you using SQL Server or Access. I noted your question in the SQL Server forum and replied. The first solution given there won't work in Access. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[TNN]

Terry,
I am using an access 2000 database. I am using SQL via ADO in VB6.0.
I tried your second example in the SQL Server forum but can't make it work. Seems like it should.

The two separate statements that work as follows:

SELECT WAMT FROM EMPEARNPERWAGE
WHERE EMPNO=62

SELECT
SUM(WAMT)
AS TOTAL FROM EMPEARNPERWAGE
WHERE EMPNO=62
These two statements work fine but I need to put them together into one statement.

Posted this also in SQL Server forum in case you went there.

Thank You, TNN, Tom
TNPAYROLL@AOL.COM




TOM

http://WWW.TNPAYROLL@AOL.COM

 

[tlbroadbent]

The syntax is slightly different in Access and SQL Server queries. Try this in Access.

SELECT e.WAmt,
(SELECT SUM(WAmt)
FROM EmpEarnPerWage
WHERE EmpNo=62) AS Total
FROM EmpEarnPerWage AS e
WHERE EmpNo=62;

You can also use the following. This next query should be more efficient than the first.

SELECT e.EmpNo, e.WAmt, s.Total
FROM EmpEarnPerWage AS e INNER JOIN
[SELECT EmpNo, sum(WAmt) AS Total
FROM EmpEarnPerWage
GROUP BY EmpNo]. AS s
ON e.EmpNo=s.EmpNo
WHERE e.EmpNo=62 Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[TNN]

Terry,

Both statements work great. Now I have to try and understand what you wrote. What is the e. and s. ???. What purpose do they serve??
Do you know of a good reference for SQL as it relates to access? The help screens in Microsoft Access are lacking in Thouroughness and examples.

I also looked at the tips and ideas for posting of questions. Thanks

Thank You Soooooo Much
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[tlbroadbent]

e and s are aliases (or names) for the queries. You can rename a column, table or query using aliases. In this case it is essential because the queries really become "derived tables" without names. The names are required in order to join the queries.

I don't have an Access book and haven't read one for several years. However, I've noted some of the favorites mentioned by Tek-Tips members include the following.

"Access 2000: Developer's Handbook" by Litwin, Getz, and Gilbert, published by Sybex
Check the website:

http://www.developershandbook.com/

Alison Balter's Mastering Access 2000 Development (Sams Professional Series)
See website:

http://halljava.com/microsoft_office_2000/access_2000/14.shtml

Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.