Re: Report in Access97

John,

I forgot one variable in this. To get an accurate percentage I need to go
back about 2 1/2 weeks for the pledge date of the sale. pledge date is one
of my fields. how would i put in this query to group still by salesperson,
but with the date field in also. thanks, Jean

"labtyda" wrote:

Thank you so much John, got it to work :)

"John Spencer" wrote:

Missed a closing parentheses in this expression

Sum(IIF(Paid,[Sale Amount],0))/ Sum([Sale Amount])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


labtyda wrote:
Hi John,
Almost there, the last field isn't working , it says the expression is
missing a closing parenthesis, bracket or vertical bar....thanks

"John Spencer" wrote:

Open a new query
Select your table
Select the fields SalesPersonNumber, Sale Amount
Select View: Totals from the menu
Under Sale amount select Sum
Under SalePersonnumber select GROUP BY
Enter the following in the field block
IIF(Paid,[Sale Amount],0)
Select SUM in the total block
Enter the following in the next field block,
Sum(IIF(Paid,[Sale Amount],0)/ Sum([Sale Amount])
Select Expression in the total block

Try running this query. IF it gives you the desired results, use it as
the source of a report.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


labtyda wrote:
I'm not quite sure I know what I'm doing, can you be more specific? haven't
done much at all with formulas, so it's confusing to say the least. do i
need to insert a column in the query? i tried using the info you gave me, but
it would say i had too many or to few ( ) [ ] (i tried different ways to what
you gave me).

"John Spencer" wrote:

Use a query that looks like the following:

SELECT SalesPersonNumber
, Sum([Sale Amount]) as Sales
, Sum(IIF(Paid,[Sale Amount],0) as Paid
, Sum(IIF(Paid,[Sale Amount],0)/ Sum([Sale Amount]) as PercentCollected
FROM [Your Table]
GROUP BY SalesPersonNumber

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

labtyda wrote:
I am trying to create a report with the following: total sales per
salesperson, total collected per salesperson, and percentage collected per
salesperon. data base has sale amount, date paid, paid (yes or no)
salesperson number (others too, not pertinent to report) any suggestions??

.

Relevant Pages

  • Re: detail section in a report
    ... each transaction may have ... > sale and half of the commissions earned. ... > my report doesn't show the detail of each ... > salesperson. ...
    (comp.databases.ms-access)
  • Re: formula help, sorting information
    ... Make the Salesperson a Page Field, then you can get a report for each ... Saleperson on a separate sheet. ... > each sale to a salespersons sheet and the new or used tally sheet ...
    (microsoft.public.excel.worksheet.functions)
  • detail section in a report
    ... credit for the sale and half of the commissions earned. ... my report doesn't show the detail of each record if the ... named salesperson is 'second' in this particular transaction. ...
    (comp.databases.ms-access)
  • formula help, sorting information
    ... reporting sales. ... corresponding salesperson and reported in a new or used tally sheet. ... What would be perfect is if there is anyway I can easily send each sale ...
    (microsoft.public.excel.worksheet.functions)