Ranking Query Results

  • Thread starter Thread starter Martin
  • Start date Start date
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
 
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?
 
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...
 
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...
 
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...
 
Back
Top