SQL Problem

[theunknownknight]

SELECT projectreview.ProjNum, Count(projectreview.Login) AS "LC", (select Count(login.pkey) from login)-1 AS "LC1"
FROM projectreview INNER JOIN login ON projectreview.Login = login.PKey WHERE &quot;LC&quot; <> &quot;LC1&quot;
GROUP BY projectreview.ProjNum;

returns this:

ProjNum LC LC1
1002 3 4
1003 4 4
1007 4 4
1008 2 4
1010 1 4

I want to retrieve 1003 and 1007 so I edit the above query to this:

SELECT projectreview.ProjNum, Count(projectreview.Login) AS &quot;LC&quot;, (select Count(login.pkey) from login)-1 AS &quot;LC1&quot;
FROM projectreview INNER JOIN login ON projectreview.Login = login.PKey WHERE &quot;LC&quot; = &quot;LC1&quot;
GROUP BY projectreview.ProjNum;

It then returns nothing. Does anyone know why or How I can fix this?

 

[r937]

WHERE &quot;LC&quot; <> &quot;LC1&quot; will return all rows because it's true for all rows -- you are comparing two literal strings!!

to retrieve 1003 and 1007, try

where LC = LC1


rudy

 

[theunknownknight]

When I do that I get a dialog box asking for a parameter LC and LC1

here is the new query:

SELECT projectreview.ProjNum, Count(projectreview.Login) AS [&quot;LC&quot;], (select Count(login.pkey) from login)-1 AS [&quot;LC1&quot;]
FROM login INNER JOIN projectreview ON login.PKey = projectreview.Login
WHERE LC = LC1
GROUP BY projectreview.ProjNum;

Access sees LC and LC1 in the where clause as Parameters. This will also happen if I take the quotations off of the aliases in the select line.

 

[r937]

okay, sorry, i saw only the bolded stuff and assumed they were column names, i failed to look higher in the query to see that they are actually column aliases

[tt]SELECT projectreview.ProjNum
, Count(projectreview.Login) AS &quot;LC&quot;
, (select Count(login.pkey) from login)-1 AS &quot;LC1&quot;
FROM projectreview
INNER
JOIN login
ON projectreview.Login = login.PKey
GROUP BY projectreview.ProjNum
having Count(projectreview.Login)
= (select Count(login.pkey) from login)-1[/tt]

rudy