Query source for a Dashboard table.

Brian Thor
Brian Thor used Ask the Experts™
on
I want to create a dashboard in an Access application which connects to data store in SQL Server. I have created a mock up of how I would like to display a table reporting actual to goal for employee hours.  The SQL for the actual and goal is no problem. I am looking for advice as to how you would build the query and then display it. Would you create a table-valued function or take some other approach?

 Employee hours - actual to goal.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
I would display the query as a subform.
John TsioumprisSoftware & Systems Engineer

Commented:
How about a passthrough query that you will use it as Source to feed the form/report.
You just have to make the connection and paste your existing SQL code without modifications.
Take a look at this tutorial from MS to get you started..and also this tutorial which is a bit more detailed
Brian ThorPresident

Author

Commented:
I'm sorry guys, I don't think I did a good job of explaining my problem. Let me try again.

The way I see it I have two queries and two calculations using the results of those queries. The first query finds the actual number of hours worked for each period. The second query finds the goal hours for the same periods. If this was all I wanted I would UNION them together and display the result. The problem is that I want to have two calculations for each column, Excel style, as shown in my sample.

Since there are just a few columns I suppose I could write a function to calculate the over/under hours and percent and then UNION those results with the first two queries. Maybe I'm just too lazy but that just seems really cumbersome. As I think about this more it seems like I either need to embed an Excel sheet somehow or just create a subform with fields for all of the results. One reason is that I'll want to display the percentage with the % sign and I also want to have a scheme of colors like green, blue, yellow, red to indicate how far away from goal an actual might be.

Let me know what you think. Thanks.
Distinguished Expert 2017

Commented:
There is no simple way to do this in Access since you essentially have a spreadsheet and since you have a spreadsheet, each calculated value has a specific formula.  Rather than using a subform as I suggested, it will be easier to use a report where you can more easily embed calculations. The report can be embedded as a subform.  The two rows from the union are bound to the detail section of the report as you would do for any recordset.  The total rows are in the footer section and will contain calculations.  The most efficient way to do the calculations is to open two recordsets in the format event of the footer.  Then do the calculations based on the two recordsets.  Name them appropriately.

Me.txtHoursTodayDif = rsActual.HoursToday - rsGoal.HoursToday
Me.txtHoursThisWeekDif = rsActual.HoursThisWeek - rsGoal.HoursThisWeek
etc.
If Nz(rsGoal.HoursToday,0) = 0 Then
    Me.txtHoursTodayPct = Null
Else
    Me.txtHoursTodayPct = rsActual.Hourstoday / rsGoal.HoursToday
End If
If Nz(rsGoal.HoursThisWeek, 0) = 0 Then
    Me.txtHoursThisWeekPct = Null
Else
    Me.txtHoursThisWeekPct = rsActual.HoursThisWeek / rsGoal.HoursThisWeek
End If
etc.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial