Hey guys, I'm having some trouble with my database. I am attemptiong to build a simple, only pulling data from one table. The table is a list of accounts managed by a wealth management firm, the advisors assigned to each account and their relative splits. The fields I am working with are titled: Current Account Value, Advisor 1, Advisor 1 Split, Advisor 2, Advisor 2 Split, Advisor 3, Advisor 3 split. My goal is to create a report based off of the query in which an advisor can enter his/her last name and the output will be their share of each account and their total money managed. (Note: An advisor can be listed under advisor 1, advisor 2, or advisor 3 dependent upon the account)
I have set up a parameter under Advisor 1 (A1) to prompt the user to enter an advisor name. I want the query to show EVERY account a given advisor is listed on, regardless of whether they are listed as advisor one, two or three. While it seems to be working when the name is listed under advisor one, it will not pull up the advisor if they are listed as advisor two or three on an account. I’m trying to set it up so that the query pulls up ALL accounts a given advisor is listed on, not just accounts where they are listed as Advisor 1. In other words, I am trying to query an item, advisor name, in multiple fields. A snapshot of my query in design view is attached.
Does anyone know how to fix this issue?