Get Totals in a Query 1

[jdegeorge]

Hi

I have a cross-tab query for which I want to get COLUMN and ROW totals. Here's the code:

PARAMETERS [Forms]![frmSignOn]![ctlConversion] Text ( 255 );
TRANSFORM Count(tblGaps.[Gap#]) AS [CountOfGap#]
SELECT tblItemStatus.Item_Status AS Status
FROM (((((tblGaps LEFT JOIN tblItemStatus ON tblGaps.Gap_Status = tblItemStatus.Item_StatusID) LEFT JOIN tblCategory ON tblGaps.Category = tblCategory.CategoryID) LEFT JOIN tblPassword ON tblGaps.NFS_Owner = tblPassword.[UserID#]) LEFT JOIN tblArea ON tblGaps.[Area/Function] = tblArea.AreaID) LEFT JOIN tblFunction ON tblGaps.Function = tblFunction.FunctionID) LEFT JOIN tblContact ON (tblGaps.Conversion = tblContact.Conversion) AND (tblGaps.Conversion_Owner = tblContact.ContactID)
WHERE (((tblGaps.Conversion)=[Forms]![frmSignOn]![ctlConversion]))
GROUP BY tblItemStatus.Item_Status
PIVOT tblCategory.Category In ("Policy/Procedure","Service Challenge","Product","OSG Technology","Other Technology");

Is there such a function or do I have to append the results of this query to a TEMP table and create totalling queries to append those numbers as well?

Please advise before I begin to create the TEMP process...you might be able to save me some time (I hope!).


Jim DeGeorge

 

[dhookom]

Row totals should be a snap by adding a row heading:
Count(tblGaps.[Gap#]) AS [CountOfAllGap]
This will not be limited to the count in only the categories listed in your column headings.

I'm not sure why you want column totals since this is usually performed in a form or report. You could create a union query of your crosstab and a similar crosstab that doesn't include [Item_Status] as a row heading.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jdegeorge]

Duane

Haven't tried this yet, but wanted to answer your question about why wanting column totals.

This query is the record source for a screen for a quick view of some totals. There won't be any report to peform column totals.

I'll let you know if this helps. Thanks!

Jim DeGeorge

 

[dhookom]

You can do column totals in a continuous form.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jdegeorge]

Duane

Put your row total code in and it's working, however, it shows the totals to the left of the columns it's totaling, not after, which is desired. In the query Design View, I moved the column to the right, but when the query actually runs the column is moved to the left of the PIVOT columns.

Is there any way to change that?

Jim DeGeorge

 

[dhookom]

The only way to change this is to:
1) change your requirements to expect the total on the left
2) create a select query based on your crosstab that reorders the columns

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jdegeorge]

Duane

Actually, while the query results were displayed, I dragged the TOTALS column to the right and saved the query. It's appearing there all the time now.

Now I just need to work on those column totals.

However, you still earned a star for the row total help. Enjoy!

Jim DeGeorge

 

[jdegeorge]

Duane

I guess I'll have to give up on the column totals. The data is displayed in datasheet form so I can't change it to continuous form.

Thanks any way for the idea!

Jim DeGeorge

 

[dhookom]

Are you giving up on the union query based on two crosstabs? This shouldn't take more than a couple minutes to create.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]