Run query and sort

[gmgenova]

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")

 

[dhookom]

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

 

[gmgenova]

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;

 

[dhookom]

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:

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

 

[gmgenova]

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;

 

[Andrzejek]

I would try something like:
[tt][blue]
SELECT * FROM ([/blue]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[blue]) ORDER BY Total DESC[/blue][/tt]


---- Andy

There is a great need for a sarcasm font.

 

[gmgenova]

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

Thanks.

 

[dhookom]

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

 

[gmgenova]

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!!