Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need help resolving aggregate function error in totals query in Access.

Posted on 2012-03-13
7
Medium Priority
?
341 Views
Last Modified: 2012-03-13
Here is the query I'm trying to run;
query
Here is the sql;
SELECT Sum(dbo_View_LaborDtl.ldLaborHrs) AS SumOfldLaborHrs,
Sum(dbo_View_LaborDtl.ldEarnedHrs) AS SumOfldEarnedHrs,
IIf([ldLaborRate]=0,[ebLaborRate],[ldLaborRate]) AS AdjLaborRate,
Round16([ldLaborHrs]*[AdjLaborRate],2) AS ldAmount
FROM dbo_View_LaborDtl
LEFT JOIN dbo_View_EmpBasic
ON dbo_View_LaborDtl.ldEmployeeNum = dbo_View_EmpBasic.ebEmpId
WHERE (((dbo_View_LaborDtl.ldPayrollDate)>=Eval('[Forms]![frmCSLPL]![txtStartDate]')
And (dbo_View_LaborDtl.ldPayrollDate)<=Eval('[Forms]![frmCSLPL]![txtEndDate]'))
AND ((dbo_View_LaborDtl.ldLaborType)<>"I") AND ((dbo_View_LaborDtl.ldLaborHedSeq)<>0));


And here is the error I'm getting;

error
Thanks in advance for the help!
0
Comment
Question by:SeyerIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 11

Expert Comment

by:Runrigger
ID: 37714220
Try this, basically, you are trying to sum two fields, but not sum the two remaining ones, I have made an assumption that you need to sum all 4 of the returned fields.

SELECT Sum(dbo_View_LaborDtl.ldLaborHrs) AS SumOfldLaborHrs,
Sum(dbo_View_LaborDtl.ldEarnedHrs) AS SumOfldEarnedHrs,
Sum(IIf([ldLaborRate]=0,[ebLaborRate],[ldLaborRate])) AS AdjLaborRate,
Sum(Round16([ldLaborHrs]*[AdjLaborRate],2)) AS ldAmount
FROM dbo_View_LaborDtl
LEFT JOIN dbo_View_EmpBasic
ON dbo_View_LaborDtl.ldEmployeeNum = dbo_View_EmpBasic.ebEmpId
WHERE (((dbo_View_LaborDtl.ldPayrollDate)>=Eval('[Forms]![frmCSLPL]![txtStartDate]')
And (dbo_View_LaborDtl.ldPayrollDate)<=Eval('[Forms]![frmCSLPL]![txtEndDate]'))
AND ((dbo_View_LaborDtl.ldLaborType)<>"I") AND ((dbo_View_LaborDtl.ldLaborHedSeq)<>0));
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37714228
try replacing "Expression" with "Last" or "First"
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37714236
Runrigger, I tried your sql and am getting the following error;
Capricorn, I also tried your method and got the same error.
error
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 11

Accepted Solution

by:
Runrigger earned 2000 total points
ID: 37714252
Try this.

SELECT Sum(dbo_View_LaborDtl.ldLaborHrs) AS SumOfldLaborHrs,
Sum(dbo_View_LaborDtl.ldEarnedHrs) AS SumOfldEarnedHrs,
Sum(IIf([ldLaborRate]=0,[ebLaborRate],[ldLaborRate])) AS AdjLaborRate,
Round16(Sum([ldLaborHrs]*IIf([ldLaborRate]=0,[ebLaborRate],[ldLaborRate])),2) AS ldAmount
FROM dbo_View_LaborDtl
LEFT JOIN dbo_View_EmpBasic
ON dbo_View_LaborDtl.ldEmployeeNum = dbo_View_EmpBasic.ebEmpId
WHERE (((dbo_View_LaborDtl.ldPayrollDate)>=Eval('[Forms]![frmCSLPL]![txtStartDate]')
And (dbo_View_LaborDtl.ldPayrollDate)<=Eval('[Forms]![frmCSLPL]![txtEndDate]'))
AND ((dbo_View_LaborDtl.ldLaborType)<>"I") AND ((dbo_View_LaborDtl.ldLaborHedSeq)<>0));
0
 
LVL 2

Author Closing Comment

by:SeyerIT
ID: 37714259
That works! Thanks for the help!
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 37714281
a pleasure, and thanks for the points
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37714283
No problem!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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