WORKS IN SQL JUST NOT ACCESS? 1

[DOGWATCH]

I'm working this query and just getting an syntax error.
It does work perfect with SQL 2000 just haven't been able to make it work on Access 2003.

Any ideas?


SELECT ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) AS ROW_A,
RTRIM( Table2.LNAME ) + ',' + Table2.FNAME AS NAME_A,
Table2.GRADE AS GRADE_A,
( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) + 4 AS ROW_B,
RTRIM( Values_B.LNAME ) + ',' + Values_B.FNAME AS NAME_B,
Values_B.GRADE AS GRADE_B
FROM table2
LEFT JOIN ( SELECT FNAME, LNAME, GRADE,
( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) AS Row_number
FROM Table2
WHERE ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) > 4
) AS Values_B ON Values_B.Row_number = ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) + 4
WHERE ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) < 5

 

[PHV]

I strongly suggest to use different alias for each table and unnamed queries.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DOGWATCH]

Why have I used some sort of reserved word for my table names or alias?

The Row_Number part seems to be the buggy part.
Row_Number is not a real col name its just an alias and its not displayed.

 

[PHV]

I quite don't understand the final goal ...
Anyway, a starting point:
SELECT ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= [highlight]A[/highlight].LNAME ) AS ROW_A,
RTRIM( Table2.LNAME ) + ',' + Table2.FNAME AS NAME_A,
Table2.GRADE AS GRADE_A,
( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= [highlight]A[/highlight].LNAME ) + 4 AS ROW_B,
RTRIM( Values_B.LNAME ) + ',' + Values_B.FNAME AS NAME_B,
Values_B.GRADE AS GRADE_B
FROM table2 [highlight]AS A[/highlight]
LEFT JOIN ( SELECT FNAME, LNAME, GRADE,
( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= [highlight]B[/highlight].LNAME ) AS Row_number
FROM Table2 [highlight]AS B[/highlight]
WHERE ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= [highlight]B[/highlight].LNAME ) > 4
) AS Values_B ON Values_B.Row_number = ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= [highlight]???[/highlight].LNAME ) + 4
WHERE ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= [highlight]???[/highlight].LNAME ) < 5

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DOGWATCH]

Here is my point:

TABLE2
FNAME LNAME GRADE
----- ----- -----
JOE AMES C
JEFF FRANKSON B
BILL BARTSON A
TIM CARRY A
JOHN DOE B
JANE ZIMMER C

HERE EXPECTED OUTPUT:

ROW_A NAME_A GRADE_A ROW_B NAME_B GRADE_B
----- ------ ----- ------ -------- ------
1 AMES,JOE C 5 FRANKSON,JEFF B
2 BARTSON,BILL A 6 ZIMMER, JANE C
3 CARRY, TIM A 7
4 DOE,JOHN B 8


the goal is to get a double coulumn query which will be used on a report. Any ideas?

 

[PHV]

So replace the ??? with A in my previous post.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DOGWATCH]

same syntax error. In my query ROW_A, NAME_a, GRADE_A
that part works.

seems like it craps out on the join. is there someway I could split that query. that the first 4 records for the left side then the last 4 for the right side and join them with a 3rd query.

 

[PHV]

OK, this works for me:
SELECT ROW_A,
RTrim(Values_A.LNAME) + ',' & Values_A.FNAME AS NAME_A, Values_A.GRADE AS GRADE_A, ROW_B,
RTrim(Values_B.LNAME) + ',' & Values_B.FNAME AS NAME_B, Values_B.GRADE AS GRADE_B
FROM (
SELECT FNAME, LNAME, GRADE,
( SELECT COUNT(*) FROM Table_2 AS cnt WHERE cnt.LNAME <= A.LNAME ) AS ROW_A,
( SELECT COUNT(*) FROM Table_2 AS cnt WHERE cnt.LNAME <= A.LNAME ) + 4 AS ROW_B
FROM table_2 AS A
) AS Values_A
LEFT JOIN (
SELECT FNAME, LNAME, GRADE,
( SELECT COUNT(*) FROM Table_2 AS cnt WHERE cnt.LNAME <= B.LNAME ) AS Row_number
FROM Table_2 AS B
WHERE ( SELECT COUNT(*) FROM Table_2 AS cnt WHERE cnt.LNAME <= B.LNAME ) > 4
) AS Values_B ON Values_A.ROW_B = Values_B.Row_number
WHERE ROW_A < 5;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

OOps, sorry for the typo:
OK, this works for me:
SELECT ROW_A,
RTrim(Values_A.LNAME) + ',' + Values_A.FNAME AS NAME_A, Values_A.GRADE AS GRADE_A, ROW_B,
RTrim(Values_B.LNAME) + ',' + Values_B.FNAME AS NAME_B, Values_B.GRADE AS GRADE_B
FROM (
SELECT FNAME, LNAME, GRADE,
( SELECT COUNT(*) FROM Table_2 AS cnt WHERE cnt.LNAME <= A.LNAME ) AS ROW_A,
( SELECT COUNT(*) FROM Table_2 AS cnt WHERE cnt.LNAME <= A.LNAME ) + 4 AS ROW_B
FROM table_2 AS A
) AS Values_A
LEFT JOIN (
SELECT FNAME, LNAME, GRADE,
( SELECT COUNT(*) FROM Table_2 AS cnt WHERE cnt.LNAME <= B.LNAME ) AS Row_number
FROM Table_2 AS B
WHERE ( SELECT COUNT(*) FROM Table_2 AS cnt WHERE cnt.LNAME <= B.LNAME ) > 4
) AS Values_B ON Values_A.ROW_B = Values_B.Row_number
WHERE ROW_A < 5;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

A more general way, providing you have an even number of rows:
SELECT ROW_A,
RTrim(Values_A.LNAME) + ',' + Values_A.FNAME AS NAME_A, Values_A.GRADE AS GRADE_A, ROW_B,
RTrim(Values_B.LNAME) + ',' + Values_B.FNAME AS NAME_B, Values_B.GRADE AS GRADE_B
FROM (
SELECT FNAME, LNAME, GRADE,
( SELECT COUNT(*) FROM Table_2 AS cnt WHERE cnt.LNAME <= A.LNAME ) AS ROW_A,
( SELECT COUNT(*) FROM Table_2 AS cnt WHERE cnt.LNAME <= A.LNAME ) + (SELECT Count(*) FROM Table_2)/2 AS ROW_B
FROM table_2 AS A
) AS Values_A
LEFT JOIN (
SELECT FNAME, LNAME, GRADE,
( SELECT COUNT(*) FROM Table_2 AS cnt WHERE cnt.LNAME <= B.LNAME ) AS Row_number
FROM Table_2 AS B
WHERE ( SELECT COUNT(*) FROM Table_2 AS cnt WHERE cnt.LNAME <= B.LNAME ) > (SELECT Count(*) FROM Table_2)/2
) AS Values_B ON Values_A.ROW_B = Values_B.Row_number
WHERE ROW_A <= (SELECT Count(*) FROM Table_2)/2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DOGWATCH]

Outstanding! This solution works perfect in Access.
Thanks man!