Query expression Help

[Guest]

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!

 

[Ken Snell \(MVP\)]

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

 

[Guest]

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"

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

--

Ken Snell
<MS ACCESS MVP>

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!

 

[Ken Snell \(MVP\)]

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

--

Ken Snell
<MS ACCESS MVP>

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"

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

--

Ken Snell
<MS ACCESS MVP>

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!

 

[Guest]

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;

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

--

Ken Snell
<MS ACCESS MVP>

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"

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

--

Ken Snell
<MS ACCESS MVP>


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!

 

[Ken Snell \(MVP\)]

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>

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;

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

--

Ken Snell
<MS ACCESS MVP>

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"



:

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

--

Ken Snell
<MS ACCESS MVP>


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!

 

[Guest]

John eveything worked great untill I wanted to sum the totals of EMPLOYEE
TIME for each supervisors crew by the supervisors names. Chad Zablackas,
Lenny Lamatrice and Jim Balvin. On my form that the data is being collected
from I have a combo box that asked for the supervisor of the employee that I
was entering data for and its saved in the tblMain. I just wanted to bring up
a total of EMPLOYEE TIME for that perticular supervisor in a date range. I
hope this help.... Thanks!

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>

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;

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

--

Ken Snell
<MS ACCESS MVP>




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"



:

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

--

Ken Snell
<MS ACCESS MVP>


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!

 

[Ken Snell \(MVP\)]

It's Ken, not John ;-)

Try this SQL statement, based on what you want to do:

SELECT tblMain.Supervisor,
Sum([EMPLOYEE TIME]) AS SubordinatesTotalTime
FROM tblMain
WHERE tblMain.[DAYS DATE] Between [Enter Week Beginning: (mm/dd/yy)] And
[Enter Week Ending: (mm/dd/yy)]
GROUP BY tblMain.Supervisor
ORDER BY tblMain.Shift;

--

Ken Snell
<MS ACCESS MVP>

John eveything worked great untill I wanted to sum the totals of EMPLOYEE
TIME for each supervisors crew by the supervisors names. Chad Zablackas,
Lenny Lamatrice and Jim Balvin. On my form that the data is being
collected
from I have a combo box that asked for the supervisor of the employee that
I
was entering data for and its saved in the tblMain. I just wanted to bring
up
a total of EMPLOYEE TIME for that perticular supervisor in a date range. I
hope this help.... Thanks!

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>

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;


:

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

--

Ken Snell
<MS ACCESS MVP>




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"



:

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

--

Ken Snell
<MS ACCESS MVP>


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!

 

[Guest]

Ken, Thanks for the support but I got it fixed! I wanted to use the formula
in a text box on the rpt itself to calculate the totals from each supervisor.
I used:

=Abs(Sum([Supervisor]="supervisors name")*[DT
REGULAR])/Abs(Sum([Supervisor]=" supervisors name ")*[EMPLOYEE TIME])

=Sum(Abs([Supervisor]=" supervisors name ")*[EMPLOYEE TIME])

=Sum(Abs([Supervisor]=" supervisors name ")*[FOOTAGE])


All good....Thanks!

It's Ken, not John ;-)

Try this SQL statement, based on what you want to do:

SELECT tblMain.Supervisor,
Sum([EMPLOYEE TIME]) AS SubordinatesTotalTime
FROM tblMain
WHERE tblMain.[DAYS DATE] Between [Enter Week Beginning: (mm/dd/yy)] And
[Enter Week Ending: (mm/dd/yy)]
GROUP BY tblMain.Supervisor
ORDER BY tblMain.Shift;

--

Ken Snell
<MS ACCESS MVP>

John eveything worked great untill I wanted to sum the totals of EMPLOYEE
TIME for each supervisors crew by the supervisors names. Chad Zablackas,
Lenny Lamatrice and Jim Balvin. On my form that the data is being
collected
from I have a combo box that asked for the supervisor of the employee that
I
was entering data for and its saved in the tblMain. I just wanted to bring
up
a total of EMPLOYEE TIME for that perticular supervisor in a date range. I
hope this help.... Thanks!

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>




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;


:

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

--

Ken Snell
<MS ACCESS MVP>




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"



:

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

--

Ken Snell
<MS ACCESS MVP>


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!