Query to Select all records that start the same (1 Viewer)

[mikej0077]

Is there a query I can do that will select all the records that start the same?

Example:

Chef Works Baseball Cap Blue
Chef Works Baseball Cap Red
Chef Works Baseball Cap Yellow
Whites Baseball Cap Blue
Whites Baseball Cap Red
Whites Baseball Cap Yellow
Fruit of the loom T-shirt Blue
Fruit of the loom T-shirt Red
Fruit of the loom T-shirt Yellow
Spare Buttons White

So I want a query that would return all the records where the 1st 21 characters match other records in the column, giving me:

Chef Works Baseball Cap Blue
Chef Works Baseball Cap Red
Chef Works Baseball Cap Yellow
Fruit of the loom T-shirt Blue
Fruit of the loom T-shirt Red
Fruit of the loom T-shirt Yellow

I would also like it to create new field that would take the common characters and put these into a new column with dashes instead of spaces. Like this:

Chef Works Baseball Cap Blue │ Chef-Works-Baseball-Cap
Chef Works Baseball Cap Red │ Chef-Works-Baseball-Cap
Chef Works Baseball Cap Yellow │ Chef-Works-Baseball-Cap
Fruit of the loom T-shirt Blue │ Fruit-of-the-loom-T-shirt
Fruit of the loom T-shirt Red │ Fruit-of-the-loom-T-shirt
Fruit of the loom T-shirt Yellow │ Fruit-of-the-loom-T-shirt

 

[Ranman256]

make a query that pulls the 1st 20 chars:
select left([field],20) as Left20 , [Field] from table

join it to another query if you need on Left20 field in both queries.

 

[theDBguy]

Hi. In your example data, "Whites Baseball Cap " (with a trailing space) qualifies your condition/criteria (20 chars), so it should be included in the results.

 

[mikej0077]

Hi. In your example data, "Whites Baseball Cap " (with a trailing space) qualifies your condition/criteria (20 chars), so it should be included in the results.

Ha good spot. 21 Characters then.

 

[mikej0077]

make a query that pulls the 1st 20 chars:
select left([field],20) as Left20 , [Field] from table

join it to another query if you need on Left 20 field in both queries.

Will this solution work for a table with thousands of records? I'm still just learning, but my understanding is that this will return the first 20 characters of every field. I only want the records that have the same first 20 characters as other records in the field.

 

[theDBguy]

Will this solution work for a table with thousands of records? I'm still just learning, but my understanding is that this will return the first 20 characters of every field. I only want the records that have the same first 20 characters as other records in the field.

Try adding the DISTINCT keyword to your query to get a set of unique data.

 

[mikej0077]

Try adding the DISTINCT keyword to your query to get a set of unique data.

That sounds promising... will look into this. Thanks!

 

[theDBguy]

That sounds promising... will look into this. Thanks!

Sure. Should look something like:

SELECT DISTINCT Left([FieldName],21) AS First21 FROM TableName

 

[mikej0077]

Sure. Should look something like:

SELECT DISTINCT Left([FieldName],21) AS First21 FROM TableName

Here's what I put. It seems to be just giving me the first 35 characters of all the records. I am trying to narrow the list down.

SELECT DISTINCT Left([Title],35) AS First35, [Pre Sep].ProductCode, [Pre Sep].Title
FROM [Pre Sep];

 

[theDBguy]

Here's what I put. It seems to be just giving me the first 35 characters of all the records. I am trying to narrow the list down.

SELECT DISTINCT Left([Title],35) AS First35, [Pre Sep].ProductCode, [Pre Sep].Title
FROM [Pre Sep];

Hi. Take out the other fields, since we're trying to generate a list of data to match your table. So, try:

SELECT DISTINCT Left([Title],35) AS First35, 
FROM [Pre Sep]

 

[mikej0077]

Hi. Take out the other fields, since we're trying to generate a list of data to match your table. So, try:

SELECT DISTINCT Left([Title],35) AS First35, 
FROM [Pre Sep]

Cool, that's kind of working. I need to keep playing with these to figure out how to get what I want. Thanks for the help!

 

[theDBguy]

Cool, that's kind of working. I need to keep playing with these to figure out how to get what I want. Thanks for the help!

Okay. Let us know how it goes...