Trouble with LIKE statement

[Dionubis]

Hey, all -

I'm trying to create a CF report where a QoQ provides a total of times an
entry appears in a column from Access. To make it simple, there are different
users (Nate, Sarah, and Matt) that could be in the fields for the category in
question (User_Comp). Sometimes it's just one user's name, sometimes two or
even all three appear. I want to make a table that will list how many times
each user's name appears. I can get it to list out the total amount that the
field only holds *one* of the users' names (such as Nate), by using an equals
sign in the WHERE statement, but I can't get it to include the ones where
multiple names appear in the field (such as Matt, Sarah, Nate). I've been
trying to use LIKE (and I've tried CONTAINS), but both attempts give me an
error:

null null
The error occurred on line -1.

LIKE seems to be the operator to use, but I can't see what I'm doing wrong.
Here's a simplified look at the code - any help would be GREATLY appreciated.
I'm concerned that I'm just overlooking something small...

---------------

<CFOUTPUT QUERY="GetType" GROUP="Public_priv">

<CFQUERY DATASOURCE="lister" NAME="getall">
SELECT *
FROM questionaire
WHERE
Class='#class#'
AND public='#public#'
AND month = '#month#'
</CFQUERY>

<!---------------------------------------------------------------------------
| Get counts for each user.
---------------------------------------------------------------------------->


<CFQUERY DBTYPE="query" NAME="user_Nate">
SELECT User_Comp
FROM getall
WHERE User_Comp LIKE '%Nate%'
</CFQUERY>


<!---------------------------------------------------------------------------
| Print Table cell data.
---------------------------------------------------------------------------->

<TR>
<TD> #Class_Full# &nbsp;
<TD> #public_priv# &nbsp;

<CFIF user_Nate.recordcount EQ "0">
<TD ALIGN="right"> N/A
<CFELSE>
<TD ALIGN="right"> #(user_Nate.recordcount)# &nbsp;
</CFIF>


</CFOUTPUT>

[Dan Bracuk]

How about

names = (Nate,Sara,Matt);

SELECT User_Comp
FROM getall
WHERE User_Comp in (#listqualify(names)#)

[Dionubis]

Won't that just adjust the way the string is listed? I would like to total up
the number of times, say, Nate's name appears. So, for example, these are the
fields in User_Comp:

Nate
Nate,Sarah
Sarah,Matt
Sarah,Matt,Nate

I would want a table listing: Nate - 3, Sarah - 3, Matt - 2.

Will ListQualify do that for me?

[mxstu]

Dionubis,

Are you storing delimited lists of information in a single column? I have to
tell you that is a bad table design. Aside from violating database modeling
recommendations, it is difficult to work (as you've noticed) and inefficient to
query If you were using a more normalized structure, your current problem
would be solved quite easily and the query would most likely be faster. The
data should be stored in rows, like:

QuestionaireID | UserID
-------------------------------------
1 | 23 <-- Nate
2 | 23 <-- Nate
2 | 16 <-- Sarah
3 | 16 <-- Sarah
3 | 8 <-- Matt

Then you could retrieve the totals with a simple join and a COUNT(*) .

[jorgepino]

just use "Cfloop" and run thru the query as many time as you request

[Dionubis]

Yes, sadly it is being stored as such - it was created before I took on the
job, and I'm trying to see if I can take care of it without disrupting the
entire system they've set up...

I'll try out the cfloop suggestion, but I have a feeling I might have to go
the UserID route...

Thanks for your help!

- D

[mxstu]

Ugh. Sorry to hear that. Don't know what the original designers were
thinking.

I'll play around with it too and see if I can come up with anything. I think
jorgepino is correct though. You will probably need to loop through the
results, using ListFindNoCase() or ReFindNoCase(), total up the number of
occurrences for each name. Once you get the counts, you want to display them
in an HTML table right?

[Dan Bracuk]

Might be worth the time and effort.

Originally posted by: Dionubis
Yes, sadly it is being stored as such - it was created before I took on the
job, and I'm trying to see if I can take care of it without disrupting the
entire system they've set up...

I'll try out the cfloop suggestion, but I have a feeling I might have to go
the UserID route...

Thanks for your help!

- D

[Dionubis]

Originally posted by: mxstu
Once you get the counts, you want to display them in an HTML table right?

Yeah, exactly.

[mxstu]

And the list of names is delimited by a comma, and each name only appears once
in the column?

For example, you could have this in the same column: Sarah,Matt,Nate
but not this: Nate,Sarah,Matt,Nate

[mxstu]

..

<cfset nameStruct = structNew()>
<cfloop query="getAll">
<cfloop list="#User_Comp#" index="currName">
<cfif StructKeyExists(nameStruct, currName)>
<cfset nameStruct[currName] = val(nameStruct[currName]) + 1>
<cfelse>
<cfset nameStruct[currName] = 1>
</cfif>
</cfloop>
</cfloop>

<cfdump var="#nameStruct#">

[Dionubis]

Originally posted by: mxstu
And the list of names is delimited by a comma, and each name only appears once
in the column?

For example, you could have this in the same column: Sarah,Matt,Nate
but not this: Nate,Sarah,Matt,Nate

Right - it would be in the same order, as in Sarah's name would always appear
before Matt's name, etc. - but they won't always all be there. It might be
Sarah,Matt,Nate; it could be Matt,Nate; or Sarah,Nate.

I'll try your suggested code. I won't be able until Monday - thanks for your
help.