I have a cross tab query which shows salespeople, their YTD commission plus the last 3 months of commission. What I want to also do is to show the total commission for each of the 3 months by adding up the respective columns. I know I cant do that in the query but hoped I could do it in a report. I have set up a report that shows the crosstab then tried to total the columns in the footer but it didnt work. Does anyone have any idea how/if this can be done?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Adding column totals to cross-tab report 2
- Thread starter shaunacol
- Start date
Which footer? You cannot use the page footer for totals, but the report footer and group footers are fine. Have you based the report on the crosstab or have you used a subreport to show the crosstab query? If it is the first option, it is always worth considering the wizard, which will create total for you, when you choose Group, this is always useful for an example. If it is the second option, you will need to create a summary query.
- Thread starter
- #3
Hi, thanks for the reply. I have tried entering in all different footers but it does not recognise the text box name (access thinks I am asking for data to be inputted because it doesnt not recognise the text box name I am trying to sum). I did use the wizard to set up the report but I dont thing the grouping will work. The reason being that I only want to see 3 months of data so have set the crosstab query 'column headings' property to those 3 months. The issue is that these 3 months are listed as individual items in the report wizard. Therefore if I group on January then there is still Feb and Mar to consider where I cant get the totals. Similarly, I think this is the reason why my calculations are not recognised in any of the footers. Maybe there is a better way of showing totals for rows and columns on the same report or even in a form??
-
2
- #4
You can't total a "text box". You can total fields or expressions from your report's record source. If you have months (or other date intervals) as column headings, look in the FAQs for this forum for a solution.
If you can't find it or it doesn't fit, come on back.
Duane
Hook'D on Access
MS Access MVP
If you can't find it or it doesn't fit, come on back.
Duane
Hook'D on Access
MS Access MVP
Here is one FAQ to get you started:
- Thread starter
- #6
Thanks for the info but I used the FAQ that remou points out to create my cross tab query in the first place and I cant find any FAQs that tell me how to then total the columns from that format. In addition, I am trying to total text boxes which are based on the results of that query (i.e. the reports record source).
For clarification I want a report that uses my [sales] field to calculate the YTD sales and then shows the last 3 months broken down with totals at the end like this:
Jan Feb Mar YTD
Simon £34000 £33000 £32000 £99000
Jenny £1000 £2000 £3000 £6000
Pete £4000 £5000 £7000 £16000
Total per month £39000 £40000 £42000 £121000
But all I can get from a cross tab query using the FAQ is this:
Jan Feb Mar YTD
Simon £34000 £33000 £32000 £99000
Jenny £1000 £2000 £3000 £6000
Pete £4000 £5000 £7000 £16000
I thought I could then create a report based on the query above and do my own calculations to show the totals but that does not work. Any advice appreciated.
For clarification I want a report that uses my [sales] field to calculate the YTD sales and then shows the last 3 months broken down with totals at the end like this:
Jan Feb Mar YTD
Simon £34000 £33000 £32000 £99000
Jenny £1000 £2000 £3000 £6000
Pete £4000 £5000 £7000 £16000
Total per month £39000 £40000 £42000 £121000
But all I can get from a cross tab query using the FAQ is this:
Jan Feb Mar YTD
Simon £34000 £33000 £32000 £99000
Jenny £1000 £2000 £3000 £6000
Pete £4000 £5000 £7000 £16000
I thought I could then create a report based on the query above and do my own calculations to show the totals but that does not work. Any advice appreciated.
Why can't you add text boxes in the report footer with control sources like:
=Sum([Your Field Name])
Duane
Hook'D on Access
MS Access MVP
=Sum([Your Field Name])
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #8
I have tried that in the page footer and report footer and either got 'error' or just nothing in the actual report. I can not do this in a group footer because of the above explanation (i.e. each month becomes a separate entity so it will only work if I have 3 footers (one for each month) and put the calculation in there. Then it just looks stepped and messy....
I am beginning to think it is impossible! Thanks to everyone who is trying to help me resolve!
I am beginning to think it is impossible! Thanks to everyone who is trying to help me resolve!
Totals in page footers never work without a lot of work. Can you provide the field names from your report's record source as well as the control sources of your totals text boxes in your report footer?
Can you provide the SQL view of the report's record source?
Duane
Hook'D on Access
MS Access MVP
Can you provide the SQL view of the report's record source?
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #11
My table is called SalesORders and I am using 2 fields - GP (which is £s), Saleperson (text). Here is the query that my report is based on:
TRANSFORM Count(SalesOrders.GP) AS CountOfGP
SELECT Salesorders.Salesperson, COunt(Salesorders.GP) AS [Total of GP]
FROM SalesOrders
GROUP BY SalesOrders.Salesperson
PIVOT Format([DateOfSale], "mmm") In ("Jan,"Feb","Mar");
Thanks again....
TRANSFORM Count(SalesOrders.GP) AS CountOfGP
SELECT Salesorders.Salesperson, COunt(Salesorders.GP) AS [Total of GP]
FROM SalesOrders
GROUP BY SalesOrders.Salesperson
PIVOT Format([DateOfSale], "mmm") In ("Jan,"Feb","Mar");
Thanks again....
Apparently you didn't "use(d) the FAQ that remou points". Your columns are created as numeric so you should have no trouble adding a text box to the report footer section with a control source of:
=Sum([Jan])
I assume you realize you will have to change this query and report every quarter. Also, the monthly totals will span all years in the table.
The FAQ that remou suggested uses a relative month so it will return the 3 months you want without changing anything.
Duane
Hook'D on Access
MS Access MVP
=Sum([Jan])
I assume you realize you will have to change this query and report every quarter. Also, the monthly totals will span all years in the table.
The FAQ that remou suggested uses a relative month so it will return the 3 months you want without changing anything.
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #13
Are you sure you placed the control in the report footer and not the Page footer? When you look at the datasheet view of the report's record source, does the Jan column appear left or right aligned?
Duane
Hook'D on Access
MS Access MVP
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #15
Hi shaunacol
You can thank dhookom for his valuable answers by clicking:
Thank dhookom
for this valuable post!
You can thank dhookom for his valuable answers by clicking:
Thank dhookom
for this valuable post!
- Thread starter
- #17
- Status
Similar threads
- Locked
- Question
- Locked
- Question