Query to find Partial Match of Column Data

[rachmorr9]

I have a table that I am trying to run a query on to pull records that contain duplicate data.
For example:



Wholesaler_Drug_ID = 123456
Wholesaler_Drug_ID = 123456INNER

I want my query to match the first 6 characters (without defining what they are since they will all be different) and wildcard anything that follows (which in this case is the word INNER).

Please let me know if more details about the query are needed in order to understand my request.

[JeroenMioch]

In query design, put under Criteria

like [enter six digits] & "*"

You get an inputbox that asks you to input six digits and will wildcard everything behind it

[June7]

Something like:

SELECT Left([Wholesaler_Drug_ID,6) AS ShortID, Count(*) AS CountDups GROUP BY Left([Wholesaler_Drug_ID,6);

The count will tell you which ID have 'duplicates'.

Then try:

SELECT table.*, Q.* FROM table INNER JOIN (SELECT Left([Wholesaler_Drug_ID,6) AS ShortID, Count(*) AS CountDups GROUP BY Left([Wholesaler_Drug_ID,6)) AS Q ON table.Left([Wholesaler_Drug_ID,6)=Q.ShortID WHERE CountDups > 1;

Or try:

SELECT * FROM table WHERE DCount("*", "table", "Left([Wholesaler_Drug_ID,6)='" & Left([Wholesaler_Drug_ID,6) & "'")>1;