How to get an instant sum (1 Viewer)

A

ariansman

Registered User.

Local time

Today, 02:11

Joined

Apr 3, 2012

Messages

157

• Aug 8, 2018

• #1

I have the following form based on a table:

Code:Copy to clipboard

empName             salary            working
jack                     1000              yes/no
Steve                   1500               yes/no
Janet                   200                yes/no
summary:  [number]

fields “empname” and”salary” are locked, but the working is field is able the be switched between “yes” and “No” by the operator by a click.

Is it possible that the "summary" shows the instant sum of salaries of the employs whose working status is “yes”? For example all “working” are yes and summary shows 2700. But the operator clicks on the working status of Steve and changes it to NO, and the summary right away shows 1200.

i am not sure if this is doable on a form or report.
thank you

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 02:11

Joined

Aug 30, 2003

Messages

36,125

• Aug 8, 2018

• #2

In the form or report footer:

=Sum(IIf(Working = True, Salary, 0))

 

A

ariansman

Registered User.

Local time

Today, 02:11

Joined

Apr 3, 2012

Messages

157

• Aug 8, 2018

• #3

i am havuing the form in data sheet view. in data sheet view there is not a footer.is that right?

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 02:11

Joined

Aug 30, 2003

Messages

36,125

• Aug 8, 2018

• #4

That could be; I never use datasheet view.

 

missinglinq

AWF VIP

Local time

Today, 05:11

Joined

Jun 20, 2003

Messages

6,423

• Aug 8, 2018

• #5

You'd have to use the old trick of using a Continuous View Form and tweaking it to look like a Datasheet View Form.

Linq ;0)>

 

MarkK

bit cruncher

Local time

Today, 02:11

Joined

Mar 17, 2004

Messages

8,181

• Aug 8, 2018

• #6

ariansman said:

i am havuing the form in data sheet view. in data sheet view there is not a footer.is that right?

Click to expand...

If you are in datasheet, and there is no footer, then where, in the description of the form's design you posted above, is the summary?
Mark

 

June7

AWF VIP

Local time

Today, 01:11

Joined

Mar 9, 2014

Messages

5,470

• Aug 8, 2018

• #7

Cross posted http://www.accessforums.net/showthread.php?t=73250

 

isladogs

MVP / VIP

Local time

Today, 10:11

Joined

Jan 14, 2017

Messages

18,219

• Aug 8, 2018

• #8

Just in case you're not aware, it is possible to add a totals row to a datasheet - in a table/query or in a form.
See https://www.access-programmers.co.uk/forums/showthread.php?t=300211

However it will total all visible rows so, for your purposes, you would need to use a query as your datasheet source filtered for Working = yes.
OR use code to change the salary to 0 when Working = No.

I'll leave you to decide whether that's flexible enough for your needs

 

Last edited: Aug 9, 2018

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 19:11

Joined

Jan 20, 2009

Messages

12,852

• Aug 8, 2018

• #9

ariansman said:

i am havuing the form in data sheet view. in data sheet view there is not a footer.is that right?

Click to expand...

A Datasheet form does have a footer. It just doesn't display. So you can put a textbox in the footer to do the Sum then refer it in the ControlSource of another textbox.

To use this technique, put a the Datasheet form in a subform and display the result in the parent form.

However I generally use Continuous Forms instead.