SQL problem...

[Scamp]

<cfquery name="QBDQuery" datasource="Quotes">
SELECT QUOTE_TRACK.SalesNumber, QUOTE_TRACK.CustomerNumber,
QUOTE_TRACK.Scopes, SUM(QUOTE.ExtPrice)
FROM QUOTE_TRACK, QUOTE
WHERE ((Quote_Track.QuoteDate BETWEEN ###FORM.StartDate### AND
###Form.EndDate###)
AND (QUOTE_TRACK.QuoteNumber = QUOTE.QuoteNumber))
ORDER BY QUOTE_TRACK.QuoteDate DESC, 1
</cfquery>

I'm trying to get some "Who it's To" information from the Quote_Track table
and the SUM of the ExtPrice column in the Quote table so I can generate a
report of the quotes that each salesman did for a given period of time, which
instrument the quote was on, and the total dollar amount that the quote was
for.

I've tried inner joins and I've tried just shouting "Give me the right GD
answer, you SOB. Whatever I do, I just get an error. Please help!

Thanks
Brendan

[paross1]

Does this work any better for you?

<cfquery name="QBDQuery" datasource="Quotes">
SELECT qt.SalesNumber, qt.CustomerNumber, qt.Scopes, SUM(q.ExtPrice)
FROM QUOTE_TRACK qt, QUOTE q
WHERE qt.QuoteDate BETWEEN #createodbcdatetime(FORM.StartDate)#
AND #createodbcdatetime(Form.EndDate)#
AND qt.QuoteNumber = q.QuoteNumber
GROUP BY qt.SalesNumber, qt.CustomerNumber, qt.Scopes
ORDER BY 1
</cfquery>

I abbreviated it a little by using table aliases, and "fixed" your dates (I
hope), and added a GROUP BY clause, since you are attempting to SUM, and
changed your ORDER BY, since I don't know how you can sort on a field that you
didn't SELECT.

I hope that this does the trick.

Phil

[Scamp]

Phil - it works differently in that it gives me different error messages. I
forgot to mention that I'm doing this in Dreamweaver 8 and using an Access
database. The new errors have to do with my output query, which looks like
this:

<cfoutput query="QBDQuery">
<tr>
<td width="25%"><div
align="center"><strong>#QBDQuery.QuoteNumber#</strong></div></td>
<td width="25%"><div
align="center"><strong>#DateFormat(QBDQuery.QuoteD ate,"M/D/YYYY")#</strong></div

> </td>

<td width="25%"><div
align="center"><strong>#QBDQuery.SalesNumber#</strong></div></td>
<td width="25%"><div align="center"><a
href="FQWCD.cfm?QuoteNumber=#QBDQuery.QuoteNumber# "><strong>#QBDQuery.Scopes#</s
trong></a></div></td>
<td width="25%"><div
align="center"><strong>#DollarFormat(QBDQuery.Expr 1003)#</strong></div></td>

</tr></cfoutput>

First, it tells me that QuoteNumber is undefined within QBDQuery. I can live
with that, so I whack that part of the report and run it again. Another error.
This time QuoteDate is undefined, and I can already see that the rest of the
stuff will also be undefined. So I figure I'll just go into the query and
define the missing jewels. Then they won't be undefined anymore. WRONG! Now
it won't even load at all.

My report as it is right now works well with ###Form.StartDate### &
###Form.EndDate###. But it has no total dollars field and that's the thing I
want so I can see if the salesman is quoting things that are going to make
money or if he's just filling up the system with BS. The report now only uses
data from the Quote_Track table.

Thanks for your help. My one remaining hair also thanks you.

Brendan

[paross1]

You should alias the SUM() column so that you can use that name...

<cfquery name="QBDQuery" datasource="Quotes">
SELECT qt.SalesNumber, qt.CustomerNumber, qt.Scopes, SUM(q.ExtPrice) AS
tot_dollars
FROM QUOTE_TRACK qt, QUOTE q
WHERE qt.QuoteDate BETWEEN .....
...
</cfquery>

Plus, since you are already defining the query in your CFOUTPUT tag, you don't
need to use it in your output. also, if you want to use fields in the output,
you need to select them in your query, so that is why you are getting undefined
on things like QuoteNumber and QuoteDate, etc.

Your output might look something like this if you fix your query and add the
appropriate fields to your select statement:

<cfoutput query="QBDQuery">
<tr>
<td width="25%"><div align="center"><strong>#QuoteNumber#</strong></div></td>
<td width="25%"><div
align="center"><strong>#DateFormat(QuoteDate,"M/D/YYYY")#</strong></div></td>
<td width="25%"><div align="center"><strong>#SalesNumber#</strong></div></td>
<td width="25%"><div align="center"><a
href="FQWCD.cfm?QuoteNumber=#QuoteNumber#"><strong >#Scopes#</strong></a></div></
td>
<td width="25%"><div
align="center"><strong>#DollarFormat(tot_dollars)# </strong></div></td>
</tr>
</cfoutput>

Phil