{"id":449,"date":"2014-11-17T10:53:39","date_gmt":"2014-11-17T16:53:39","guid":{"rendered":"http:\/\/accessusergroups.org\/sql-server-with-access\/?p=449"},"modified":"2016-08-26T09:37:02","modified_gmt":"2016-08-26T14:37:02","slug":"creating-dashboards-in-microsoft-access","status":"publish","type":"post","link":"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/","title":{"rendered":"Creating Dashboards in Microsoft Access"},"content":{"rendered":"<p>Congratulations Juan Soto on a great presentation this month! \u00a0I also want to thank everyone who joined the meeting and\u00a0hope that you found the webinar\u00a0informative and worthwhile.<\/p>\n<p>The video is now available for you to review:<\/p>\n<p><iframe loading=\"lazy\" width=\"720\" height=\"420\" src=\"https:\/\/www.youtube.com\/embed\/4_lHNwFFwfI\" frameborder=\"0\" allowfullscreen><\/iframe><\/p>\n<p>Here are the dashboard files:\u00a0<a href=\"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-content\/uploads\/sites\/2\/2014\/11\/NorthwindDash.zip\">NorthwindDash<\/a><\/p>\n<p>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.<\/p>\n<p>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:<\/p>\n<pre>USE [NorthwindDash]\r\nGO\r\n\/****** Object: StoredProcedure [dbo].[usp_Dashboard] Script Date: 8\/14\/2015 8:08:56 AM ******\/\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n-- =============================================\r\n-- Author: Juan Soto\r\n-- Create date: November 17th, 2014\r\n-- Description: Create table for Dashboard\r\n-- =============================================\r\nALTER PROCEDURE [dbo].[usp_Dashboard] \r\n -- Add the parameters for the stored procedure here\r\n @UserStartDate DateTime\r\nAS\r\nBEGIN\r\n\r\n -- SET NOCOUNT ON added to prevent extra result sets from\r\n -- interfering with SELECT statements.\r\n SET NOCOUNT ON;\r\n\r\n Declare @Dashboard Table\r\n (\r\n myMetric VarChar(50),\r\n myTimeFrame VarChar(50),\r\n Qty Money\r\n )\r\n\r\n DECLARE @StartDate DateTime\r\n DECLARE @EndDate DateTime\r\n\r\n DECLARE @Year DATE \r\n DECLARE @Quarter INT\r\n\r\n\r\n DECLARE @QuarterStartDate DateTime\r\n DECLARE @QuarterEndDate DateTime\r\n\r\n -- User supplies dates for analysis\r\n --Set @UserStartDate = CONVERT(DATETIME, '2014-11-08 00:00:00', 102)\r\n\r\n SET DATEFIRST 1; -- We want Monday as first weekday\r\n\r\n SELECT @StartDate= DATEADD(dd, -(DATEPART(dw, @UserStartDate )-1), @UserStartDate ) ;\r\n SELECT @EndDate= DATEADD(dd, 7-(DATEPART(dw, @UserStartDate )), @UserStartDate ) ;\r\n\r\n -- Calculation Quarters\r\n set @Year = DATEADD(day,-day(@userstartdate) + 1 ,@userstartdate)\r\n set @Year = dateadd(month, -month(@year)+1 ,@year)\r\n\r\n set @Quarter = datepart(quarter,@Userstartdate)\r\n\r\n set @QuarterStartDate =DATEADD(QUARTER, @Quarter - 1, @Year) \r\n\r\n set @QuarterEndDate = DATEADD(DAY, -1, DATEADD(QUARTER, @Quarter, @Year))\r\n \r\n --Calc customers created today\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'New Customers', 'Today', COUNT(ID) AS Qty\r\n FROM Customers\r\n WHERE DateCreated = @StartDate\r\n\r\n --Calc customers created this week\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'New Customers', 'ThisWeek', COUNT(ID) AS Qty\r\n FROM Customers\r\n WHERE (DateCreated BETWEEN @StartDate and @EndDate);\r\n\r\n\r\n --Calc customers created this month\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'New Customers', 'ThisMonth', COUNT(ID) AS Qty\r\n FROM Customers\r\n where (month(datecreated) = month(@UserStartDate) and year(datecreated) = year(@UserStartDate) )\r\n\r\n\r\n --Calc customers created this quarter\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'New Customers', 'ThisQtr', COUNT(ID) AS Qty\r\n FROM Customers\r\n WHERE (DateCreated BETWEEN @QuarterStartDate AND @QuarterEndDate)\r\n\r\n\r\n --Calc customers created this year\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'New Customers', 'ThisYear', COUNT(ID) AS Qty\r\n FROM Customers\r\n WHERE (year(DateCreated) = year(@UserstartDate) )\r\n\r\n --Calc orders created today\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'New Orders', 'Today', COUNT([Order ID]) AS Qty\r\n FROM Orders\r\n WHERE [Order Date] = @UserStartDate;\r\n\r\n --Calc Orders created this week\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'New Orders', 'ThisWeek', COUNT([Order ID]) AS Qty\r\n FROM Orders\r\n WHERE ([Order Date] BETWEEN @StartDate and @EndDate);\r\n\r\n\r\n --Calc Orders created this month\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'New Orders', 'ThisMonth', COUNT([Order ID]) AS Qty\r\n FROM Orders\r\n where (month([Order Date]) = month(@UserStartDate) and year([Order Date]) = year(@UserStartDate) )\r\n\r\n --Calc Orders created this quarter\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'New Orders', 'ThisQtr', COUNT([Order ID]) AS Qty\r\n FROM Orders\r\n WHERE ([Order Date] BETWEEN @QuarterStartDate AND @QuarterEndDate)\r\n\r\n --Calc Orders created this year\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'New Orders', 'ThisYear', COUNT([Order ID]) AS Qty\r\n FROM Orders\r\n WHERE (year([Order Date]) = year(@UserstartDate) )\r\n\r\n --Calc order dollars created today\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'Order$','Today',SUM([Order Summary].[Order Total]) AS Total\r\n FROM [Order Summary] INNER JOIN\r\n Orders ON [Order Summary].[Order ID] = Orders.[Order ID]\r\n WHERE Orders.[Paid Date] = @UserStartDate\r\n\r\n --Calc order dollars created this week\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'Order$','ThisWeek',SUM([Order Summary].[Order Total]) AS Total\r\n FROM [Order Summary] INNER JOIN\r\n Orders ON [Order Summary].[Order ID] = Orders.[Order ID]\r\n WHERE (Orders.[Paid Date] BETWEEN @StartDate and @EndDate);\r\n\r\n --Calc order dollars created this month\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'Order$','ThisMonth',SUM([Order Summary].[Order Total]) AS Total\r\n FROM [Order Summary] INNER JOIN\r\n Orders ON [Order Summary].[Order ID] = Orders.[Order ID]\r\n where (month(Orders.[Paid Date]) = month(@UserStartDate) and year(Orders.[Paid Date]) = year(@UserStartDate) )\r\n\r\n --Calc order dollars created this quarter\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'Order$','ThisQtr',SUM([Order Summary].[Order Total]) AS Total\r\n FROM [Order Summary] INNER JOIN\r\n Orders ON [Order Summary].[Order ID] = Orders.[Order ID]\r\n WHERE (Orders.[Paid Date] BETWEEN @QuarterStartDate AND @QuarterEndDate)\r\n\r\n --Calc order dollars created this year\r\n Insert Into @Dashboard(myMetric, myTimeFrame, Qty)\r\n SELECT 'Order$','ThisYear',SUM([Order Summary].[Order Total]) AS Total\r\n FROM [Order Summary] INNER JOIN\r\n Orders ON [Order Summary].[Order ID] = Orders.[Order ID]\r\n WHERE (year(orders.[Paid Date]) = year(@UserstartDate) )\r\n\r\n Select * from @Dashboard\r\n\r\nEnd<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Congratulations Juan Soto on a great presentation this month! \u00a0I also want to thank everyone who joined the meeting and\u00a0hope that you found the webinar\u00a0informative and worthwhile. The video is now available for you to review: Here are the dashboard files:\u00a0NorthwindDash Your presence helped to make this event a great success. We wish you all [&hellip;]<\/p>\n","protected":false},"author":103,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"mc4wp_mailchimp_campaign":[],"footnotes":""},"categories":[1],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Creating Dashboards in Microsoft Access - SQL Server with Access - AccessUserGroups.org<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Creating Dashboards in Microsoft Access - SQL Server with Access - AccessUserGroups.org\" \/>\n<meta property=\"og:description\" content=\"Congratulations Juan Soto on a great presentation this month! \u00a0I also want to thank everyone who joined the meeting and\u00a0hope that you found the webinar\u00a0informative and worthwhile. The video is now available for you to review: Here are the dashboard files:\u00a0NorthwindDash Your presence helped to make this event a great success. We wish you all [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Server with Access - AccessUserGroups.org\" \/>\n<meta property=\"article:published_time\" content=\"2014-11-17T16:53:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-08-26T14:37:02+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-content\/uploads\/sites\/2\/2023\/04\/aug-share.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"675\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Susan Pyne\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Susan Pyne\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/\",\"url\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/\",\"name\":\"Creating Dashboards in Microsoft Access - SQL Server with Access - AccessUserGroups.org\",\"isPartOf\":{\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/#website\"},\"datePublished\":\"2014-11-17T16:53:39+00:00\",\"dateModified\":\"2016-08-26T14:37:02+00:00\",\"author\":{\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/#\/schema\/person\/ac5d08948c70df3e3ac5e12de3272a62\"},\"breadcrumb\":{\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Creating Dashboards in Microsoft Access\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/#website\",\"url\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/\",\"name\":\"SQL Server with Access - AccessUserGroups.org\",\"description\":\"SQL Server with Access Virtual User Group\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/#\/schema\/person\/ac5d08948c70df3e3ac5e12de3272a62\",\"name\":\"Susan Pyne\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/4ffbaa6e6f4e682364c27b9f21ffd05b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/4ffbaa6e6f4e682364c27b9f21ffd05b?s=96&d=mm&r=g\",\"caption\":\"Susan Pyne\"},\"description\":\"I started with the firm as a Sales Assistant and over the years worked my way up to Senior Access developer, I\u2019m 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!\",\"url\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/author\/susan-pyne\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Creating Dashboards in Microsoft Access - SQL Server with Access - AccessUserGroups.org","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/","og_locale":"en_US","og_type":"article","og_title":"Creating Dashboards in Microsoft Access - SQL Server with Access - AccessUserGroups.org","og_description":"Congratulations Juan Soto on a great presentation this month! \u00a0I also want to thank everyone who joined the meeting and\u00a0hope that you found the webinar\u00a0informative and worthwhile. The video is now available for you to review: Here are the dashboard files:\u00a0NorthwindDash Your presence helped to make this event a great success. We wish you all [&hellip;]","og_url":"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/","og_site_name":"SQL Server with Access - AccessUserGroups.org","article_published_time":"2014-11-17T16:53:39+00:00","article_modified_time":"2016-08-26T14:37:02+00:00","og_image":[{"width":1200,"height":675,"url":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-content\/uploads\/sites\/2\/2023\/04\/aug-share.png","type":"image\/png"}],"author":"Susan Pyne","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Susan Pyne","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/","url":"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/","name":"Creating Dashboards in Microsoft Access - SQL Server with Access - AccessUserGroups.org","isPartOf":{"@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/#website"},"datePublished":"2014-11-17T16:53:39+00:00","dateModified":"2016-08-26T14:37:02+00:00","author":{"@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/#\/schema\/person\/ac5d08948c70df3e3ac5e12de3272a62"},"breadcrumb":{"@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/creating-dashboards-in-microsoft-access\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/accessusergroups.org\/sql-server-with-access\/"},{"@type":"ListItem","position":2,"name":"Creating Dashboards in Microsoft Access"}]},{"@type":"WebSite","@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/#website","url":"https:\/\/accessusergroups.org\/sql-server-with-access\/","name":"SQL Server with Access - AccessUserGroups.org","description":"SQL Server with Access Virtual User Group","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/accessusergroups.org\/sql-server-with-access\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/#\/schema\/person\/ac5d08948c70df3e3ac5e12de3272a62","name":"Susan Pyne","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/4ffbaa6e6f4e682364c27b9f21ffd05b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ffbaa6e6f4e682364c27b9f21ffd05b?s=96&d=mm&r=g","caption":"Susan Pyne"},"description":"I started with the firm as a Sales Assistant and over the years worked my way up to Senior Access developer, I\u2019m 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!","url":"https:\/\/accessusergroups.org\/sql-server-with-access\/author\/susan-pyne\/"}]}},"_links":{"self":[{"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/posts\/449"}],"collection":[{"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/users\/103"}],"replies":[{"embeddable":true,"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/comments?post=449"}],"version-history":[{"count":4,"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/posts\/449\/revisions"}],"predecessor-version":[{"id":843,"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/posts\/449\/revisions\/843"}],"wp:attachment":[{"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/media?parent=449"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/categories?post=449"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/tags?post=449"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}