|
 |
access thread: Dumb question about a query
Message #1 by "John Pace" <jpace@h...> on Thu, 13 Jun 2002 10:07:33 -0500
|
|
Ok, this may be the dumbest question ever, so please forgive me, but I am
having a brain meltdown.
I have a query that gets EmployeeID, Date and HoursWorked. This query is
the basis of a report.
The query only selects the records that fall in a date range provided by the
user. All this
works fine. My problem is that I want the query to only get records in
which the total of
HoursWorked for the given date range is greater than 40.
I know I am missing something obvious, so I would be very grateful if you
all would help me.
John Pace
Manager of Information Technologies
Haws and Tingle General Contractors
650 West Vickery Blvd
Fort Worth, TX 76104
xxx-xxx-xxxx
Fax: xxx-xxx-xxxx
E-Mail: jpace@h...
www.hawstingle.com
Message #2 by "John Pace" <jpace@h...> on Thu, 13 Jun 2002 10:24:39 -0500
|
|
I'm sorry, stated the problem incorrectly. For each EmployeeID, I want the
query to get records in which the total of
HoursWorked for the given date range is greater than 40. I hope that makes
sense. Basically, if employee 1234 worked
32 hours for the date range, don't show his records. If he worked 42 hours,
then show them.
-----Original Message-----
From: John Pace [mailto:jpace@h...]
Sent: Thursday, June 13, 2002 10:08 AM
To: Access
Subject: Dumb question about a query
Ok, this may be the dumbest question ever, so please forgive me, but I am
having a brain meltdown.
I have a query that gets EmployeeID, Date and HoursWorked. This query is
the basis of a report.
The query only selects the records that fall in a date range provided by the
user. All this
works fine. My problem is that I want the query to only get records in
which the total of
HoursWorked for the given date range is greater than 40.
I know I am missing something obvious, so I would be very grateful if you
all would help me.
John Pace
Manager of Information Technologies
Haws and Tingle General Contractors
650 West Vickery Blvd
Fort Worth, TX 76104
xxx-xxx-xxxx
Fax: xxx-xxx-xxxx
E-Mail: jpace@h...
www.hawstingle.com
Message #3 by "Carnley, Dave" <dcarnley@a...> on Thu, 13 Jun 2002 10:32:38 -0500
|
|
select employeeid, sum(hoursworked)
from WorkTable
where date >= mindate and date <= maxdate
group by employeeid
having sum(hoursworked) >= 40
-----Original Message-----
From: John Pace [mailto:jpace@h...]
Sent: Thursday, June 13, 2002 10:25 AM
To: Access
Subject: [access] FW: Dumb question about a query
I'm sorry, stated the problem incorrectly. For each EmployeeID, I want the
query to get records in which the total of
HoursWorked for the given date range is greater than 40. I hope that makes
sense. Basically, if employee 1234 worked
32 hours for the date range, don't show his records. If he worked 42 hours,
then show them.
-----Original Message-----
From: John Pace [mailto:jpace@h...]
Sent: Thursday, June 13, 2002 10:08 AM
To: Access
Subject: Dumb question about a query
Ok, this may be the dumbest question ever, so please forgive me, but I am
having a brain meltdown.
I have a query that gets EmployeeID, Date and HoursWorked. This query is
the basis of a report.
The query only selects the records that fall in a date range provided by the
user. All this
works fine. My problem is that I want the query to only get records in
which the total of
HoursWorked for the given date range is greater than 40.
I know I am missing something obvious, so I would be very grateful if you
all would help me.
John Pace
Manager of Information Technologies
Haws and Tingle General Contractors
650 West Vickery Blvd
Fort Worth, TX 76104
xxx-xxx-xxxx
Fax: xxx-xxx-xxxx
E-Mail: jpace@h...
www.hawstingle.com
Message #4 by Howard Henderson <howardh@o...> on Thu, 13 Jun 2002 11:38:25 -0500
|
|
When I've had to do similar queries, I have created a "core" query (named
core in this example):
select emp_id from hours_table
where date-range = xyz
having sum(hours) > 40;
and then used a second query like:
select all_other_fields
from other_tables, core
where other_tables.emp_id = core.emp_id;
This is is just a rough paraphrase of true SQL syntax, but hopefully enough
to get the idea...
I'd welcome any comments on this approach, since I don't know how
"efficient" it is in relation to other approaches.
Howard H.
----- Original Message -----
From: "John Pace" <jpace@h...>
To: "Access" <access@p...>
Sent: Thursday, June 13, 2002 10:24 AM
Subject: [access] FW: Dumb question about a query
> I'm sorry, stated the problem incorrectly. For each EmployeeID, I want
the
> query to get records in which the total of
> HoursWorked for the given date range is greater than 40. I hope that
makes
> sense. Basically, if employee 1234 worked
> 32 hours for the date range, don't show his records. If he worked 42
hours,
> then show them.
>
Message #5 by "Wesley Kendrick" <wez.k@n...> on Fri, 14 Jun 2002 01:57:52 +0100
|
|
Hi, open the query in Design view, then insert ' >=40 ' as the criteria of
the HoursWorked field.
Regards, Wesley Kendrick
----- Original Message -----
From: "John Pace" <jpace@h...>
To: "Access" <access@p...>
Sent: Thursday, June 13, 2002 4:07 PM
Subject: [access] Dumb question about a query
> Ok, this may be the dumbest question ever, so please forgive me, but I am
> having a brain meltdown.
>
> I have a query that gets EmployeeID, Date and HoursWorked. This query is
> the basis of a report.
> The query only selects the records that fall in a date range provided by
the
> user. All this
> works fine. My problem is that I want the query to only get records in
> which the total of
> HoursWorked for the given date range is greater than 40.
>
> I know I am missing something obvious, so I would be very grateful if you
> all would help me.
>
> John Pace
> Manager of Information Technologies
> Haws and Tingle General Contractors
> 650 West Vickery Blvd
> Fort Worth, TX 76104
> xxx-xxx-xxxx
> Fax: xxx-xxx-xxxx
> E-Mail: jpace@h...
> www.hawstingle.com
>
>
>
|
|
|
View Cart
