Query by Contact

[svtguy02]

Hi all,

I've written a query in SQL View. To be honest, its my first SQL Query EVER and I'm very happy that it works for half of what I need to do.

I'll paste the query here:

SELECT DISTINCT
Abs(Avg([Handoff Tracking].[Internal Kickoff?])) AS [Avg Of Internal Kickoff?],
Abs(Avg([Handoff Tracking].[E-Mailed prior to visit?])) AS [Avg Of E-Mailed prior to visit?],
Abs(Avg([Handoff Tracking].[Call to Support?])) AS [Avg Of Call to Support?],
Abs(Avg([Handoff Tracking].[Transfer to Support?])) AS [Avg Of Transfer to Support?],
Abs(Avg([Handoff Tracking].[Recieve Post trip Info?])) AS [Avg Of Recieve Post trip Info?],
Abs(Avg([Handoff Tracking].[Total Workstations])) AS [Avg Of Total Workstations],
Abs(Avg([Handoff Tracking].[Failed workstations])) AS [Avg Of Failed workstations],
Abs(Avg([Handoff Tracking].[Config File Issues?])) AS [Avg Of Config File Issues?],
Abs(Avg([Handoff Tracking].[3rd Party App Issues?])) AS [Avg Of 3rd Party App Issues?],
Abs(Avg([Handoff Tracking].[Welcome Pack Reviewed?])) AS [Avg Of Welcome Pack Reviewed?]
FROM [Handoff Tracking];

As you probably see above it does the average of [table name].[field name] then outside THOSE brackets it does the absolute value of that averaged value (the numbers USED to show up as negative numbers.

This query is great becuase it checks the average value of ALL records regardless of contact, which is good becuase I need something to compare each contact to.

Now I need to get a query that will do the average value, for all fields PER contact!

What would be the string to narrow the range of the average equation down to a per contact basis.
As far as relationships go I have 2 tables 1 for contacts.... and 1 for handoff tracking very simple DB structure wise. I just need to know how to do the same query as above, on a per user basis. Thanks for any input anyone might have

 

[svtguy02]

I think I've figured it out:

Here is to get the PERSONAL totals percent value:

SELECT DISTINCT
Format(Abs(Avg([Handoff Tracking].[Internal Kickoff?])),'Percent') AS [Personal Internal Kickoff Percentage],
Format(Abs(Avg([Handoff Tracking].[E-Mailed prior to visit?])),'Percent') AS [Personal E-Mailed prior to visit Percentage],
Format(Abs(Avg([Handoff Tracking].[Call to Support?])),'Percent') AS [Personal Call to Support Percentage],
Format(Abs(Avg([Handoff Tracking].[Transfer to Support?])),'Percent') AS [Personal Transfer to Support Percentage],
Format(Abs(Avg([Handoff Tracking].[Recieve Post trip Info?])),'Percent') AS [Personal Recieve Post Trip Info Percentage],

Format(Abs(Avg([Handoff Tracking].[Total Workstations])),'Fixed') AS [Personal Average of Total Workstations],
Format(Abs(Avg([Handoff Tracking].[Failed workstations])),'Fixed') AS [Personal Average of Failed Workstations],

Format(Abs(Avg([Handoff Tracking].[Config File Issues?])),'Percent') AS [Personal Config File Issues Percentage],
Format(Abs(Avg([Handoff Tracking].[3rd Party App Issues?])),'Percent') AS [Personal 3rd Party App Issues Percentage],
Format(Abs(Avg([Handoff Tracking].[Welcome Pack Reviewed?])),'Percent') AS [Personal Welcome Pack Reviewed Percentage], [Handoff Tracking].[Project Manager],
Format(Count(*),'General Number') AS [Total Projects]
FROM [Handoff Tracking]
GROUP BY [Handoff Tracking].[Project Manager];


Here is to get the entire GROUPS Total Percent value:

SELECT
Format(Abs(Avg([Handoff Tracking].[Internal Kickoff?])),'Percent') AS [Internal Kickoff Percentage],
Format(Abs(Avg([Handoff Tracking].[E-Mailed prior to visit?])),'Percent') AS [E-Mailed prior to visit Percentage],
Format(Abs(Avg([Handoff Tracking].[Call to Support?])),'Percent') AS [Call to Support Percentage],
Format(Abs(Avg([Handoff Tracking].[Transfer to Support?])),'Percent') AS [Transfer to Support Percentage],
Format(Abs(Avg([Handoff Tracking].[Recieve Post trip Info?])),'Percent') AS [Recieve Post Trip Info Percentage],
Format(Abs(Avg([Handoff Tracking].[Total Workstations])),'Fixed') AS [Average of Total Workstations],
Format(Abs(Avg([Handoff Tracking].[Failed workstations])),'Fixed') AS [Average of Failed Workstations],
Format(Abs(Avg([Handoff Tracking].[Config File Issues?])),'Percent') AS [Config File Issues Percentage],
Format(Abs(Avg([Handoff Tracking].[3rd Party App Issues?])),'Percent') AS [3rd Party App Issues Percentage],
Format(Abs(Avg([Handoff Tracking].[Welcome Pack Reviewed?])),'Percent') AS [Welcome Pack Reviewed Percentage],
Format(Count(*),'General Number') AS [Total Projects]
FROM [Handoff Tracking];