Results 1 to 12 of 12
  1. #1
    ExcelGrad is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    7

    Querying for All criteria values across multiple records


    I’m working a database maintaining a record of contacts and events in which they participate. I have a Contacts table with Email as primary key, an Events table with Event Name as primary key, and a Participation table with an auto primary key and foreign keys of email and event name. A contact can play one or more of 8 different roles in an event. A user can perform queries on Participation from a form created for that purpose. In addition to simple queries such as email and/or event, I need to provide 2 query types by role. The first query should return records where a contact played one or more of up to 3 roles specified in the query. The second query should return records where a contact played all the roles specified in the query (up to 3).

    Originally, all the Roles played by a contact at an event were comma-separated in a text field (not my choice). The queries used “Like *Role*” in criteria for the text field with either OR (first query) or AND (second query). Role query fields on the query form were checked for NULL roles as well. It made for a very complex query and was not a very robust solution.

    My question is, if each record in Participation were to have only one role, and a situation where a Contact played more than one role results in multiple records, how can I define the second query (AND)?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Never mind, I missed the point there could be more than 3 roles in an event and you want to specify only 3 as criteria. I will have to study this some more. Might need a VBA custom function. It was solution offered for a similar requirement https://stackoverflow.com/questions/...48266#70748266
    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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I would say using email/names as a primary key is not a good idea. You have additional maintenance to do if the name is corrected/changed and it will really hit performance once you start getting a lot of records as strings take up a lot more space than longs which is what is usually used as a primary key. You can still set these fields to be indexed, no duplicates.

    The queries used “Like *Role*” in criteria
    again a slow way of using a database as the initial * precludes the use of an index - plus there is the risk of typos for the same role and you may get overlaps i.e. I don't know what your data looks like but you might have one role which is 'accountant' and another which is 'senior accountant' - a criteria based on 'accountant' will return both roles.

    if each record in Participation were to have only one role, and a situation where a Contact played more than one role results in multiple records,
    this is definitely a better way to go and I suspect you can then use a simply aggregate query to count by participant and role and have criteria based on the count (where a contact played one or more of up to 3 roles specified in the query the criteria would be HAVING Count([field])<=3) and for The second query should return records where a contact played all the roles specified in the query (up to 3) - if I've understood correctly - you would add the event to the counting and the criteria would be HAVING Count([field])=3

  4. #4
    ExcelGrad is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    7
    Sorry if I wasn't clear.
    General roles might be: Host, Sponsor, Volunteer, Leader, Donor, Legislative, Medical, Press

    If fred32@gmail.com participated in 2022_Kickoff as a Volunteer, Donor, and Leader then presumably there would be three records with his email and the event name with each having a single and different role.
    If mary332@gmail.com participated in the same event as a Volunteer, there would be one record.

    If the user queries for Volunteer, Donor OR Leader then all 4 records should be displayed. If the user queries for ALL those roles, only the records for fred32@gmail should be returned.

    In either case, I will want to display only the unique email addresses of matching records.

  5. #5
    ExcelGrad is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    7
    Thanks, Ajax.

    I'll certainly consider your comments in paragraph 1. As it is, I know I am facing potential for cascading update queries with email changes.

    I'm definitely with you on your second paragraph. That was their previous approach and I'm trying to transition away from it.

    You understood query aspect correctly and I think the Count approach might be the way to handle it. I will give that a try. Thanks

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK - bit clearer - some followup questions

    does an event always have 3 roles - or can there be more or less?

    what if Mary has participated in 3 different events, one as a Volunteer, one as a Donor, and the third as a Leader? And if so is there some criteria around that such as events must have occurred within say a 3 month period? or be of the same type or subject?

    But signing off now for the night, so perhaps someone else can pick it up, otherwise I'll take a look again tomorrow (later today!)

  7. #7
    ExcelGrad is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    7
    Not all roles apply to all events so some may have more than 3 and some may fewer. As a note, the "3 roles" are based on the user's requirement to query on that many. It's pretty arbitrary.

    Events have start and end dates in their table and Participation table records have a participation date, so a query could include a range, year, month, day, etc.

    In the Mary example, the user might want to query for any of those roles out of all participation in 2021. All 3 would be returned and I would expect aggregation. If any of the 3 were not in 2021 then they would be excluded. If the user queried for all of those roles in 2021, none of Mary's records would meet criteria. I would expect aggregation only when querying for ALL roles.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK so the parameters a user would need to choose is

    1. A period of time
    2. One or more roles - the number of roles specified setting the limit

    which leads to more questions.

    I would expect aggregation only when querying for ALL roles.
    Please clarify - all

    In the scenario where Mary in 2021 did 4 roles as Donor across 4 events and the user just specifies 2021 and Donor (1 role), she will appear in the first list (<=1) but not in the second (=1) - so should this be >=1 instead?

    Or does the fact Mary has performed the same role in different events count only as 1?

    Suggest provide more comprehensive example data to cover a range of user requirements and show what result you require for a range of those requirements - together with a brief description of the business requirement (It's called a user story) so it can be put in context.

    A user story is just a few sentences along the lines of:

    'I want to be able to select all contacts who have played one or more of a range of roles I specify over a period of time that I specify. I need to see which of those roles they have played. I do not need to see in which events they played the roles or how many times they have done so. I do need to highlight those contacts who have played all the roles I specified'

  9. #9
    ExcelGrad is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    7
    Your user story was very close. Thanks for that. I added a desire to be able to specify matching of all roles specified. A count of how many times a role was played might be a nice addition, but not critical.
    'I want to be able to select all contacts who have played one or more, or all of a range of roles I specify over a period of time that I specify. I need to see which of those roles they have played. I do not need to see in which events they played the roles or how many times they have done so. I do need to highlight those contacts who have played all the roles I specified'

    Using sample data below:
    A query for 1 role (Donor) in 2021 would return Mary and Fred as each played the role of Donor at least once in 2021. It would not be necessary to show the role played because only on role was queried.

    A query for 2 roles (Donor, Host) would have different outcomes depending on whether the user wanted contacts with ANY of the 2 roles specified or ALL the roles specified.
    In the first case (ANY), Mary and Fred would be selected as each played Donor and/or Host in 2021.
    The role(s) played would need to be shown.

    In the second case (ALL), only Fred would be selected as only he played both Donor and Host in 2021.
    It would not be necessary to show the roles played because the user would know the roles that were queried.

    A query for 2 roles (Donor, Volunteer) would have different outcomes depending on whether the user wanted contacts with ANY of the 2 roles specified or ALL the roles specified.
    In the first case (ANY), Mary and Fred would be selected as each played Donor and/or Volunteer in 2021.
    The role(s) played would need to be shown.

    In the second case (ALL), Mary and Fred would be selected as they both played both Donor and Volunteer in 2021.
    It would not be necessary to show the roles played because the user would know the roles that were queried.

    Particip ID Event Email Address Role Participation Date
    1 Appeal 2021 Q1 mary332@gmail.com Donor 2/1/2021
    2 Appeal 2021 Q2 mary332@gmail.com Donor 5/1/2021
    3 Appeal 2021 Q3 mary332@gmail.com Donor 8/1/2021
    4 Appeal 2021 Q4 mary332@gmail.com Donor 11/1/2021
    5 Appeal 2021 Q1 fred32@gmail.com Host 3/15/2021
    6 Appeal 2021 Q1 fred32@gmail.com Donor 3/15/2021
    7 Appeal 2021 Q2 fred32@gmail.com Host 6/15/2021
    8 Appeal 2021 Q2 fred32@gmail.com Donor 6/15/2021
    9 Appeal 2021 Q3 fred32@gmail.com Sponsor 9/15/2021
    10 Appeal 2021 Q3 fred32@gmail.com Donor 9/15/2021
    11 Appeal 2021 Q4 fred32@gmail.com Donor 12/15/2021
    12 Walk 2021 mary332@gmail.com Volunteer 8/1/2021
    13 Walk 2021 fred32@gmail.com Volunteer 8/1/2021

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    please see attached - I believe it is close to what you want and should get you started.

    You did not provide an example of the output required, merely a description which is open to interpretation and to not show a role when only one is selected by a user is an indulgence too far for me!

    There is minimal error protection and the report is the same for Any or All - to see if a contact has covered all roles chosen - there should be an X in each column (one column per role, one row per contact)

    A count of how many times a role was played might be a nice addition, but not critical
    I did not do this on purpose as you need to understand how it works and no better way than getting your hands dirty to make changes. It is fairly easy to do - you just need to make changes to the 'value' column in the crosstab query
    Attached Files Attached Files

  11. #11
    ExcelGrad is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    7
    Ajax,

    Thanks so much! Looks like it will fit the bill and I appreciate your help and input.

    I had expected output to just show the roles played. Definitely open to interpretation, but your approach certainly meets the intent. Showing columns for each and marking those applicable with an "X" is something I would never have thought of. I'm also fine not indulging the user doing a single role query.

    I will get my hands dirty on the role count and will revisit having email and event name as primary keys.

    Thanks again.

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    happy to help, good luck with your project

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

Similar Threads

  1. Replies: 4
    Last Post: 04-24-2020, 11:36 PM
  2. Access Query: criteria multiple values
    By sj13 in forum Access
    Replies: 2
    Last Post: 05-15-2018, 04:38 PM
  3. Querying for values that end in #
    By BullwinkleTMoose in forum Queries
    Replies: 2
    Last Post: 12-20-2016, 04:00 PM
  4. Replies: 29
    Last Post: 07-09-2015, 06:21 PM
  5. Querying id by having criteria in two lines
    By ahunter488 in forum Queries
    Replies: 3
    Last Post: 06-21-2011, 05:21 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
  •  
Other Forums: Microsoft Office Forums