Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listing Inactive Users (Access 2K)

    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?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listing Inactive Users (Access 2K)

    You can create a query based on the user table and the query you already have. Join the table and query on the unique identifier of the user, then double click the join and specify that you want all records from the user table. This is the left join you mention.

    Add fields to the query grid from the user table that you want in the report. Also add the user identifier from the query, clear the Show check box and set Is Null as criteria for this field.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listing Inactive Users (Access 2K)

    Thanks, but this part I already figured out.

    It needs to be understood that this capability 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. That's why I was questioning the possibility of nesting Selects in SQL.

    Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listing Inactive Users (Access 2K)

    Yes, it is possible to nest SQL statements, for example:

    SELECT UserID FROM tblUSERS WHERE UserID Not In (SELECT UserID FROM tblWorkedHours WHERE Month(SomeDate) = Forms!frmInput!txtMonth And Year(SomeDate) = Forms!frmInput!txtYear)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •