Query source for a Dashboard table.

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.
Brian ThorPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
I would display the query as a subform.
John TsioumprisSoftware & Systems EngineerCommented:
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 ThorPresidentAuthor 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.
PatHartmanCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.