Searching for Stats: Daily Ares Requests Received Using Microsoft Access' Pass-Through Queries and ODBC

We recently had a site that needs to know how many requests were placed on a specific date. They're unable to use AtlasBI (don't worry, we're working on alternative ways to get locally-hosted sites access), so I wrote some SQL queries to go along with our pre-existing Community post on using Microsoft Access with "pass-through queries"

For more information on connecting your Ares database and Microsoft Access, please see: https://support.atlas-sys.com/hc/en-us/articles/360011922533-Microsoft-Access-Reports

Instructions for Access 2007

1. On the Create tab, click Query Design in the Other group.
2. Click Close in the Show Table dialog box without adding any tables or queries.
3. On the Design tab, click Pass-Through in the Query Type workgroup.
4. Click Property Sheet in the Show/Hide workgroup to display the property sheet for the query.
5. In the query property sheet, place the mouse pointer in the
   ODBC Connect Str property, and then, click the Build (...) button.
   
   With the ODBC Connect Str property, you specify information about the database to which you want to connect. You can type the connection information, or click Build, and then enter information about the server to which you are connecting.
   
   
6. When you are prompted to save the password in the connection string, click Yes if you want the password and logon name to be stored in the connection string information.
7. If the query is not the type that returns records, set the ReturnsRecords property to No.
8. In the SQL Pass-Through Query window, you'll type your pass-through query.
   
   The queries for the topic I explained above are further down, but here's a simple example that uses the Microsoft SQL Server TOP operator in the SELECT statement to return only the first 25 ItemIDs in the Items table:

   SELECT TOP 25 ItemID from Items
   

9. To run the query, click Run in the Results group on the Design tab. For a SQL pass-through query that returns records, click Datasheet view on the status bar.
10. If necessary, Microsoft Access prompts you for information about your server database.

From: https://support.microsoft.com/en-in/help/303968/how-to-create-an-sql-pass-through-query-in-access 

Crafting your Query

Here are the sample queries for returning information about item requests submitted on any particular day. I limited the queries to requests for NON-hardcopy requests. I chose to include this filter, since it seemed like requests in the past would have already been processed and any that came in with a default value of HardCopy (which just shows up when it comes in, if it's a scan request or an instructor-to-provide request), but we wouldn't want to keep that delimiter in the query for current requests, because they might still have that default value.

This following query returns a count of submitted, non-hard-copy requests on a SPECIFIC DATE. Note there are two places to enter dates. The first is for the date you want. The second date needs to be the day after the day you want, because we're using this limit to say show us all requests before this date. 

This next query will return all the details for last year's requests. See how it uses the asterisk after select? That means return everything in those tables.

select * from itemhistory ih join items i on 
ih.itemid=i.itemid where i.DocumentType <>'HardCopy' and (ih.entry = 'Status changed to Item Submitted 
via Cloned Course' OR ih.entry = 'Status changed to Item Submitted') and
 ih.datetime >= Convert(datetime, '2019-03-24') and ih.datetime < 
Convert(datetime, '2019-03-25')

This next version is the count when you want to see today's requests (today = 3/24/2020 in this example). The only difference is that it doesn't have the filter to show only requests with a DocumentType NOT equal to HardCopy.

select count(distinct ih.itemid) from itemhistory ih join items i on 
ih.itemid=i.itemid where (ih.entry = 'Status changed to Item Submitted 
via Cloned Course' OR ih.entry = 'Status changed to Item Submitted') and
 ih.datetime >= Convert(datetime, '2020-03-24') and ih.datetime < 
Convert(datetime, '2020-03-25')

To make things easier and so you don't have to manually change the date every time, I've also written them up for daily use - so you can replace a specific date with something called "GETDATE" in SQL. Here's the three same queries with that replacement.

These queries also use the concept of adding ONE to the date that marks the end of your search period. So if today was 3/24/2020, instead of using "Convert (datetime, '2019-03-24' )" and "Convert (datetime, '2019-03-25' )," we'll use Convert(date, GETDATE()-365) and Convert(date, GETDATE()-364).

This query returns a count of requests that were submitted TODAY. (note the hardcopy restriction is not here, just like in the last version)

select count(distinct ih.itemid) from itemhistory ih join items i on 
ih.itemid=i.itemid where (ih.entry = 'Status changed to Item Submitted 
via Cloned Course' OR ih.entry = 'Status changed to Item Submitted') and
 ih.datetime >= Convert(date, GETDATE()) and ih.datetime < 
Convert(date, GETDATE()+1)

This query returns all fields for non-hard-copy requests that were submitted exactly a year from today's date.

select * from  itemhistory ih join items i on
 ih.itemid=i.itemid where i.DocumentType <> 'HardCopy' and (ih.entry =  'Status changed to Item Submitted
 via Cloned Course' OR ih.entry =  'Status changed to Item Submitted') 
and ih.datetime >= Convert(date,  GETDATE()-365) and ih.datetime <
 Convert(date, GETDATE()-364)

You can mix and match these to remove DocumentType restrictions, add current status filters, or add in for ItemHistory entries for items added in by Staff through the Client... pretty much whatever you need. Let us know if you're running into issues by commenting below OR by emailing support@atlasp-sys.com and we'll do our best to help you out.

Cheers,

Kerry