Solved

Access Query

Posted on 2013-10-23
4
246 Views
Last Modified: 2013-10-23
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]));

Open in new window

0
Comment
Question by:surah79
  • 3
4 Comments
 
LVL 26

Accepted Solution

by:
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]));

Open in new window

0
 
LVL 26

Expert Comment

by:jerryb30
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

by:surah79
ID: 39595939
Thanks a lot
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39595955
Just curious. Why the B?
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question