Results 1 to 6 of 6
  1. #1
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187

    Query tweak

    This db is for scheduling employees to go to events. I'm trying to make it easier for the schedulers to only populate the people combobox with people who are not already scheduled for the date they are currently scheduling (the combo is populated after a date is chosed on the form, which is Forms!Navigation!NavigationSubform.Form!EventDate) . Forms!Navigation!NavigationSubform.Form!txtID is the current event being entered.

    -When an event is created, the Events table holds the ID of the event and the event date
    -When a person is assigned to an event, a record is created in attendance table with the Event ID and Security ID

    My logic is:

    Code:
    Where not exists 
    -Event for same as selected date
    -Event ID <> current event ID
    -EventID = Attendance.EventID AND Security.ID = Attendance.EmployeeID
    Tables:


    Code:
    Events
    -ID
    -EventDate
    
    Security
    -ID
    -FirstName
    -LastName
    
    Attendance
    -EventID (=Events.ID)
    -EmployeeID (=Security.ID)
    What I added to the existing query
    Code:
        AND Exists (
         SELECT Security.ID 
         FROM Security, Events, Attendance 
         WHERE ((Events.EventDate = Forms!Navigation!NavigationSubform.Form!EventDate) AND 
          (Events.ID <> Forms!Navigation!NavigationSubform.Form!txtID)) AND 
          (Events.ID=Attendance.EventID AND 
          Attendance.EmployeeID = Security.ID))=False))

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    What is your question?
    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
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    Quote Originally Posted by June7 View Post
    What is your question?
    This db is for scheduling employees to go to events. I'm trying to make it easier for the schedulers to only populate the people combobox with people who are not already scheduled for the date they are currently scheduling (the combo is populated after a date is chosed on the form, which is Forms!Navigation!NavigationSubform.Form!EventDate) . Forms!Navigation!NavigationSubform.Form!txtID is the current event being entered.

    So assume I am already scheduled for an event in the db on 1/24/14

    A scheduler starts entering an event, picks 1/24/14 as the date, the comobox is requeried and it should not include me because I have an existing event out there on this same day already.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    This gets complicated. The technique to accomplish has been discussed in a number of threads, finding them again is a challenge. Here is one https://www.accessforums.net/forms/h...ist-37486.html

    First, the record must be committed to table for the info to be available for combobox filtering.

    DoCmd.RunCommand acCmdSaveRecord
    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.

  5. #5
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    I haven't dug through that thread yet, but based on the subject I am not looking to remove an item from the combobox (though that would be cool) once they are selected, I am looking to initially populate it with people who are not yet scheduled on that date. The date selection is required before selecting attendees so the date is there for checking in the query.

    1. Create event
    2. Pick date
    3. Populate attendees who are not yet linked to an event on selected date in step 2

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    You want to select a date and have the combobox requery to show only people who are not already scheduled for that date.

    Sorry, misstatement about committing the record first.

    However, the technique attempting to describe in that referenced thread is still relevant. I know I've gone through this more than once. Seem to have touched on it in your other thread https://www.accessforums.net/queries...17/index2.html - go to post 23.
    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.

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

Similar Threads

  1. Query tweak to get singles - newb
    By bentod in forum Queries
    Replies: 7
    Last Post: 10-11-2012, 11:41 AM

Posting Permissions

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