How do I create a summary on a report that lists montly and quartery sales, returns and net sales by salesperson.

mchapman286
mchapman286 used Ask the Experts™
on
I have a database that captures all potential and actual sales and returns.  What I would like to do is on the Sales Report have Gross Sales by month, under that have whatever was returned and for a total line have the net sales.  The gross sales needs to be by salesperson then a Gross Sales Total, under that I need returns (not by salesperson but total for that month).  Then the last line would be net sales.

Example:
                                   Jan              Feb           Mar            .................
John
Joe
Mary
_______________________________________________________
Gross Sales              
Returns
Net Sales  

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Use crosstab query.
post your field names so I can help

Commented:
I imagine Returns are in a different table than Sales??  Too much guessing here so far.

Commented:
You should create a crosstab query then if you want you can append the data to another table using append query
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
As GRayL mentions, you have asked a question here that requires a lot of guesswork as to your table structure and the data contained in the table.

The solution may involve:
Report Manipulation
VBA Coding
SQL
...so you must be fairly knowledgeable in all 3.

You could go the CrossTab query route, but there are several pitfalls when using them as the direct source for a Report, see here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_24577123.html

JeffCoachman

Author

Commented:
Hi Everyone:
Thanks for your responses.  Hopefully I can clarify my question.  

In one table I have the salespeople names.  In another table are the sales and returns.  If an item sells the date of sale is recorded and amount of sale.  If within the year an item is returned then another field within the same table as sales records a negative number and the date of the return.  The $ amount of the return would be taken off of that months sales.  I currently have a query that pulls all the information I need for the details of my sales report and have a crosstab query to give me a summary that returns the gross sales per month by salesperson.  

The detailed query has the following field names:  Client, Salesperson, Itemsold, salestatus, DateofSale, Amount, ReturnAmount, ReturnDate.  

My field names in the cross tab query are:  Salesperson, Format([DateofSale]),"mmm"), Amount, Total of Amount:Amount.  

The sales show up how I want them to in my crosstab query and in my report.  Now I need to have on the report a row under gross sales that lists the total of returns for the office by month (does not need to be by salesperson).  Under that I need the net sales by month.  I cant seem to create this in the subreport (crosstab results) or in the main report (detailed report results).

I'm not great at coding in either VBA or SQL but know some basics.

Thanks for any help you can provide.

Commented:
Assuming tblSalesPeople is linked to tblSalesReturns by SalesID (a number), using tblSales, the Cross Tab would look like the first part of the following to which you have to add three more crosstabs as Unions:

PARAMETERS [EnterYear - yyyy] Long
TRANSFORM Sum(Amount)
SELECT SalesID, Sum(Amount) AS YrTotal
FROM tblSales
WHERE Year(DateOfSale) = [EnterYear - yyyy]
GROUP BY SalesID
PIVOT Format(DateOfSale,"yyyymm")
                                                                                'Now add three unions
UNION ALL
TRANSFORM Sum(Amount)
SELECT "Gross Sales", Sum(Amount)
FROM tblSales
WHERE Year(DateOfSale)=[EnterYear - yyyy]         'This one gives Gross Sales
GROUP BY "Gross Sales"
Pivot Format(DateOfSale,"yyyymm")

UNION ALL
TRANSFORM Sum(ReturnAmount)
SELECT "Returns", Sum(ReturnAmount)
FROM tblSales
WHERE Year(DateOfSale)=[EnterYear - yyyy]         'This one gives Returns
GROUP BY Null
Pivot Format(DateOfSale,"yyyymm")

UNION ALL
TRANSFORM Sum(Amount-ReturnAmount)
SELECT "Net Sales", Sum(Amount-ReturnAmount))
FROM tblSales
WHERE Year(DateOfSale)=[EnterYear - yyyy]         'This one gives Net Sales
GROUP BY "Net Sales"
Pivot Format(DateOfSale,"yyyymm");

While this is off the top of my head, my logic says it should work.  You can get the Sales persons name using a join after you get this part working.  

Author

Commented:
I've tried putting the union in and get a syntax error in TRANSFORM statement pop-up box.

Commented:
PARAMETERS has to end with a semi-colon.  In my test I wrapped each cross tab (between UNION ALL)  in parentheses and it worked.

PARAMETERS [EnterYear - yyyy] Long;
(TRANSFORM Sum(Amount)
SELECT SalesID, Sum(Amount) AS YrTotal
FROM tblSales
WHERE Year(DateOfSale) = [EnterYear - yyyy]
GROUP BY SalesID
PIVOT Format(DateOfSale,"yyyymm"))
                                                                                'Now add three unions
UNION ALL
(TRANSFORM Sum(Amount)
SELECT "Gross Sales", Sum(Amount)
FROM tblSales
WHERE Year(DateOfSale)=[EnterYear - yyyy]         'This one gives Gross Sales
GROUP BY "Gross Sales"
Pivot Format(DateOfSale,"yyyymm"))

UNION ALL
(TRANSFORM Sum(ReturnAmount)
SELECT "Returns", Sum(ReturnAmount)
FROM tblSales
WHERE Year(DateOfSale)=[EnterYear - yyyy]         'This one gives Returns
GROUP BY Null
Pivot Format(DateOfSale,"yyyymm"))

UNION ALL
(TRANSFORM Sum(Amount-ReturnAmount)
SELECT "Net Sales", Sum(Amount-ReturnAmount))
FROM tblSales
WHERE Year(DateOfSale)=[EnterYear - yyyy]         'This one gives Net Sales
GROUP BY "Net Sales"
Pivot Format(DateOfSale,"yyyymm"));

Commented:
Of course you have removed my four comment lines.

Author

Commented:
Thanks GRayL.  For some reason I am still getting a syntax error.  I have tried creating an empty database with only the one table - tblSales with only the fields need and don't seem to be able to get it to work.  For your example.  I am simply adding it to a blank query in the SQL View and then running it?  This is what I have done without success.  I am using Access 2003 and have attached the sample database.  The query text is pasted into a report as I couldn't get the query to save.  Thanks for all your help.
testcrosstab.mdb

Commented:
I have not been available for the past three weeks, but I can now spend some time on a solution - nothing guaranteed - if that is agreeable to all.
Commented:
I have a solution but you need to change the structure of your table.  I saved your old table as tblSales1, and redid the structure in tblSales.
testcrosstab.mdb

Author

Commented:
Hi GRayL.   I have been successful using your solution.  I ended up using a workaround back in July but have learned from your solution and will be able to use it for refrence if I need to have something similar done in the future.  Thank you for your help.

Commented:
Thanks, glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial