I am building a application for the purposes of tracking field sales
personnel.

As a result I am having two problems.

Firstly I need to make the performance reports alternate between
whether the member of staff is male or female, i.e. he sold or she
sold

Also I need a report to print off the four months in which each member
of staff achieved the highest sales.

Any help would be most appreciated.

Thanks
Boulent

"Boulent Mustafa" <OhWhite (AT) hotmail (DOT) com> wrote

a function to a field in your report like this:

iif(member.gender = 'M', 'he', 'she')

keyword?) four. But in Fox 3, I would go with a cursor to give you the
totals and orders, then scan the cursor, deleting all but 4 records for each
member.

select;
member,;
month(sales_date) as month,; <<this doesn't consider more than 1 year.
sum(sales) as sold;
from sales_table;
group by member, month;
order by member, sold; <<Maybe a 'desc' goes here?
into cursor SalesTemp

local lReps
lReps = 1
select SalesTemp
scatter memvar fields member
scan
if member = SalesTemp.member
lReps = lReps + 1
else
lReps = 1
scatter memvar fields member
loop
endif
if lReps > 4
delete
endif
endscan

Or something like that. And you may have to put a 'Descending' on that
order by 'sold', but it's after midnight, and I'm on my third glass of wine,
and your problem is looking less and less important as the night goes on :-)

I hope that gives you SOME idea. (I gotta stop giving advice AFTER the
wine...I'm not good enough BEFORE.....)

Bill Browne
www.edgefinderstudios.com

Assuming a table called 'member' with a field called 'gender', you
could add
a function to a field in your report like this:
I am looking at the field settings on the report screen and I am
currently unable to see where to include the if statement.

Its not a case of records, its more a case of twelve fields (one for
each month) and me wanting to display the four months in which highest
sales were achieved without accessing specific details, such as totals
and orders regarding those sales

Thanks
Boulent

"Boulent Mustafa" <OhWhite (AT) hotmail (DOT) com> wrote

Look closely. That's not and 'IF' statement, it's an 'IIF' function. 'IIF'
stands for 'Inline IF'. It is a function with 3 arguments (?). The first
is a comparison. If the comparison results in .t., then the 2nd argument is
returned. If .f., then the third is returned. What we are trying to do
here is fill a character field with what is returned (one of the two
character arguments) by the IIF function.

That's probably not very good database design. You should have a database
with 3 fields: member, sales_date, sales_amount, with one record for each
sale made. Monthly amounts would then be gathered by an SQL statement
similar to :

select;
member, ;
month(sales_date) as sales_month,;
sum(sales_amount) as total;
from sales;
group by member, sales_month

IMHO, good database design stores DATA, never RESULTS. Results are computed
as they are needed. This makes life a lot easier when corrections are made,
or when data shows up late.