I'm working on a database app that tracks software metrics data for
our team. In it is a talbe that contains a list of users and another
that contains a collections of time records consisting of a date and
number of hours worked (among other things). Our supervisor wants a
report of those users that haven't entered anything for a given month.
Now, I have a query that produces a list of users that did enter data
and reports the number of hours each of them reported. A form provides
the capability to select the month and year for the report and sets
the Where condition for the report at print time.

My problem is that for the users that haven't entered for the month in
question I need to take the result of the aforementioned query WITH
the where condition passed to it by the form and do an outer join (i
think thats right, it's a left join in a test query I'm playing with)
with the table that contains all the users. How do I get that into a
report and still allow a user to provide the month/year input they
need? Is there a way in SQL to 'nest' one select inside another? Is
that even the right approach?

It needs to be understood that this needs to be driven by a form. The
users of the tool are not Access users and won't know how to build a
query. I am able to put the queries together to get what I want, but
I can't figure out how to implement it in a form that lets the user
select a month and year and have the app spit out the list.

Thanks.

Bill Lugg, Engineer,
MilStar AF Command Post Terminal Software Support
OO-ALC/TISFE, Peterson AFB, CO

William:
This query will do what you want with just a small fix:

SELECT Names.Name, DatePart("m",[worked]![wdate]) AS [Month],
Sum(worked.hours) AS SumOfhours
FROM [Names] LEFT JOIN worked ON Names.Name = worked.Name
GROUP BY Names.Name, DatePart("m",[worked]![wdate])
HAVING (((Sum(worked.hours)) Is Null));

Create a new qury and paste this into the sql view and then look at it
in design.
You'll need to privide a month number as a criteria which you can do
from a form. Text fields on forms are available as long as the form
is open. So if the form has a field named MNum and the form is named
NoReporting, the creteria in the query would be:
forms!noreporting!MNum

Good Luck
Ira Solomon

On 12 Aug 2003 14:30:20 -0700, william.lugg (AT) cisf (DOT) af.mil (William Lugg)
wrote:

If Ira's suggestions doesn't do what you want, try involving a Subselect in
your query:

SELECT * FROM YourTable WHERE lngEmpID NOT IN (SELECT * FROM
TheQueryYouBuilt) WHERE <<your criteria here>>


"William Lugg" <william.lugg (AT) cisf (DOT) af.mil> wrote