This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
(Office 2010).
This question involves a law firm that uses an Access database to store names and other information, including whether the individual or entity was ever a client or adversary, the firm's matter/case no., and the attorney assigned. The firm is obligated to search the database each time there is a possibility of representation in order to determine whether there would be any conflicts of interest should the firm represent the party. On occasion, the firm becomes involved in class action matters that involve thousands of individual clients within the class. We will receive a list of names that we must search for in our Access database to determine if any have ever been a client or adversary.
The database search is a boolean search since some names are entered into the database with last name first, and some have first name first. Accordingly, if we search the database for John Smith, we have an Access search form that allows us to search for John AND Smith in two separate cells, which will find everything that has the name John and Smith regardless of how the name was input into the database. Also, the search will find partial matches as well, e.g., a search for American AND Insurance will yield every entity with American and Insurance in the name, whether North American Insurance; Southwest American Insurance, etc.
We now would like a way to search our Access database using an Excel list of names instead of having to manually type the name of each individual or entity--and then use the Excel spreadsheet list to somehow link to our Access database and search the database for the names contained in the Excel spreadsheet. The results would be generated into a separate Excel spreadsheet or if it's possible to use our Access search results report, that would be outstanding.
The purpose here is to avoid manually typing each name and entity into our Access search form. Instead, link an Excel or even another Access table to our main Access database, so we can eliminate the manual process.
Is there a way this can be done?
A family of Microsoft relational database management systems designed for ease of use.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(89.60000000000001, 51%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
As John, said, no you go to the other way. You connect from Access to Excel. What you showed was connecting Excel to Access (By the way, those icons look like Office 2013, but you indicated you are working with 2010).
Once linked, you can use the linked table as you would any Access table.
I know the database design, as far as name structure, leaves a lot to be desired. As far as linking an Excel list of names to the Access database, are you saying that a basic link, as provided below will work?:
Connect an Access database to your workbook
I find this a little odd. I'm wondering if the same person that designed the search designed this database. The search mechanism, while simple, shows an above average knowledge of Access. On the other hand, the use of one field for names, shows a lack of knowledge of database design.
When building a database, each field should be a single piece of data. I generally use five fields for people names: Salutation (Mr, Ms, etc.) First, Middle, Last, Suffix (Jr, Sr, etc.). I'm mildly shocked that a law firm would not require this basic design.
Frankly I would really suggest that redesigning the database be strongly considered.
But that doesn't answer your question. The answer is Yes, you can link to the Excel sheet, then loop through each record in the sheet and apply the same logic used in your search routine to check each record against your database.
Thank you for responding. If something like J AND Smith is searched for, the results would be any first name that starts with the letter "J" and any last name of Smith or contains Smith. For example, if the last name is "Smithson" then it would show up in the search results since it contains 'Smith'. In other words, the search is very broad, including anything with a middle initial, such as John J. Smith or Smithson could be a search result when searching for J AND Smith. If the search is something like J. (with a period) AND Smith, then the result would be limited to all names beginning with J. and all last names with Smith, including Smithson, etc. But if there is no period after J, then the search would find every first name beginning with a J, such as Jack, John, Joe, Jerry, Jason, etc. and everything with last name of Smith or containing Smith.
As far as how names are stored in the Access database, they are stored in one cell and could be entered either first and last name or last name first with a comma, then first name. Same with business names. Everything is entered into one cell. So, there isn't a first name field, middle initial or name field, and last name field, or a separate field for business entity names.
The Access database search form is simple. That is, it has one field on the left for entering anything related to the particular name being search, whether a last name, first name, or business entity. It doesn't matter what is typed in the cell. Then there is a drop-down list, which allows a choice of Boolean search terms/operators like, AND, OR, and NONE, and another field to the right of the drop-down list, which allows a second search term, if there is one. If there isn't one, then NONE is selected, which means the second field is not being used. If I want to search for John Smith, I would enter John in the first field then select Boolean operator AND, then Smith in the right side field (technically, I could revers the order of search terms in the left and right fields, such as last name in field one and first name in field two). This search would yield everything with John and Smith, Smithson, etc. regardless of whether the name contains a middle name or initial. The search is only concerned with finding anything containing John AND Smith.
Yes, with some pretty big caveats. You can use External Data... Excel... Link to link your spreadsheet to Access, and then run a Query using your spreadsheet data to search an Access table.
The caveat is that you are clearly doing a pretty fuzzy search. Is "J. Smith" a match for "John Smith"? How about "John K. Smith"? "John Smythe"? How are the names stored in the Access table - in one field, broken out into first and last name fields, or even finer?
Perhaps you could post some more details of your current Access search form, e.g. the SQL code of the query it invokes, or the VBA program that does the search.