Solved

# Access Query

Posted on 2013-10-23
246 Views
Hi guys

I am trying to get Month to date sum from two tables and cal efficiency its giving an error
That expression is not include as  a part of aggregate  function.

``````SELECT SETTLEMENT.[SDate], SETTLEMENT.[RepName], Sum(SETTLEMENT.[Hours Work]) AS [SumOfHours Work], Sum(SETTLEMENT.[Swift]) AS SumOfSwift, Sum(SETTLEMENT.[Phone Calls]) AS [SumOfPhone Calls], Sum(SETTLEMENT.[Meetings]) AS SumOfMeetings, Sum(SETTLEMENT.[Training]) AS SumOfTraining, Sum(SETTLEMENT.[Compliance]) AS SumOfCompliance, Sum(SETTLEMENT.[QA]) AS SumOfQA, Sum(SETTLEMENT.[Oracle Input]) AS [SumOfOracle Input], Sum(SETTLEMENT.[Bank Input]) AS [SumOfBank Input], Sum(SETTLEMENT.[Bank Auth]) AS [SumOfBank Auth], Sum(SETTLEMENT.[Insurances]) AS SumOfInsurances, Sum(SETTLEMENT.[Other]) AS SumOfOther, Sum(SettDPCAT.NewSettled) AS NS, Sum(SettDPCAT.NewHeld) AS NH, Sum(SettDPCAT.HeldSettled) AS HS, Sum(SettDPCAT.HeldReHeld) AS HRH, Sum((([Hours Work])*86400)) AS TH, ((([NS]*1200+[NH]*1500+[HS]*300+[HRH]*300+[S]*90+[Phone Calls]*120+[Meetings]*1800+[Training]*1800+[Compliance]*1800+[QA]*1800+[Oracle Input]*90+[Bank Input]*120+[Bank Auth]*60+[Insurances]*90+[Other]*1800)/([TH]))) AS Efficiency
FROM SettDPCAT INNER JOIN SETTLEMENT ON (SettDPCAT.RepName = SETTLEMENT.RepName) AND (SettDPCAT.[Today Date] = SETTLEMENT.SDate)
GROUP BY SETTLEMENT.[SDate], SETTLEMENT.[RepName]
HAVING (((SETTLEMENT.[SDate]) Between [sd] And [ed]));
``````
0
Question by:surah79
• 3

LVL 26

Accepted Solution

jerryb30 earned 500 total points
ID: 39595836
Maybe:
``````SELECT SETTLEMENT.[SDate], SETTLEMENT.[RepName], Sum(SETTLEMENT.[Hours Work]) AS [SumOfHours Work], Sum(SETTLEMENT.[Swift]) AS SumOfSwift, Sum(SETTLEMENT.[Phone Calls]) AS [SumOfPhone Calls], Sum(SETTLEMENT.[Meetings]) AS SumOfMeetings, Sum(SETTLEMENT.[Training]) AS SumOfTraining, Sum(SETTLEMENT.[Compliance]) AS SumOfCompliance, Sum(SETTLEMENT.[QA]) AS SumOfQA, Sum(SETTLEMENT.[Oracle Input]) AS [SumOfOracle Input], Sum(SETTLEMENT.[Bank Input]) AS [SumOfBank Input], Sum(SETTLEMENT.[Bank Auth]) AS [SumOfBank Auth], Sum(SETTLEMENT.[Insurances]) AS SumOfInsurances, Sum(SETTLEMENT.[Other]) AS SumOfOther, Sum(SettDPCAT.NewSettled) AS NS, Sum(SettDPCAT.NewHeld) AS NH, Sum(SettDPCAT.HeldSettled) AS HS, Sum(SettDPCAT.HeldReHeld) AS HRH, Sum((([Hours Work])*86400)) AS TH, ((([NS]*1200+[NH]*1500+[HS]*300+[HRH]*300+[S]*90+[Phone Calls]*120+[Meetings]*1800+[Training]*1800+[Compliance]*1800+[QA]*1800+[Oracle Input]*90+[Bank Input]*120+[Bank Auth]*60+[Insurances]*90+[Other]*1800)/([TH]))) AS Efficiency
FROM SettDPCAT INNER JOIN SETTLEMENT ON (SettDPCAT.RepName = SETTLEMENT.RepName) AND (SettDPCAT.[Today Date] = SETTLEMENT.SDate)
GROUP BY SETTLEMENT.[SDate], SETTLEMENT.[RepName],(([NS]*1200+[NH]*1500+[HS]*300+[HRH]*300+[S]*90+[Phone Calls]*120+[Meetings]*1800+[Training]*1800+[Compliance]*1800+[QA]*1800+[Oracle Input]*90+[Bank Input]*120+[Bank Auth]*60+[Insurances]*90+[Other]*1800)/([TH])))
HAVING (((SETTLEMENT.[SDate]) Between [sd] And [ed]));
``````
0

LVL 26

Expert Comment

ID: 39595857
Can you post a small (sanitized) sample, with your query? And desired results? If this is with linked tables, you'll have to spoof things by importing necessary tables/data.
0

Author Closing Comment

ID: 39595939
Thanks a lot
0

LVL 26

Expert Comment

ID: 39595955
Just curious. Why the B?
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question