Ranking Query Results

M

Martin

Hi,

I have this SQL which works almost as I want it to:

SELECT [tbl Staff].Name, [tbl Staff].Username, [tbl Staff].CST, (SELECT
Count(*) FROM [tbl Staff] AS X WHERE X.Name = X.Name AND [tbl Staff].Name >=
X.Name) AS Rank
FROM QryUsername RIGHT JOIN [tbl Staff] ON QryUsername.CST = [tbl Staff].CST
WHERE (((QryUsername.CST) Is Not Null))
GROUP BY [tbl Staff].Name, [tbl Staff].Username, [tbl Staff].CST
HAVING ((([tbl Staff].Username)<>GetUser()))
ORDER BY [tbl Staff].Name;

The rank is based on the table [tbl Staff] but I only want it to rank the
records that are selected in this query i.e. 14 records (or staff) instead of
the total 54 staff.

If I remove the rank comumn and write another query based on this one and
substitute the [tbl Rank] with the name of this query I get an error message
and Access closes.

Can anyone advise where I am going wrong?

Thanks in advance.

Martin
 
D

Dale Fye

Let me rephrase your problem to make sure I understand what you want to do.

1. You have a query "QryUsername" which returns a subset of the total staff.

2. You want to write another query which returns, the staff members name,
username, CST, and alphabetical rank from among the names returned by
qryUsername, and excluding the user who is running the query. Is that
correct?
 
M

Martin

Almost spot on but I want the rank to be only on those records within the SQL
posted.

The SQL posted get the rank from the source table and then gives me that
rank. So If the query has only 10 records returned, the rank could possibly
be from the total of 54 from the source table so I may end up with only 10
records but ranks of much higher i.e. in the 30's or 40's

Dale Fye said:
Let me rephrase your problem to make sure I understand what you want to do.

1. You have a query "QryUsername" which returns a subset of the total staff.

2. You want to write another query which returns, the staff members name,
username, CST, and alphabetical rank from among the names returned by
qryUsername, and excluding the user who is running the query. Is that
correct?

----
HTH
Dale



Martin said:
Hi,

I have this SQL which works almost as I want it to:

SELECT [tbl Staff].Name, [tbl Staff].Username, [tbl Staff].CST, (SELECT
Count(*) FROM [tbl Staff] AS X WHERE X.Name = X.Name AND [tbl Staff].Name >=
X.Name) AS Rank
FROM QryUsername RIGHT JOIN [tbl Staff] ON QryUsername.CST = [tbl Staff].CST
WHERE (((QryUsername.CST) Is Not Null))
GROUP BY [tbl Staff].Name, [tbl Staff].Username, [tbl Staff].CST
HAVING ((([tbl Staff].Username)<>GetUser()))
ORDER BY [tbl Staff].Name;

The rank is based on the table [tbl Staff] but I only want it to rank the
records that are selected in this query i.e. 14 records (or staff) instead of
the total 54 staff.

If I remove the rank comumn and write another query based on this one and
substitute the [tbl Rank] with the name of this query I get an error message
and Access closes.

Can anyone advise where I am going wrong?

Thanks in advance.

Martin
Click to expand...
Click to expand...
 
K

KARL DEWEY

I think the problem is the use of HAVING as it is utilized after the records
are pulled.
Try changing like this --
WHERE (((QryUsername.CST) Is Not Null)) AND ((([tbl
Staff].Username)<>GetUser()))
ORDER BY [tbl Staff].Name, [tbl Staff].Username, [tbl Staff].CST;


--
Build a little, test a little.


Martin said:
Almost spot on but I want the rank to be only on those records within the SQL
posted.

The SQL posted get the rank from the source table and then gives me that
rank. So If the query has only 10 records returned, the rank could possibly
be from the total of 54 from the source table so I may end up with only 10
records but ranks of much higher i.e. in the 30's or 40's

Dale Fye said:
Let me rephrase your problem to make sure I understand what you want to do.

1. You have a query "QryUsername" which returns a subset of the total staff.

2. You want to write another query which returns, the staff members name,
username, CST, and alphabetical rank from among the names returned by
qryUsername, and excluding the user who is running the query. Is that
correct?

----
HTH
Dale



Martin said:
Hi,

I have this SQL which works almost as I want it to:

SELECT [tbl Staff].Name, [tbl Staff].Username, [tbl Staff].CST, (SELECT
Count(*) FROM [tbl Staff] AS X WHERE X.Name = X.Name AND [tbl Staff].Name >=
X.Name) AS Rank
FROM QryUsername RIGHT JOIN [tbl Staff] ON QryUsername.CST = [tbl Staff].CST
WHERE (((QryUsername.CST) Is Not Null))
GROUP BY [tbl Staff].Name, [tbl Staff].Username, [tbl Staff].CST
HAVING ((([tbl Staff].Username)<>GetUser()))
ORDER BY [tbl Staff].Name;

The rank is based on the table [tbl Staff] but I only want it to rank the
records that are selected in this query i.e. 14 records (or staff) instead of
the total 54 staff.

If I remove the rank comumn and write another query based on this one and
substitute the [tbl Rank] with the name of this query I get an error message
and Access closes.

Can anyone advise where I am going wrong?

Thanks in advance.

Martin
Click to expand...
Click to expand...
Click to expand...
 
V

vanderghast

Rank the ranks values you got (in another query based on the actual query)
?


Vanderghast, Access MVP


Martin said:
Almost spot on but I want the rank to be only on those records within the
SQL
posted.

The SQL posted get the rank from the source table and then gives me that
rank. So If the query has only 10 records returned, the rank could
possibly
be from the total of 54 from the source table so I may end up with only 10
records but ranks of much higher i.e. in the 30's or 40's

Dale Fye said:
Let me rephrase your problem to make sure I understand what you want to
do.

1. You have a query "QryUsername" which returns a subset of the total
staff.

2. You want to write another query which returns, the staff members
name,
username, CST, and alphabetical rank from among the names returned by
qryUsername, and excluding the user who is running the query. Is that
correct?

----
HTH
Dale



Martin said:
Hi,

I have this SQL which works almost as I want it to:

SELECT [tbl Staff].Name, [tbl Staff].Username, [tbl Staff].CST, (SELECT
Count(*) FROM [tbl Staff] AS X WHERE X.Name = X.Name AND [tbl
Staff].Name >=
X.Name) AS Rank
FROM QryUsername RIGHT JOIN [tbl Staff] ON QryUsername.CST = [tbl
Staff].CST
WHERE (((QryUsername.CST) Is Not Null))
GROUP BY [tbl Staff].Name, [tbl Staff].Username, [tbl Staff].CST
HAVING ((([tbl Staff].Username)<>GetUser()))
ORDER BY [tbl Staff].Name;

The rank is based on the table [tbl Staff] but I only want it to rank
the
records that are selected in this query i.e. 14 records (or staff)
instead of
the total 54 staff.

If I remove the rank comumn and write another query based on this one
and
substitute the [tbl Rank] with the name of this query I get an error
message
and Access closes.

Can anyone advise where I am going wrong?

Thanks in advance.

Martin
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Issues with Left Join 1
Basic Select Query Duplicating a single end user's records 2
query too slow 1
Crosstab query as data source for update query 7
show all results even when null for each name 6
LIKE Query 2
Delete Qry Problem 3
Reports are only totaling the fields for the first record 2

Top