Use field to select but not group by?

[Ann Scharpf]

Is it possible to use a field to select data but not use it as a group by
criterion? Here's what I'm trying to do:

Select all time record with a date greater than xx.
Present a single total number of hours for each task code/person combo.

Right now, I get about 15 records per person, since each pay period is its
own record/row in the query results. I then create another query that
eliminates the PPE date and then I can get the one row per person with the
total hours.

I've always wondered if there is a straightforward way to do this in a
single query or do you have to use the two step method that I have described.

Thanks very much for your help.

 

[Golfinray]

I don't quite understand what you are asking, but for totals you would
usually do it one of two ways. Make a query to sum. You don't have to use
group by, just pull your field into a query and use the little summation
button of the toolbar. That would get you a sum that you could then pull back
into your other query as the total. Or you can use a form to sum. Below the
box that has your amounts in it on the report, put a listbox and set its
control source to:
=sum([yourfield])

 

[Ann Scharpf]

Thanks for taking the time to answer. Let me be a bit more precise.

Here is the SQL for the first query:

SELECT eBizHoursRecorded.Task, eBizHoursRecorded.Function,
eBizHoursRecorded.Employee, eBizHoursRecorded.PPEdate,
eBizHoursRecorded.Project, eBizHoursRecorded.Vendor,
Sum(eBizHoursRecorded.TotalHours) AS SumOfTotalHours
FROM eBizHoursRecorded
GROUP BY eBizHoursRecorded.Task, eBizHoursRecorded.Function,
eBizHoursRecorded.Employee, eBizHoursRecorded.PPEdate,
eBizHoursRecorded.Project, eBizHoursRecorded.Vendor
HAVING (((eBizHoursRecorded.Task) Like "xa*") AND
((eBizHoursRecorded.PPEdate)>#2/16/2008#))
ORDER BY eBizHoursRecorded.Task, eBizHoursRecorded.Function,
eBizHoursRecorded.Employee;

It gives me results that look like this:

Task Function Employee PPEdate Project Vendor SumOfTotalHours
XA07B02 TBJ00 BLAKE, VICTOR J 10/11/2008 09249115ABNP FM0007-1 63
XA07B02 TBJ00 BLAKE, VICTOR J 10/25/2008 09249115ABNP FM0007-1 63.5
XA07B02 TBJ00 BLAKE, VICTOR J 11/8/2008 09249115ABNP FM0007-1 61
XA07B02 TBJ00 PURNELL, TERRY 10/25/2008 09249115ABNP FM0007-1 72
XA07B02 TBJ00 PURNELL, TERRY 11/8/2008 09249115ABNP FM0007-1 80

__________________________

I don't want three rows for Victor and two rows for Terry. I want one row
each with a grand total. So I have a second query that eliminates the date,
which I was only using to filter out records that are older than I want.
Here's the SQL for the second query:

SELECT [^+^ABOMHrsSinceFeb16].Task, [^+^ABOMHrsSinceFeb16].Function,
[^+^ABOMHrsSinceFeb16].Employee, [^+^ABOMHrsSinceFeb16].Project,
[^+^ABOMHrsSinceFeb16].Vendor, Sum([^+^ABOMHrsSinceFeb16].SumOfTotalHours) AS
TotalHrs
FROM [^+^ABOMHrsSinceFeb16]
GROUP BY [^+^ABOMHrsSinceFeb16].Task, [^+^ABOMHrsSinceFeb16].Function,
[^+^ABOMHrsSinceFeb16].Employee, [^+^ABOMHrsSinceFeb16].Project,
[^+^ABOMHrsSinceFeb16].Vendor;


This query give me the result I want – one line per employee:

Task Function Employee Project Vendor TotalHrs
XA07B02 TBJ00 BLAKE, VICTOR J 09249115ABNP FM0007-1 187.50
XA07B02 TBJ00 PURNELL, TERRY 09249115ABNP FM0007-1 152.00


_______________

I'd like to know if it is possible to do this in a single query instead of
creating one as a feeder for another.
--
Ann Scharpf

I don't quite understand what you are asking, but for totals you would
usually do it one of two ways. Make a query to sum. You don't have to use
group by, just pull your field into a query and use the little summation
button of the toolbar. That would get you a sum that you could then pull back
into your other query as the total. Or you can use a form to sum. Below the
box that has your amounts in it on the report, put a listbox and set its
control source to:
=sum([yourfield])

Is it possible to use a field to select data but not use it as a group by
criterion? Here's what I'm trying to do:

Select all time record with a date greater than xx.
Present a single total number of hours for each task code/person combo.

Right now, I get about 15 records per person, since each pay period is its
own record/row in the query results. I then create another query that
eliminates the PPE date and then I can get the one row per person with the
total hours.

I've always wondered if there is a straightforward way to do this in a
single query or do you have to use the two step method that I have described.

Thanks very much for your help.

 

[KARL DEWEY]

Use a Totals query. In design view add table, drag or double click task,
people, hours and datetime fields to place in Field row of the grid. Click
on Greek symbol Sigma (looks like an 'M' on its side). In the Group By row
for the hours field change Group By to Sum. In the Group By row for the
datetime field change Group By to Where.
In datetime criteria row enter --

=CVDate([Enter from date like- 2/15/2008])

 

[Ann Scharpf]

Where! I forgot all about WHERE! Thank you so much. That was the missing
link.
--
Ann Scharpf

Use a Totals query. In design view add table, drag or double click task,
people, hours and datetime fields to place in Field row of the grid. Click
on Greek symbol Sigma (looks like an 'M' on its side). In the Group By row
for the hours field change Group By to Sum. In the Group By row for the
datetime field change Group By to Where.
In datetime criteria row enter --

=CVDate([Enter from date like- 2/15/2008])

--
KARL DEWEY
Build a little - Test a little

Is it possible to use a field to select data but not use it as a group by
criterion? Here's what I'm trying to do:

Select all time record with a date greater than xx.
Present a single total number of hours for each task code/person combo.

Right now, I get about 15 records per person, since each pay period is its
own record/row in the query results. I then create another query that
eliminates the PPE date and then I can get the one row per person with the
total hours.

I've always wondered if there is a straightforward way to do this in a
single query or do you have to use the two step method that I have described.

Thanks very much for your help.