Kelly,
All I can do is guess since I don't know your db design but here goes:
-Run your first query
-Run this query
<cfquery name="qgetitems" datasource="#DSN#">
SELECT COUNT(SI_Item_ID) AS SI_Item_ID_COUNT, SI_Item_ID
FROM SI_ITEMS2
WHERE SI_ITEM_ID IN (#SI_Item_ID#)
AND SI_Opsrpt_ID = #ValueList(qgetsi.SI_Opsrpt_ID)#
GROUP BY SI_Item_ID
</cfquery>
-output like this
<CFOUTPUT query="qgetitems">
#SI_Item_ID_COUNT# - #SI_Item_ID#
</cfoutput>
I would also suggest that you not do SELECT * but specify the fields you
want instead. It may take a little more work but will make debugging
and/or interpreting your code much easier.
hth,
Kevin
At 02:04 PM 8/10/00 -0400, you wrote:
>Ok here is my perdicament. Yours worked fine
><cfquery name="qgetitems" datasource="#DSN#">
> SELECT *
> FROM SI_ITEMS2
> WHERE SI_ITEM_ID IN (#SI_Item_ID#)
> AND SI_Opsrpt_ID = #qgetsi.SI_Opsrpt_ID#
></cfquery>
>however what I am really trying to do is get a count.
>So say there are 3 SI_OPSRPT_ID's
>and i pass 5 item_ids i want to do a count of how many times
>each ITEM shows up within those 3 reports.
>I'm still somewhat new at getting things to query and display properly.
>would want the output to be something like.
>
> From this date to this date (i pass the date range thats what determines how
>many SI_opsrpt_id's will be included):
>There were 7 instances of this Item.
>???
>Kelly
>---------------------------------------
>Kelly Matthews
>Internet Development Coordinator
>AAAE
>703.578.2509
>[EMAIL PROTECTED]
>http://www.airportnet.org
>---------------------------------------
snip
> > -----Original Message-----
> > From: Kelly Matthews [mailto:[EMAIL PROTECTED]]
> > Sent: Thursday, August 10, 2000 12:14 PM
> > To: '[EMAIL PROTECTED]'
> > Subject: QUERY QUESTION
> >
> >
> > Ok I have 2 queries I am running the first is:
> >
> > <CFQUERY datasource="#DSN#" name="qgetsi">
> > SELECT *
> > FROM SelfInspection
> > WHERE 0=0
> > <CFIF (IsDefined("form.date1")) AND (IsDefined("form.Date2"))>
> > AND SI_dt BETWEEN #CREATEODBCDate(form.date1)# AND
> > #CREATEODBCDate(form.date2)#
> > </CFIF>
> > </CFQUERY>
> > This runs fine...
> >
> > Next one is:
> >
> > <cfquery name="qgetitems" datasource="#DSN#">
> > <cfloop list="#SI_Item_ID#" index="TheItem">
> > SELECT *
> > FROM SI_ITEMS2
> > WHERE SI_ITEM_ID = #TheITem#
> > AND SI_Opsrpt_ID = #qgetsi.SI_Opsrpt_ID#
> > </cfloop>
> > </cfquery>
> >
> > This seems to run fine but comes back with ONE record when it should come
> > back with THREE:
> >
> > Results:
> > qgetitems (Records=1, Time=0ms)
> > SQL = SELECT * FROM SI_ITEMS2
> > WHERE SI_ITEM_ID = 19 AND
> > SI_Opsrpt_ID = 5
> > SELECT * FROM SI_ITEMS2
> > WHERE SI_ITEM_ID = 13
> > AND SI_Opsrpt_ID = 5
> > SELECT * FROM
> > SI_ITEMS2
> > WHERE SI_ITEM_ID = 15
> > AND SI_Opsrpt_ID = 5
> >
> > It only appears to bring back ONE record however it should bring back 3
> > because
> > ID 5 is in there with item id 19,15 and 13 but it seems to only pick up
> > and
> > display the first one....
> > Do I need to change my query????
> > Kelly
> >
--------------------------
Kevin Marshall
Web Application Developer
eCalton.com, Inc.
Vero Beach, FL
www.ecalton.com
[EMAIL PROTECTED]
561.569.4500
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
