p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

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
>
>
>


  Return to Index