Total Column in Query

[spoolinaz]

I'm trying to create a simple sales query by manufacture by month. I want the month as the column headers (with a total YTD Column). Mfg's are the row headers. The query is already completed and I figured out how to do the row total but can't figure out how to create total column. Can I not add a total column in design view? How would I do that?

[June7]

You built a CROSSTAB query?

No, not in query Design View.

Open query in Datasheet, click Totals icon on the ribbon. This should open a Totals row at bottom of the query. Pick the aggregate function desired under appropriate fields.

If you want totals on a report, use aggregate functions in textbox in header/footer sections.

[spoolinaz]

Thanks for your response.

Yes, it's a crosstab query. I figured out how to do the Total Row it's the Total Column I'm having issues with. So I want to add Jan, Feb, Mar...... of 2014 and have a total column.

Hope this helps. Thanks

[spoolinaz]

Here is a sample of what I'm trying to accomplish. Thanks

[June7]

Sorry, I read that backwards, thought the row was what you needed.

If you use the CROSSTAB query wizard to build the crosstab, it will automatically generate the totals column. I've never figured out how to do it manually, I seldom have need of CROSSTAB.

[spoolinaz]

I've tried to create it using the Wizard and I still don't get a total column. I can recreate no problem. At what point during the wizard does it ask for a total column? It asks If I want a row total but not a column total. Thanks

[rpeare]

perform whatever calculation yields your month to month numbers (if it's net sales vs net purchases for example).
in the crosstab row make it ROW HEADING
make the total SUM

[rpeare]

This is my data:

MfgName	MfgDate	MfgNet
Company 1	1/1/2013	$5.00
Company 1	2/1/2013	$10.00
Company 1	3/1/2013	$15.00
Company 1	4/1/2013	$10.00
Company 2	1/2/2013	$2.00
Company 2	2/2/2013	$3.00
Company 2	3/2/2013	$5.00
Company 2	4/2/2013	$1.00
Company 3	1/3/2013	$6.00
Company 3	2/3/2013	$5.00
Company 3	3/3/2013	$4.00
Company 3	4/3/2013	$3.00

This is my SQL

TRANSFORM Sum(tblTest.MfgNet) AS SumOfMfgNet
SELECT tblTest.MfgName, Sum(tblTest.MfgNet) AS RowTotal
FROM tblTest
GROUP BY tblTest.MfgName
PIVOT MonthName(DatePart("m",[mfgdate]),True);

This is my result

MfgName	RowTotal	Apr	Feb	Jan	Mar
Company 1	$40.00	$10.00	$10.00	$5.00	$15.00
Company 2	$11.00	$1.00	$3.00	$2.00	$5.00
Company 3	$18.00	$3.00	$5.00	$6.00	$4.00

[June7]

I don't understand. That result shows total column. Isn't that what you want? Then with the query in datasheet view, click the Totals button to open the aggregate row.

Or use the CROSSTAB as RecordSource for a report and do aggregate calcs in report footer.

Why are the month headers in that order?

[rpeare]

well that's just my example to show how to get the vertical total column which I thought he wanted, the month columns he's already got to his satisfaction, I'm just concerned with showing the total on the row, not the order of the months.

[June7]

Ooops, didn't notice the poster name.