MS Access SQL "Like" and wildcard returning exact matches only
I have two tables: CHART and GRANTs.
CHART contains fields CHART.ID and CHART.Account. The CHART.ID and CHART.Account combinations are unique per record.
The table GRANT contains GRANT.ID and GRANT.Accounts. GRANT.ID is unique per record, however the GRANT.Accounts field can be either a single account number, or several comma separated account numbers (ex: 1234, 5432, 9876, etc). Unfortunately I have no control over the structure of the report.
What I need is to match each CHART.ID to the applicable GRANT.ID via a partial match between CHART.Account and Grant.Accounts. For instance, if GRANT.ID Grant1 has Accounts 1234, 5432, 9876 I want to able to match CHART.Account 5432 as applicable to that GRANT.ID
SELECT GRANTS.ID, GRANTS.Accounts, CHART.ID, CHART.Account FROM CHART, GRANTS WHERE CHART.Account Like GRANTS.Accounts
So far, my SQL code returns only those records where CHART.Account matches GRANT.Accounts exactly, that is, only where there is only one Account in the GRANT.Account field. I've also tried various forms of wildcards with the LIKE to no success. I'm guessing my problem stems from a misunderstanding of how LIKE and wildcards work.
Any tips would be very much appreciated.
The Wild card would need to be concatenated to the field value, and if you have lots of data this will get very slow, as the wildcard at the beginning of the searched term means that any indexes on it can't be used
SELECT GRANTS.ID, GRANTS.Accounts, CHART.ID, CHART.Account FROM CHART, GRANTS WHERE CHART.Account Like ("*" & GRANTS.Accounts & "*")
You could however use a query to make a temporary table of your distinct values, and then use a direct matched query. On a large dataset this would be much more efficient.
Just because your data arrives in a poor format, there's nothing to stop you manipulating it to a sensible format.
What flavor of SQL is used inside Microsoft Access?, to answer a simple question, to perform calculations, to combine data from different tables, or even to add, change, or delete table data. Access is designed as a rapid application development (RAD) tool that does not require programming. You can write and execute SQL statements in Access, but you have to use a back-door method to do it.
Maybe if you can provide actual value examples of the two fields you are trying to match and they are not matching you can get more meaningful help.
At a guess, your query probably needs to be more like this:
SELECT GRANTS.ID, GRANTS.Accounts, CHART.ID, CHART.Account FROM CHART INNER JOIN GRANTS ON CHART.Account Like ("*" & GRANTS.Accounts & "*")
MS Access - Query Data, To view the SQL, go to the Home tab. Select SQL View from the View menu and you will see the SQL of your query. Home Tab. Example. The following is another Microsoft Data Access Objects (DAO) is the native programming object model that lets you get at the heart of Access and SQL Server to create, delete, modify, and list objects, tables, fields, indexes, relations, queries, properties, and external databases. For more information, see Microsoft Data Access Objects reference.
GRANTS.Accounts (plural) holds the value list
CHART.Account has to be the partial match
SELECT GRANTS.ID , GRANTS.Accounts , CHART.ID , CHART.Account FROM CHART INNER JOIN GRANTS ON GRANTS.Accounts LIKE "*" & CHART.Account & "*"
If length of
CHART.Account is not fixed, you have to include the delimiter in criteria or CHART.Account
1234 will match
12345 too (
SELECT GRANTS.ID , GRANTS.Accounts , CHART.ID , CHART.Account FROM CHART INNER JOIN GRANTS ON "," & GRANTS.Accounts & "," LIKE "*," & CHART.Account & ",*"
Access is designed as a rapid application development (RAD) tool that does not require programming. You can write and execute SQL statements in Access, but MS Access has many built-in functions. This reference contains the string, numeric, and date functions in MS Access.
In this lesson we run you through how to create a table in SQL. You learn about the syntax for Duration: 6:18 Posted: Mar 5, 2016 You have selected certain fields in the Query Grid; at the same time, MS Access has also created an SQL Query with the results obtained from your Query Grid. To view the SQL, go to the Home tab. Select SQL View from the View menu and you will see the SQL of your query.
SELECT statement (Microsoft Access SQL). 10/18/2018; 5 minutes to read. In this article. Syntax; Remarks; Example. Applies to: Access 2013 | Office 2013. UtterAccess is the premier Microsoft Access wiki and help forum. SQL to VBA Formatter. Viewing Records Within A Defined Range. Example. Some of the following examples assume the existence of a hypothetical Salary field in an Employees table. Note that this field does not actually exist in the Northwind database Employees table.
Microsoft Access SQL reference. 09/18/2015; 2 minutes to read. In this article. Applies to: Access 2013, Office 2013. Overview of the Access SQL reference · Data In operator (Microsoft Access SQL) 06/08/2019; 2 minutes to read +4; In this article. Applies to: Access 2013 | Access 2016 Determines whether the value of an expression is equal to any of several values in a specified list.