Report in Access97

[labtyda]

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??

 

[John Spencer]

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]

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).

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

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??

 

[John Spencer]

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
'====================================================

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).

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

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??

 

[labtyda]

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

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
'====================================================

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).

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??

 

[John Spencer]

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
'====================================================

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

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
'====================================================

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).

:

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??

 

[labtyda]

Thank you so much John, got it to work

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
'====================================================

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

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).

:

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??

 

[labtyda]

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

Thank you so much John, got it to work

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
'====================================================

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

:

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).

:

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??