Retrieve multiple recordcounts (1 Viewer)

[MrHans]

Hello all,

I am in the process of migrating an Access database to a SQL Server backend. I'm pretty new to SQL Server so I'm learning along the way.

I need to populate a dashboard with multiple recordcounts from the same table, so I'm looking for an efficient way of doing this.

Let's say I have a table in my SQL Server backend called tblOrders.
Relevant columns are OrderID (PK), OrderDate and OrderStatus.

On my dashboard I want to display the number of orders this year, last year, two years ago. Number of orders this year with Status Completed, status pending, status on hold, etc.
In total around 20 recordcounts based on different criteria.

What is the best way to get these numbers?

I can use 20 Dcounts on my linked table, but this puts the workload on Access instead of the server. So perhaps a ADO Recordset with filters and recordcounts?
Or maybe a 'stored procedure' or 'view'?
Not sure how to make that work, but I could read up on that.

Thanks in advance for your advice.

 

[theDBguy]

Hi. My first impression was to use Views as well, but perhaps you could get away with simply using pass through queries. Just a thought...

 

[plog]

On my dashboard I want to display the number of orders this year, last year, two years ago. Number of orders this year with Status Completed, status pending, status on hold, etc.

I really think this is dictated by the format you want your dashboard to have. If the format isn't already decided upon I would suggest accomplishing the above with 2 subforms each based on their own query:

SELECT YEAR(OrderDate) AS OrderYear, COUNT(OrderID)
FROM tblOrders
WHERE Year(OrderDate)>=(Year(Date())-2)
GROUP BY Year(OrderDate)

SELECT OrderStatus, COUNT(OrderStatus) AS Orders
FROM tblOrders
WHERE Year(OrderDate)=Year(Date())
GROUP BY OrderSTatus

Save those queries and use the form wizard and you have your subforms.

 

[MrHans]

Thanks a lot for your advices.

I guess I could create those queries and then retrieve the values using a recordset, but I guess you do mean pass-through queries right? Otherwise the workload would still be on the Access Frontend.

 

[plog]

I meant add those queries in Access adn then build forms based on them. No recordset, no pass thru.

You haven't tried anything yet, how do you know performance will be poor?

 

[MrHans]

Ok, no I didn't try it yet, probably performance will be fine.

My understanding was that part of the migration involves converting DAO to ADO to connect directly to the server and use the processing power of the server instead of the local frontend. Pulling only results over the network instead of entire tables.

Surely 20 dCounts on the linked table will work as well.

Thanks again, I'll experiment a bit to see if I experience any performance differences.

 

[Galaxiom]

My understanding was that part of the migration involves converting DAO to ADO to connect directly to the server and use the processing power of the server instead of the local frontend. Pulling only results over the network instead of entire tables.

Access does not pull whole tables from SQL Server via ODBC if the queries are well designed. Provided they use standard SQL key words and avoid applying functions to the SELECT criteria, the ODBC will send the query to the server for processing.

Under certain circumstances you can get far better performance from a connection but most simple queries work fine on linked tables or views.

Surely 20 dCounts on the linked table will work as well.

Whether it is 20 local queries or 20 DCounts the performance will be unimpressive especially if you use queries like the one posted by Plog who aught to know better by now. (Sorry Plog but I am sick and tired of having to point this out.)

SELECT YEAR(OrderDate) AS OrderYear, COUNT(OrderID)
FROM tblOrders
WHERE Year(OrderDate)>=(Year(Date())-2)
GROUP BY Year(OrderDate)

NEVER do this in any query. By applying a function to the left side of the criteria, every record in the table must be returned to apply the function because it can't use the index.

WHERE Year(OrderDate)>=(Year(Date())-2)

Where possible, ALWAYS use this structure so that the left side refers only to a field. Make sure the field (or column in SQL Server) is indexed

WHERE OrderDate >= DateSerial(Year(Date()) - 2, 1,1)

If you don't understand why it matters please search for information about SARGable queries.

It would be better to create a View on the server and link it to Access.

If you need to be able to include parameters then create a Stored Procedure to return a recordset. The recordset can be displayed in a form by setting the Recordset Property of the form to the returned recordset.

Another alternative is to create a Table Valued Function which can be used in Access via a PassThrough Query and used in queries if you want. You can pass the parameters to the function by modifying the SQL property of the query.

 

[plog]

Sorry Plog but I am sick and tired of having to point this out

No apology needed. Its one of those things I've never given much thought to, but your explanation makes perfect sense. I just hope it sticks in my brain--feel free to call me out again when I do it again.

 

[The_Doc_Man]

Sorry, just couldn't pass it up... The Ambrose Bierce definition of "experience" is "recognizing your mistakes when you make them again."