Re: Query expression Help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Sorry. ... my error. I neglected to note that you were putting a Criteria on
an aggregate function field.

However, before I try to provide a revised SQL statement for you, you need
to restate your logic. In the SQL statement that you posted, it appears that
you are wanting to sum the employee time for various employees, all in one
record. Don't you just want to show the sum of employee time for each
employee, in separate records? The WHERE clause syntax that you're showing
will always return zero records because it appears you want to select
records where the employee name field contains all three employee names,
which is not possible.

So, state in words what you want the query to do, and let's go from there.
--

Ken Snell
<MS ACCESS MVP>




"Chad" <Chad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:69286534-CACC-4015-9CB4-D544E5C9A478@xxxxxxxxxxxxxxxx
Its giving me an error Cannot have aggreate function in Where
clause(tblMain.[DAYS DATE] Between [Enter Week Begining: (mm/dd/yy)] And
[Enter Week Ending: (mm/dd/yy)] And Sum([EMPLOYEE TIME])="Chad Zablackas"
And
Sum([EMPLOYEE TIME])="Lenny Lamatrice" And Sum([EMPLOYEE TIME])="Jim
Balvin"). Here is the SQL.

SELECT tblMain.FOOTAGE, tblMain.[Product Description], tblMain.Supervisor,
tblMain.Shift, tblMain.[EMPLOYEE NAME], tblMain.[DT REGULAR],
tblMain.[Machine Ran], tblMain.[Total Footage], tblMain.[DAYS DATE],
tblMain.[EMPLOYEE TIME], ([DT REGULAR])/([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2]) AS [Delay Percent], tblMain.[DT Reason 1],
tblMain.[DT Reason 01], tblMain.[DT Reason 2], tblMain.[DT Reason 02],
tblMain.[DT MAINTENANCE], Sum([EMPLOYEE TIME]) AS [Chad Zablackas],
Sum([EMPLOYEE TIME]) AS [Lenny Lamatrice], Sum([EMPLOYEE TIME]) AS [Jim
Balvin]
FROM tblMain
WHERE (((tblMain.[DAYS DATE]) Between [Enter Week Beginning: (mm/dd/yy)]
And
[Enter Week Ending: (mm/dd/yy)]) AND ((Sum([EMPLOYEE TIME]))="Chad
Zablackas") AND ((Sum([EMPLOYEE TIME]))="Lenny Lamatrice") AND
((Sum([EMPLOYEE TIME]))="Jim Balvin"))
ORDER BY tblMain.Shift;


"Ken Snell (MVP)" wrote:

Field: Bill Black: Sum([EMPLOYEE TIME])
Criteria: "Bill Black"

--

Ken Snell
<MS ACCESS MVP>




"Chad" <Chad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F17EBDB0-B368-4F7E-BA2A-C73C23EE4318@xxxxxxxxxxxxxxxx
Not quite sure I understand what you mean. Can I do it this way? But it
asks
for the SumOfEMPLOYEE TIME when I run the query? I have to do this for
3
supervisors..Thanks!

Field: Bill Black: [SumOfEMPLOYEE TIME]
Criteria: "Bill Black"



"Ken Snell (MVP)" wrote:

SELECT EmployeeName, Sum([EMPLOYEE TIME]) AS TotalEmpTime
FROM TableName
WHERE EmployeeName = 'Bill Black'
GROUP BY EmployeeName;

--

Ken Snell
<MS ACCESS MVP>


"Chad" <Chad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6D4FF467-179C-4ACB-8997-46F1D2DD952B@xxxxxxxxxxxxxxxx
Hello, I have a query that I want to have an expression and I wnat
to
use
it
on a RPT but everytime I write it it asks the expression after it
asks
for
my
date range. I want to sum EMPLOYEE TIME for a perticular supervisor
say
Bill
Black. How would I write this in the query as an expression and the
criteria
being Bill Black?

Thanks!








.

Relevant Pages

  • Re: Extract Records via Userform
    ... The criteria range can have multiple rows and deliver some strange combinations of data. ... The employee has transferred ... charged while he has been working for his current supervisor that the ...
    (microsoft.public.excel.programming)
  • Re: Mutli value field? - Memo field?
    ... the perfromance of an employee against set criteria. ... 20 criteria to be measured by the person monitoring the employee's ... I guess I need a memo field for MeasuresFailed, ...
    (microsoft.public.access.forms)
  • Re: Report parameters
    ... It looks to me like you should add some option boxes on to you criteria ... selection form with the options All, Range, Single. ... I have a button called cmdfilter, two option boxes - 1 called optDateRange ... > PK from the Employee table); ...
    (microsoft.public.access.reports)
  • Re: Not like
    ... The field is text and is used to store notations or employee status and ... number and if not scheduled it will have the word Off and that criteria work ... for that field to exclude the employee Off or not scheduled. ...
    (microsoft.public.access.queries)
  • Re: Subscript Out of Range Error
    ... "JLGWhiz" wrote: ... an area that he, at some point, decides he wants to add new criteria to ... it to the list in the QA Template and each employee workbook so that all ... that will be mirrored in all employee workbooks and the QA ...
    (microsoft.public.excel.programming)