Creating Dashboards in Microsoft Access

By | November 17, 2014

Congratulations Juan Soto on a great presentation this month!  I also want to thank everyone who joined the meeting and hope that you found the webinar informative and worthwhile.

The video is now available for you to review:

Here are the dashboard files: NorthwindDash

Your presence helped to make this event a great success. We wish you all the best and hope that you continue to be engaged with the Access User Group. Stay tuned for upcoming events by visiting accessusergroups.org.

NOTE: You can see the stored procedure by restoring the database in the zip file NorthwindDash above, but if you just want to see the stored procedure here it is:

USE [NorthwindDash]
GO
/****** Object: StoredProcedure [dbo].[usp_Dashboard] Script Date: 8/14/2015 8:08:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Juan Soto
-- Create date: November 17th, 2014
-- Description: Create table for Dashboard
-- =============================================
ALTER PROCEDURE [dbo].[usp_Dashboard] 
 -- Add the parameters for the stored procedure here
 @UserStartDate DateTime
AS
BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 Declare @Dashboard Table
 (
 myMetric VarChar(50),
 myTimeFrame VarChar(50),
 Qty Money
 )

 DECLARE @StartDate DateTime
 DECLARE @EndDate DateTime

 DECLARE @Year DATE 
 DECLARE @Quarter INT


 DECLARE @QuarterStartDate DateTime
 DECLARE @QuarterEndDate DateTime

 -- User supplies dates for analysis
 --Set @UserStartDate = CONVERT(DATETIME, '2014-11-08 00:00:00', 102)

 SET DATEFIRST 1; -- We want Monday as first weekday

 SELECT @StartDate= DATEADD(dd, -(DATEPART(dw, @UserStartDate )-1), @UserStartDate ) ;
 SELECT @EndDate= DATEADD(dd, 7-(DATEPART(dw, @UserStartDate )), @UserStartDate ) ;

 -- Calculation Quarters
 set @Year = DATEADD(day,-day(@userstartdate) + 1 ,@userstartdate)
 set @Year = dateadd(month, -month(@year)+1 ,@year)

 set @Quarter = datepart(quarter,@Userstartdate)

 set @QuarterStartDate =DATEADD(QUARTER, @Quarter - 1, @Year) 

 set @QuarterEndDate = DATEADD(DAY, -1, DATEADD(QUARTER, @Quarter, @Year))
 
 --Calc customers created today
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'Today', COUNT(ID) AS Qty
 FROM Customers
 WHERE DateCreated = @StartDate

 --Calc customers created this week
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'ThisWeek', COUNT(ID) AS Qty
 FROM Customers
 WHERE (DateCreated BETWEEN @StartDate and @EndDate);


 --Calc customers created this month
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'ThisMonth', COUNT(ID) AS Qty
 FROM Customers
 where (month(datecreated) = month(@UserStartDate) and year(datecreated) = year(@UserStartDate) )


 --Calc customers created this quarter
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'ThisQtr', COUNT(ID) AS Qty
 FROM Customers
 WHERE (DateCreated BETWEEN @QuarterStartDate AND @QuarterEndDate)


 --Calc customers created this year
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'ThisYear', COUNT(ID) AS Qty
 FROM Customers
 WHERE (year(DateCreated) = year(@UserstartDate) )

 --Calc orders created today
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'Today', COUNT([Order ID]) AS Qty
 FROM Orders
 WHERE [Order Date] = @UserStartDate;

 --Calc Orders created this week
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'ThisWeek', COUNT([Order ID]) AS Qty
 FROM Orders
 WHERE ([Order Date] BETWEEN @StartDate and @EndDate);


 --Calc Orders created this month
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'ThisMonth', COUNT([Order ID]) AS Qty
 FROM Orders
 where (month([Order Date]) = month(@UserStartDate) and year([Order Date]) = year(@UserStartDate) )

 --Calc Orders created this quarter
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'ThisQtr', COUNT([Order ID]) AS Qty
 FROM Orders
 WHERE ([Order Date] BETWEEN @QuarterStartDate AND @QuarterEndDate)

 --Calc Orders created this year
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'ThisYear', COUNT([Order ID]) AS Qty
 FROM Orders
 WHERE (year([Order Date]) = year(@UserstartDate) )

 --Calc order dollars created today
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','Today',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 WHERE Orders.[Paid Date] = @UserStartDate

 --Calc order dollars created this week
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','ThisWeek',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 WHERE (Orders.[Paid Date] BETWEEN @StartDate and @EndDate);

 --Calc order dollars created this month
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','ThisMonth',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 where (month(Orders.[Paid Date]) = month(@UserStartDate) and year(Orders.[Paid Date]) = year(@UserStartDate) )

 --Calc order dollars created this quarter
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','ThisQtr',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 WHERE (Orders.[Paid Date] BETWEEN @QuarterStartDate AND @QuarterEndDate)

 --Calc order dollars created this year
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','ThisYear',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 WHERE (year(orders.[Paid Date]) = year(@UserstartDate) )

 Select * from @Dashboard

End
Category: Blog

About Susan Pyne

I started with the firm as a Sales Assistant and over the years worked my way up to Senior Access developer, I’m certified on Power BI, with more certifications coming. I'm also the chapter coordinator for AccessUserGroups.org, a worldwide organization of Access virtual chapters that meet each month online to discuss a range of topics in different languages. I LOVE working with Access and learning new technologies!

3 thoughts on “Creating Dashboards in Microsoft Access

  1. aaronrobe

    Juan, your presentation was excellent.

    I am trying to restore your database but the stored procedure usp_Dashboard seems to be missing.

    Can you possibly post the code to the stored procedure?

    Thank you for all your very informative advice.

    Robert A.

  2. grovelli

    Will there be a video recording available of the latest meeting: Power and Simplicity using Tables for Dates and Numbers?

  3. jfrenzley

    Juan Soto mentioned that you use some tool to create the menu for MS Access. Can you please share what tool this is.. Thanks

    John

Leave a Reply