Making ORDER BY parameterized

I'm using an Access database for a backend to an ASP.NET site and was wondering how to go about setting up and storing a parameterized query that uses a parameter to specify which field to ORDER BY. I would like the parameter to be a field name. I can't seem to get it to work properly and would appreciate an example. Below is one of the queries for which I would like to be able to do this by specifiying either Members.MemberName, DKPs, or LifetimeEarned in the ORDER BY clause.

On a similar note, if I have the following query:

Code:

---

SELECT Members.MemberName, Sum(DKPAudits.DKPAuditAdjPts) AS DKPs, Sum(IIf(DKPAudits.DKPAuditType=2,-DKPAudits.DKPAuditAdjPts,0)) AS PtsSpent, Sum(IIf(DKPAudits.DKPAuditType<>2,DKPAudits.DKPAuditAdjPts,0)) AS [Lifetime Earned], Count(IIf(DKPAudits.DKPAuditType=2,1,Null)) AS Epics
FROM Members INNER JOIN DKPAudits ON Members.MemberID=DKPAudits.DKPAuditMemberID
GROUP BY Members.MemberName
ORDER BY Sum(DKPAudits.DKPAuditAdjPts) DESC;

---

Why will it not accept "DKPs" in the ORDER BY clause when i already specify that Sum() AS DKPs above? Do I really need the same sum in the SELECT and ORDER BY clauses?

Quote:

---

Originally Posted by TSmooth

Why will it not accept "DKPs" in the ORDER BY clause when i already specify that Sum() AS DKPs above? Do I really need the same sum in the SELECT and ORDER BY clauses?

---

I'm not sure why it doesn't work for you - but it should be. Actually, the ORDER BY clause is the only part of the query where you CAN use column aliases.

so surprise!!!
it might be the nature or the weakness of Access...
Insteads of repeating expression, just use column number such as "order by 2"

Quote:

---

Originally Posted by columbus2003

so surprise!!!
it might be the nature or the weakness of Access...
Insteads of repeating expression, just use column number such as "order by 2"

---

"ORDER BY Column" number is a very bad practise. Person responsible for maintaining your code will curse you.

anyway, I hope you will agree it is better than repeating the expression.

Quote:

---

Originally Posted by columbus2003

anyway, I hope you will agree it is better than repeating the expression.

---

I totally disagree with you. It's better to repeat the already known expression
than to use less clear syntax (BTW I've heard this syntax is deprecated).

Hi,

For running the parameterized query you have to put the query in the query section of the MS Access.on the left side u can find the tables toolbox.below tables there is queries.go there and write the query as


SELECT Members.MemberName, Sum(DKPAudits.DKPAuditAdjPts) AS DKPs, Sum(IIf(DKPAudits.DKPAuditType=2,-DKPAudits.DKPAuditAdjPts,0)) AS PtsSpent, Sum(IIf(DKPAudits.DKPAuditType<>2,DKPAudits.DKPAuditAdjPts,0)) AS [Lifetime Earned], Count(IIf(DKPAudits.DKPAuditType=2,1,Null)) AS Epics
FROM Members INNER JOIN DKPAudits ON Members.MemberID=DKPAudits.DKPAuditMemberID
GROUP BY Members.MemberName
ORDER BY @variable;


save this query there.You have to call this query from asp.net with the input values for that.the input must be the necessary field name u want.such as members or sum(audit) something.

Try.All the best