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

    Query using 2 sets of criteria

    I am trying to perform a query to grab all people with a particular role, AND also grab other people IF they are found in the Attendee field for the currently selected Event (role does not matter for the second part).



    Code:
    Table=Events
    Fields:
    ID
    Attendees (From People table: Name1,Name2,Name3) (Yes, I know I should normalize) 
    
    Table=People
    Fields:
    ID
    Name
    Role
    Scenario to populate listbox:
    Select all people from People table with role="Attendee"
    Select people from People table IF they exist in the Attendee field (can use InStr()) for the current event ID.

    The first part is simple, but I'm not sure how to perform the second query. When I use an temp attendee, they are added to the People table with the role of "Attendee (temp)". We do not want these people to appear in the Attendee listbox for events that they are not a part of OR when creating new event. They should only be included in the listbox in the event(s) for which they were manually added and are found in the Attendee field for the event (I have a command button to add these temps). We re-visit records up for 4 times before they are completed and the temps name(s) re-appearing in the listbox is important.

    Event1
    Ryan Attendee
    Bill Attendee
    Bob Attendee (temp)

    If I create a new event, I should see only "Attendees" from people table
    If I re-visit Event1, I should see all "Attendees" from people table AND "Bob" since he is in the attendee field for Event1.

    I made this for grabbing the non Attendees, which is probably wrong. Please help (Again, the role does not matter for the temps, I had to include that in my code to prevent every Attendee from being returned). I wrote this in VBA to allow the ID to be dynamic based on the currently viewed event, me.id being the Event ID.
    Code:
    strSQL = "SELECT DISTINCT People.Name" & vbCrLf
    strSQL = strSQL & "FROM Events,People" & vbCrLf
    strSQL = strSQL & "WHERE ((People.Role= 'Attendee (temp)') AND (" & Me.ID & " = DLookup('[ID]', '[Events]', InStr([Attendees],[People].[Name])>0)))" & vbCrLf
    lstAttendees.RowSource = strSQL
    lstAttendees.Requery
    I need that, combined with
    Code:
    SELECT People.Name
    FROM People
    WHERE People.Role <> "dbadmin"
    ORDER BY People.Name;

  2. #2
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    For testing, Adam should show up when I visit event ID 4.

    TestAttendees.zip

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,831
    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.

  4. #4
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    Yes, except that thread was for if I went with The combobox continuous normalized method. :x

  5. #5
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    Which I'd like to do, but idk if I can get every one of my queries to work with that method. I've normalized the rest of my Db though, the attendance is the only thing nit being done correctly... But it works great.

  6. #6
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    Quote Originally Posted by BRZ-Ryan View Post
    Which I'd like to do, but idk if I can get every one of my queries to work with that method. I've normalized the rest of my Db though, the attendance is the only thing nit being done correctly... But it works great.
    In your opinion, do you see any long term impact (performance\stability) on keeping all names in one field versus using a separate attendance table? I should not need any further reporting other than what I already have.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,708
    I think (guess without details) you would have

    People
    Events
    Roles

    and you may need junction table(s) if
    -A person could be in many events
    -An event involves 1 or more People(Persons)
    -A Person can play 1 or more Roles
    -A Role could be played by 1 or More Persons.

    Need details, then get your tables and relationships set up before getting into Forms....

  8. #8
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    I think I am going to go with the independant table for attendance instead of storing redundant individual peoples names in the event table.

    I now have the following tables for this: People, Events, Attendance, attendance being the junction table..

    When using a continuous subform with comboboxes (for attendee selection), how do I read each item from these combos with VBA? Should I do it directly through the control or through a query

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,708
    see this data model and the Roster table to deal with attendance.
    You really should get your tables and relationships designed to meet your business rules before developing interfaces, forms, reports etc.

  10. #10
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    I am using a button to send an email through Outlook. I need to retrieve the names from the comboboxes to set them as recipients.

    I put together this query, which with VBA I can manually insert the current eventID. Is there a simpler way to get the names out of the combobox? (I was manually specifying the eventID instead of using Events.ID) Should I use DAO?

    Code:
    SELECT Q1.FirstName & " " & Q1.LastName AS Attendees
    FROM (SELECT EmployeeID, FirstName, LastName FROM Attendance,Events,People WHERE (People.ID=Attendance.EmployeeID) AND (Attendance.EventID = Events.ID))  AS Q1
    GROUP BY Q1.FirstName & " " & Q1.LastName;

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

Similar Threads

  1. Form and query return different record sets
    By Daryl2106 in forum Access
    Replies: 3
    Last Post: 12-11-2012, 09:41 PM
  2. Replies: 5
    Last Post: 09-20-2012, 03:27 PM
  3. Replies: 5
    Last Post: 08-02-2012, 09:44 AM
  4. Update Query - Multiple SETS and WHERE conditions
    By jasonbarnes in forum Queries
    Replies: 26
    Last Post: 12-15-2010, 01:08 PM
  5. Selecting Unique SETS of rows in Access Query
    By dalessandroh in forum Queries
    Replies: 0
    Last Post: 11-09-2010, 10:42 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