×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Run query and sort

Run query and sort

Run query and sort

(OP)
I have a crosstab query that has an aggregate field in it that I can not automatically sort when the query runs in Access. I am making a VBA macro and am trying to run the query in there and also sort that particular field. I am not very experienced with VBA so I am getting errors. I am just not sure of the correct syntax. When I try it like below, I get a Data Type Conversion Error. Any help is appreciated. Thank you.


Set rsQuery = dbs.OpenRecordset("Name of my query here", "DoCmd.SetOrderBy Total DESC")

RE: Run query and sort

The OpenRecordset method doesn't allow a parameter to set the Order By. Can't you do this in the query? What is the SQL of your query?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Run query and sort

(OP)

No, because the field I want to sort by is an aggregate field.

TRANSFORM Sum([dbo_RX Reporting].Price) AS SumOfPrice
SELECT [dbo_RX Reporting].[Master Drug Name], Sum([dbo_RX Reporting].Price) AS Total
FROM [dbo_RX Reporting]
WHERE ((([dbo_RX Reporting].[Therapeutic Class]) Like "*Analg*") AND (([dbo_RX Reporting].Period) Like "2018*") AND (([dbo_RX Reporting].[Plan Name])="Alabama"))
GROUP BY [dbo_RX Reporting].[Plan Name], [dbo_RX Reporting].[Master Drug Name]
PIVOT [dbo_RX Reporting].Period;

RE: Run query and sort

I was able to sort by the Total when I replaced the Sum() with a DSum() expression that summed the Price. My crosstab in Northwind is:

CODE --> sql

TRANSFORM Sum(Orders.Freight) AS SumOfFreight
SELECT Employees.LastName, Val(DSum("[Freight]","Orders","EmployeeID = " & [Orders].[EmployeeID])) AS Total
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.LastName, Val(DSum("[Freight]","Orders","EmployeeID = " & [Orders].[EmployeeID]))
ORDER BY Val(DSum("[Freight]","Orders","EmployeeID = " & [Orders].[EmployeeID])) DESC 
PIVOT Year([OrderDate]) & ": " & DatePart("q",[OrderDate]); 

Note that I wrapped the DSum() in Val() to convert it to a numeric value.

This might run very slow if you have thousands of records.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Run query and sort

(OP)
So mine should look like this?


TRANSFORM Sum([dbo_RX Reporting].Price) AS SumOfPrice
SELECT [dbo_RX Reporting].[Master Drug Name], Val(DSum([dbo_RX Reporting]).Price) AS Total
FROM [dbo_RX Reporting]
WHERE ((([dbo_RX Reporting].[Therapeutic Class]) Like "*Analg*") AND (([dbo_RX Reporting].Period) Like "2018*") AND (([dbo_RX Reporting].[Plan Name])="Alabama"))
GROUP BY [dbo_RX Reporting].[Plan Name], [dbo_RX Reporting].[Master Drug Name], Val(DSum([dbo_RX Reporting]).Price)
ORDER BY Val(DSum([dbo_RX Reporting]).Price) DESC
PIVOT [dbo_RX Reporting].Period;

RE: Run query and sort

I would try something like:

SELECT * FROM (
TRANSFORM Sum([dbo_RX Reporting].Price) AS SumOfPrice
SELECT [dbo_RX Reporting].[Master Drug Name], Sum([dbo_RX Reporting].Price) AS Total
FROM [dbo_RX Reporting]
WHERE ((([dbo_RX Reporting].[Therapeutic Class]) Like "*Analg*") AND (([dbo_RX Reporting].Period) Like "2018*") AND (([dbo_RX Reporting].[Plan Name])="Alabama"))
GROUP BY [dbo_RX Reporting].[Plan Name], [dbo_RX Reporting].[Master Drug Name]
PIVOT [dbo_RX Reporting].Period) ORDER BY Total DESC



---- Andy

There is a great need for a sarcasm font.

RE: Run query and sort

(OP)
I like this Andy. I tried it though and get 'Syntax error in FROM clause.'

Thanks.

RE: Run query and sort

I doubt Andy's syntax is allowed in a crosstab query in Access.

DSum() is it's own "query" and returns a single value. The function has 2 or 3 arguments where the 3rd is a where clause. You would need to set the 3rd to filter the DSum() based on [Master Drug Name].

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Run query and sort

(OP)
I ended up saving this query and making a new query that refereed to this query and sorted the field in the new query. Thanks for the help!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close