Results 1 to 4 of 4
  1. #1
    hascons is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    7

    Combobox datasource SQL question

    Hello

    I have a time sheet form on which i have a combo box that has its recordsource filtered by an SQL statement to return only employees whose "Last Worked Date" field is blank. When I enter the time for each employee into a time card table, I would like to have this employee removed from the combobox to make finding employees easier with longer lists.

    Can this be done with some kind of ( Not Select Statement )? When i create a query using the employee table and filter with " IsNull " Statement in "Last Worked Date" Field it returns the Current employee list fine. If I add the "Time Card Table" and relate the EmployeeID fields in each table, the results are to return Only the Employee entered in the "Time Card Table" which is understandable. I would actually like to return all the other Current employees in the list with the exception of the employees entered in the "Time Card Table".

    Any Ideas?

    Steve

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,878
    Is the join type an INNER? Try LEFT or RIGHT ("Include all records from Employees and only those from Time Card Table that match").
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    hascons is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    7
    Quote Originally Posted by June7 View Post
    Is the join type an INNER? Try LEFT or RIGHT ("Include all records from Employees and only those from Time Card Table that match").

    Thanks for your quick reply

    Here is the SQl Statement

    Code:
    SELECT [Employees Extended].EmployeeID, [Employees Extended].[Employee Name], [Employees Extended].DateTerminated, [Temp Time Card Entry].Dateworked
    FROM [Employees Extended] Inner JOIN [Temp Time Card Entry] ON [Employees Extended].EmployeeID = [Temp Time Card Entry].EmployeeID
    WHERE ((([Employees Extended].DateTerminated) Is Null) AND (([Temp Time Card Entry].Dateworked)>#4/16/2014#))
    ORDER BY [Employees Extended].[Employee Name];

    What i'm trying to do is as follows

    Employee Table Time Card Table
    (after filtering for just current employees) ( employee time entered )
    ---------------------------------------------------------------------

    John Smith John Smith
    Steve Jobs
    Bill Gates

    In the combobox on time sheet i would like to have ( John Smith) removed from
    the list since his time would have already been entered.

  4. #4
    hascons is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    7
    I solved this issue by using a left join and retrieving records from Time card Table where the Id was null
    I used the following SQL Statement:

    [Code]
    SELECT [Employees Extended].EmployeeID, [Employees Extended].[Employee Name], [Employees Extended].DateTerminated, [Time Card Entry].TimeCardID
    FROM [Employees Extended] LEFT JOIN [Time Card Entry] ON [Employees Extended].EmployeeID = [Time Card Entry].EmployeeID
    WHERE ((([Employees Extended].DateTerminated) Is Null) AND (([Time Card Entry].TimeCardID) Is Null))
    ORDER BY [Employees Extended].[Employee Name];

    [Code]

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 07-25-2013, 07:36 AM
  2. Change Datasource in Subform field
    By goodguy in forum Database Design
    Replies: 2
    Last Post: 04-08-2013, 03:00 PM
  3. Combobox question
    By manic in forum Forms
    Replies: 3
    Last Post: 02-05-2012, 04:55 PM
  4. Combobox Question
    By Trojnfn in forum Access
    Replies: 2
    Last Post: 10-12-2011, 11:28 AM
  5. Using an sql string as the datasource for a textbox.
    By rghollenbeck in forum Queries
    Replies: 5
    Last Post: 10-08-2011, 07:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums