Syntax for expressions

[HaChIrish]

I'm not sure if I'm in the right part of the forums because this might be a query question. I have a productivity report based on 2 tables: the primary CUSTOMERS and dependent OPTIONS. Options has 1 field (called OPTIONS, the primary key), and the field has the values SOLD, PENDING, QUOTE, DECLINE. The report was easy enough to build, basing it on a Counselors productivity using a footer for the OPTIONS and using count for each option. The question is on the footer for the Counselor: how would I write an expression that adds the sum of PENDING, SOLD, QUOTE and DECLINE divided by number of SOLD?

A related question is "what's the syntax in an identifier for choosing a particular value from a control?"

 

[llkhoutx]

One way is to use a subreport whose recordsource is a query which does the summing.

 

[HaChIrish]

That makes sense (didn't even think of that). Now how do I write the expression? I'm still not sure how to specify values in a field.

 

[llkhoutx]

the query is

SELECT Sum(IIf([fkey]=1,1,0))/Sum(IIf([fkey]=3,1,0)) AS Pending, Sum(IIf([fkey]=2,1,0))/Sum(IIf([fkey]=3,1,0)) AS Quote, Sum(IIf([fkey]=3,1,0)) AS Sold, Sum(IIf([fkey]=4,1,0))/Sum(IIf([fkey]=3,1,0)) AS Decline
FROM tbCustomers;

where

tbCustomers
CustomerID
sCustomer
FKey


FKey values
1=Pending
2=Quote
3=Sold
4=Decline

Use a similar query as the recordsource for the footer giving the totals. Note that the Sold value is not divided by the Sold.

You can use te report wizard to build the subreport by first saving the string SQL in a query.