We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Calculate Sum of a field in a table using access query

avalonwgi
avalonwgi asked
on
Medium Priority
913 Views
Last Modified: 2012-05-06
I would like to add a field to my existing query that would perform a sum calculation off of a field in the table that is shown in the query.  THis field is a cumulative amount that is not in any table.  For example; I know the Machine Num and would like to calculate the cumulated Operating hours.  Table has MachNum and OpHrs.  I am passing the MachNum from another form so I know that coming in.
Comment
Watch Question

Commented:
something like this may help you...

SELECT MachineNum, SUM(OpHrs)
FROM yourTable
WHERE MachineNum = @MachineNum
GROUP BY MachineNum

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
try this:

SELECT Table1.FIELD1, Sum(Table1.FIELD2) AS OpHrs
FROM Table1
WHERE (((Table1.FIELD1)=[Forms].[YourFormName].[txtMachNum]))
GROUP BY Table1.FIELD1;

Where FIELD1 is MachNum , FIELD2 = OpHrs

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
I'm using  txtMachNum as the Name of the control on your form passing the MachNum

mx

Author

Commented:
It looks like it is working.  I still have more to do to fully test but thanks for your help.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
How will this work in an Access database:

@MachineNum

?

Also, the Accepted solution does not take into account

"I am passing the MachNum from another form so I know that coming in."

??
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.