Search for 'contains' returns only 'equals'

[BlueWolverine]

Hello,
MS Access 2003 on XP Pro.

I have a series of daisy chained queries to count Codes for me.

I have entries in MASTER that may have multiple comma separated codes in one
field. I have these queries to look through that cell and find out what
codes are in those cells.

Now, I was told the SQL Like "*" & & "*" would take care of that for
me. But it's only grabbing ='s right now.

So it's like this.
I have ten entries that actually contain B64. I know this because a search
query I wrote found 10 containing the 3 character string "B64". However, the
join query using LIKE instead of = returns only the 2 entries that equal B64.

q_CCC_Uniques is the list of all unique codes (no repeats on this list at
all.)
[QMC MASTER LIST] contains a field [CCC's] that contains comma separated
list of CCC codes. for instance, one entry in [CCC's] might look like this.
A14, B64AT, B63AB, L26.

That should be FOUND for B64.

Here is the SQL of the query where it breaks down.

SELECT [QMC MASTER LIST].[Work Element Description], [QMC MASTER LIST].[GQRS
CAT], [QMC MASTER LIST].ACTION, [QMC MASTER LIST].[CCC's], [QMC MASTER
LIST].ACTIVE
FROM q_CCC_Uniques INNER JOIN [QMC MASTER LIST] ON q_CCC_Uniques.CCC Like
"*" & [QMC MASTER LIST].[CCC's] & "*"
WHERE ((([QMC MASTER LIST].Active)=Forms!f_Stats!StatActive))
ORDER BY [CCC];

How can I get this to work?

Thank you for your help in advance.

 

[Ken Sheridan]

It looks to me that you have the LIKE operation the wrong way round. If I
understand you correctly it should be:

INNER JOIN [QMC MASTER LIST] ON [QMC MASTER LIST].[CCC's] Like "*" &
q_CCC_Uniques.CCC & "*"

Bear in mind that if any code is a substring of another code you'll get
mismatches.

Ken Sheridan
Stafford, England

 

[BlueWolverine]

Thank you that helped.

For some reason, I got duplicates. Every single one was in there twice. I
suspect it involves spaces. Anyway, by chosing a criteria in the query for
is not null I got around it.

Then I was counting some things wrong, it's complicated because of the way
it's setup, but fundamentally, that was the error.

Thanks,

--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

It looks to me that you have the LIKE operation the wrong way round. If I
understand you correctly it should be:

INNER JOIN [QMC MASTER LIST] ON [QMC MASTER LIST].[CCC's] Like "*" &
q_CCC_Uniques.CCC & "*"

Bear in mind that if any code is a substring of another code you'll get
mismatches.

Ken Sheridan
Stafford, England

Hello,
MS Access 2003 on XP Pro.

I have a series of daisy chained queries to count Codes for me.

I have entries in MASTER that may have multiple comma separated codes in one
field. I have these queries to look through that cell and find out what
codes are in those cells.

Now, I was told the SQL Like "*" & & "*" would take care of that for
me. But it's only grabbing ='s right now.

So it's like this.
I have ten entries that actually contain B64. I know this because a search
query I wrote found 10 containing the 3 character string "B64". However, the
join query using LIKE instead of = returns only the 2 entries that equal B64.

q_CCC_Uniques is the list of all unique codes (no repeats on this list at
all.)
[QMC MASTER LIST] contains a field [CCC's] that contains comma separated
list of CCC codes. for instance, one entry in [CCC's] might look like this.
A14, B64AT, B63AB, L26.

That should be FOUND for B64.

Here is the SQL of the query where it breaks down.

SELECT [QMC MASTER LIST].[Work Element Description], [QMC MASTER LIST].[GQRS
CAT], [QMC MASTER LIST].ACTION, [QMC MASTER LIST].[CCC's], [QMC MASTER
LIST].ACTIVE
FROM q_CCC_Uniques INNER JOIN [QMC MASTER LIST] ON q_CCC_Uniques.CCC Like
"*" & [QMC MASTER LIST].[CCC's] & "*"
WHERE ((([QMC MASTER LIST].Active)=Forms!f_Stats!StatActive))
ORDER BY [CCC];

How can I get this to work?

Thank you for your help in advance.

 

[Ken Sheridan]

You can suppress duplicate rows by using:

SELECT DISTINCT [QMC MASTER LIST].[Work Element Description], etc.

Ken Sheridan
Stafford, England

Thank you that helped.

For some reason, I got duplicates. Every single one was in there twice. I
suspect it involves spaces. Anyway, by chosing a criteria in the query for
is not null I got around it.

Then I was counting some things wrong, it's complicated because of the way
it's setup, but fundamentally, that was the error.

Thanks,

--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

It looks to me that you have the LIKE operation the wrong way round. If I
understand you correctly it should be:

INNER JOIN [QMC MASTER LIST] ON [QMC MASTER LIST].[CCC's] Like "*" &
q_CCC_Uniques.CCC & "*"

Bear in mind that if any code is a substring of another code you'll get
mismatches.

Ken Sheridan
Stafford, England

Hello,
MS Access 2003 on XP Pro.

I have a series of daisy chained queries to count Codes for me.

I have entries in MASTER that may have multiple comma separated codes in one
field. I have these queries to look through that cell and find out what
codes are in those cells.

Now, I was told the SQL Like "*" & & "*" would take care of that for
me. But it's only grabbing ='s right now.

So it's like this.
I have ten entries that actually contain B64. I know this because a search
query I wrote found 10 containing the 3 character string "B64". However, the
join query using LIKE instead of = returns only the 2 entries that equal B64.

q_CCC_Uniques is the list of all unique codes (no repeats on this list at
all.)
[QMC MASTER LIST] contains a field [CCC's] that contains comma separated
list of CCC codes. for instance, one entry in [CCC's] might look like this.
A14, B64AT, B63AB, L26.

That should be FOUND for B64.

Here is the SQL of the query where it breaks down.

SELECT [QMC MASTER LIST].[Work Element Description], [QMC MASTER LIST].[GQRS
CAT], [QMC MASTER LIST].ACTION, [QMC MASTER LIST].[CCC's], [QMC MASTER
LIST].ACTIVE
FROM q_CCC_Uniques INNER JOIN [QMC MASTER LIST] ON q_CCC_Uniques.CCC Like
"*" & [QMC MASTER LIST].[CCC's] & "*"
WHERE ((([QMC MASTER LIST].Active)=Forms!f_Stats!StatActive))
ORDER BY [CCC];

How can I get this to work?

Thank you for your help in advance.