RE: IIf to return a formula or field

I just built the tables and tested the query below - works.

SELECT q_cust.cu_cust_name, order.or_part_desc, RejectQ.jc_mach_code,
order.or_cpu, IIf([jc_mach_code]<140,[or_cpu]/2,[or_cpu]) AS Price,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.mt_mach_desc,
RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;


"waterbug" wrote:

> I copied the SQL below. I have had to put
> "=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" in the report under the
> or_cpu (price) field. WHen I put it in the query on the field line
> "cost:=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" I get an error that
> states "you tried to execute a query that does not inlcude the specified
> expression 'cu_customer_name' as part of an aggregate function".
> "cu_customer_name" is just the field with the customer name. Thanks again.
>
> The value comes from when I enter a price each for the product. Normally I
> would say I entered in a form, but the tables in this application are linked
> into another foxpro program but the values are correct when I run the query
> by itself without any IIf function. So if the part cost $30.00 or $100.00 it
> shows up fine, but in the expression you had me try, it is adding up all the
> values.
>
> example:
> Date range 08/01/05-08/05/05
> customer A price $30.00 each and the return on the expression becomes
> $380.00
> customer B price $200.00 each (<140) " " " " " $190.00
> customer C price $50.00 each " " " " " $380.00
> customer D price $100.00 each (<140) " " " " " $190.00
>
> SELECT q_cust.cu_cust_name, order.or_part_desc, order.or_cpu,
> RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
> RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.jc_mach_code,
> RejectQ.mt_mach_desc, RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
> FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
> RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;
>
> thanks -
>
.

Relevant Pages

  • RE: Problem with my Query
    ... this is the SQL for the simple query: ... And this is the SQL for the more complicated query: ... tblInvoiceLines.[Unit Price], tblTransactionRequest.InvoiceRequest, ...
    (microsoft.public.access.queries)
  • Re: Selecting Lowest Value from a Table
    ... Your answer was very helpful as with my limited experience with SQL was ... unaware that JOIN with a Sub Query Like that. ... the ManPartNum and Price Fields are repeated in the returned records. ...
    (microsoft.public.sqlserver.server)
  • Re: A Question of Tables
    ... Without getting into your specific query, ... > price (which should be in the STOCKPRICES table) with this exchange rate. ... > selected lang and currency, ...
    (microsoft.public.sqlserver.programming)
  • Re: Query Help
    ... A good starting point for SQL is "SQL Queries for Mere Mortals" by Hernandez ... As for asking the user for the plate values, this query will ask the user ... (SELECT Avg(T.[Sale Price]) ... FROM TableName AS T ...
    (microsoft.public.access.queries)
  • Re: A query that joins 2 tables
    ... halves of the query in the Design Grid, and then copying the text from the ... SQL view of each and pasting either side of the "UNION" operator. ... > SELECT Company, Product, Reference, Price ...
    (microsoft.public.access.queries)
Loading