Column Totals on Form in Datasheet View???

[jdegeorge]

Hi

I have a form that's based on a crosstab query that's displayed in datasheet form.

I have row totals (thanks to dhookom) but need to know how to get column totals.

dhookom suggested changing the form to continuous, but it really needs to be in datasheet view.

Any ideas?

Jim DeGeorge

 

[PHV]

Take a look at the DSum function you can use in the ControlSource of an unbound textbox.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jdegeorge]

PHV

The form is displayed in datasheet form, as there's really only one record when all is said and done.

I can't figure out where to add the control with the DSUM source.

The query properties have the columns defined in them, and the only other colum I was able to add was for TOTALS. I need to sum the VALUE column for each heading.

This is the SQL for the query:

PARAMETERS [Forms]![frmSignOn]![ctlConversion] Text ( 255 );
TRANSFORM Count(tblGaps.[Gap#]) AS [CountOfGap#]
SELECT tblItemStatus.Item_Status AS Status, Count(tblGaps.[Gap#]) AS Totals
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 tblContact ON (tblGaps.Conversion_Owner = tblContact.ContactID) AND (tblGaps.Conversion = tblContact.Conversion)) LEFT JOIN tblCore ON tblGaps.CoreID = tblCore.CoreID) LEFT JOIN tblFunction ON tblGaps.FunctionID = tblFunction.FunctionID) LEFT JOIN tblGroup ON tblGaps.GroupID = tblGroup.GroupID) LEFT JOIN tblDivision ON tblGaps.DivisionID = tblDivision.DivisionID
WHERE (((tblGaps.Conversion)=[Forms]![frmSignOn]![ctlConversion]))
GROUP BY tblItemStatus.Item_Status
PIVOT tblCategory.Category In ("Policy/Procedure","Service Challenge","Product","OSG Technology","Other Technology");

Here's the datasheet view (spacing isn't perfect in the PASTE)

Status Policy/Procedure Service Challenge Product OSG Technology Other Technology Totals
Closed - Client Action 1 1
Closed - Impl Action 1 1
Open 30 38 5 3 2 78

How can I add a field to total each column with DSUM as you suggest? I can't figure it out.

Jim DeGeorge

 

[PHV]

I quite don't understand why don't want a continuous form.
You add the unbound total controls in the footer section.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZmrAbdulla]

Place your form as a subform in an Unbound Form.
Size the subform to cover maximum of the main form.
Set the calculations in the subform then bring it to the mainform.
So the sum will be available even if your subform is in the datasheet view.
hope this helps

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me