help with SELECT statement

[Chris Hohmann]

"Aaron" <abroadway@ameritrust.com> wrote in message
news:05a601c365df$b31a8d40$a401280a@phx.gbl...

> I have the the below select statement, which is only part
> of what I need.
>
> Set rsAE = oConn.Execute("Select count(DISTINCT
> GEN.lo_rep, sum(loan_am) as cnt) as total_records from
> Gen.DBF GEN, USERINFO U where (((action_date>= {" &
> (start_date2) & "} and action_date < {" & (end_date)
> & "}) or (app_date>= {" & (start_date2) & "} and app_date
> < {" & (end_date) & "}) and (branch_type = [B]))) " )
>
> **I need to order by the cnt, but I need cnt to be the
> sum of the loan_amt only where action_date>= {" &
> (start_date2) & "} and action_date < {" & (end_date)
> & "})
>
> I do not know how to do this. Please help!!!!! Thanks,

"SUM(ABS(action_date>= {" & start_date2 & "} and action_date < {" &
end_date & "})*loan_amt) AS cnt"

Notes:
1. Don't use ODBC. Use OLEDB.
2. Use a stored procedure/parameterized query
3. When posting database related questions, please specify which
database and version you are using and the structure of the tables in
question.

HTH
-Chris

[Aaron]

Sorry,

Thanks for the help, but I cannot do a stored prod b/c I
am accessing foxpro tables.

Any onther ideas?
Thanks

>-----Original Message-----
>"Aaron" <abroadway@ameritrust.com> wrote in message
>news:05a601c365df$b31a8d40$a401280a@phx.gbl...

>> I have the the below select statement, which is only

part

>> of what I need.
>>
>> Set rsAE = oConn.Execute("Select count(DISTINCT
>> GEN.lo_rep, sum(loan_am) as cnt) as total_records from
>> Gen.DBF GEN, USERINFO U where (((action_date>= {" &
>> (start_date2) & "} and action_date < {" & (end_date)
>> & "}) or (app_date>= {" & (start_date2) & "} and

app_date

>> < {" & (end_date) & "}) and (branch_type = [B]))) " )
>>
>> **I need to order by the cnt, but I need cnt to be the
>> sum of the loan_amt only where action_date>= {" &
>> (start_date2) & "} and action_date < {" & (end_date)
>> & "})
>>
>> I do not know how to do this. Please help!!!!! Thanks,

>
>"SUM(ABS(action_date>= {" & start_date2 & "} and

action_date < {" &

>end_date & "})*loan_amt) AS cnt"
>
>Notes:
>1. Don't use ODBC. Use OLEDB.
>2. Use a stored procedure/parameterized query
>3. When posting database related questions, please

specify which

>database and version you are using and the structure of

the tables in

>question.
>
>HTH
>-Chris
>
>
>.
>

[Chris Hohmann]

"Aaron" <abroadway@ameritrust.com> wrote in message
news:0d5101c36641$ab0673a0$a101280a@phx.gbl...

> Sorry,
>
> Thanks for the help, but I cannot do a stored prod b/c I
> am accessing foxpro tables.
>
> Any onther ideas?
> Thanks

The notes were just afterthoughts. Did you try the following expression
for cnt?

"SUM(ABS(action_date>= {" & start_date2 & "} and > action_date < {" &
end_date & "})*loan_amt) AS cnt"

Using this expression instead of your existing expression for cnt should
give you the values you're looking for.

HTH-
Chris