Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-12-2018, 09:31 AM   #1
jtidfold
Newly Registered User
 
Join Date: Mar 2018
Location: Dallas, TX, USA
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
jtidfold is on a distinguished road
Calc Query with Multiple Field Criteria

Hello:

It's been 19 years since I've used access and I've completely forgotten all of the query functions. I have a link table (from excel of course) with over 30k rows, so I want to use the processing power of access to do the calculations.

So here's what I have:

A table with multiple fields, one of which is an amount field (numeric value of course).

I have a query started, however I need to do a "SUMIFS" calculation.

Field names to total on are (the "IFS" part):
Job Number
Account

The total is in the "Amount in local currency" field (the "SUM" part).

I need to get the total by job and account and then output that in to a report (so I can export that back to excel). I tried the "Dsum" function in the "Criteria" of both a new "blank" field and in the "Amount in local currency" field, but got quite a few errors (after 19 years I thought I might!!).

Any assistance would be greatly appreciated.

I know this should be simple, but I don't know exactly how to tell MS Access to perform this calculation.

Thanks!!

jtidfold is offline   Reply With Quote
Old 03-12-2018, 10:38 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,055
Thanks: 0
Thanked 669 Times in 654 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Calc Query with Multiple Field Criteria

Q1 to add the fields in a record (simple math)
Sum1: field1+field2+field3

then Q2 that uses Q1 to sum the entire table
Sum(Sum1)
Ranman256 is offline   Reply With Quote
Old 03-12-2018, 10:57 AM   #3
jtidfold
Newly Registered User
 
Join Date: Mar 2018
Location: Dallas, TX, USA
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
jtidfold is on a distinguished road
Re: Calc Query with Multiple Field Criteria

Ok, so how do I write that in Access? Do I create a new field in the first query? If so, how do I tell it to look for unique instances of the "Job Number" and "Account" fields and create the Sum based on those multiple criteria?

jtidfold is offline   Reply With Quote
Old 03-12-2018, 11:40 AM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,585
Thanks: 55
Thanked 2,093 Times in 2,005 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Calc Query with Multiple Field Criteria

First create a simple query and select your table. Add the job number, acct, and the currency field. Press the Total on the ribbon. On the currency field choose sum (not group).
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
jtidfold (03-14-2018)
Old 03-12-2018, 12:56 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,334
Thanks: 13
Thanked 1,403 Times in 1,337 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Calc Query with Multiple Field Criteria

The Sum() function as well as all the others aggregates a SINGLE column of data it is used to turn hundreds of ROWS into a single row with a total or average, etc.

If you want to add some columns together, that is not done with a function. However given that the BE is Excel and you can never trust the contents of any Excel cell, you should use the Nz() function to ensure that the field you select is numeric and not null.

Using the QBE, you can type something like this in the top box of a new column:

MySum:Nz(fld1,0) + Nz(fld2,0) + Nz(fld3,0)

If you really want a totals query that aggregates rows, it might look like this:

MySum:Sum(Nz(fld1,0) + Nz(fld2,0) + Nz(fld3,0))

That expression adds the three fields together and then aggregates based on the Group By clause. So, if you have multiple orders and want the total of all orders for a single customer and you want to add total, shipping, and tax, this is what you would do.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
jtidfold (03-14-2018)
Old 03-13-2018, 06:13 AM   #6
jtidfold
Newly Registered User
 
Join Date: Mar 2018
Location: Dallas, TX, USA
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
jtidfold is on a distinguished road
Re: Calc Query with Multiple Field Criteria

Thanks Pat - "I did the MySum:Sum(" one and got an error message that states "Your query does not include the specified expression 'Profit Center" as part of an aggregate function.

The "Profit Center" field is just data. Is it because that field is the farthest left field in the query design?
jtidfold is offline   Reply With Quote
Old 03-13-2018, 06:24 AM   #7
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,189
Thanks: 83
Thanked 1,530 Times in 1,426 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Calc Query with Multiple Field Criteria

Quote:
The "Profit Center" field is just data. Is it because that field is the farthest left field in the query design?
No. As you have a space in the field name, you must put it in square brackets in your MySum formula i.e. [Profit Centre]

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 03-13-2018, 06:24 AM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,179
Thanks: 58
Thanked 1,127 Times in 1,029 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Calc Query with Multiple Field Criteria

When doing any of the (so-called) SQL aggregates, the rule is that you must reference fields in either GROUP BY clauses, WHERE clauses, or with an aggregate function such as SUM, AVG, MAX, MIN, etc. From what the message says, you included "[Profit Center]" as a "simple" field reference, and that isn't allowed for aggregates.

Stated another way, Access doesn't know what to do with the Profit Center.

- You didn't use it to form a group.
- You didn't use it to form an aggregate
- You didn't use it to eliminate non-conforming records

My suggestion is to either leave it out OR make it the basis for a GROUP BY clause.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 03-13-2018, 06:49 AM   #9
jtidfold
Newly Registered User
 
Join Date: Mar 2018
Location: Dallas, TX, USA
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
jtidfold is on a distinguished road
Re: Calc Query with Multiple Field Criteria

Getting closer:

I got another error message: The expression you entered contains invalid syntax. You may have entered an operand without an operator.

This is the string in the top box in the new column (field): mysum: Sum(Nz[WBS Element],0)+Nz(Account,0)+Nz([Amount in local currency],0))

I did the brackets around the fields with spaces, and since the "Account" field is just the word I left it in parenthesis. What's next?
jtidfold is offline   Reply With Quote
Old 03-13-2018, 03:49 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,334
Thanks: 13
Thanked 1,403 Times in 1,337 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Calc Query with Multiple Field Criteria

The syntax is incorrect. Those pesky square brackets sure get confused with parentheses and that is only one of the reasons that all experts recommend that you use conforming rather than non-conforming object names.

mysum: Sum(Nz([WBS Element],0)+Nz(Account,0)+Nz([Amount in local currency],0))

Make sure that all other fields in the select clause are included in the Group By section.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 03-13-2018, 04:17 PM   #11
jtidfold
Newly Registered User
 
Join Date: Mar 2018
Location: Dallas, TX, USA
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
jtidfold is on a distinguished road
Re: Calc Query with Multiple Field Criteria

So when I "fix" the command, it auto corrects the rest to come to this:

mysum: Sum(Nz([WBS Element],0)+Nz([Account],0)+Nz([Amount in local currency],0))

However, when it runs, it give an error that states: Your query does not include the specified expression 'WBS element' as part of an aggregate function.

When I hit "OK," nothing in the command string is highlighted.

Also, what's a "Group By" section?

Like I said, I haven't used this in 19 years, so I'm going to need a bit of assistance
jtidfold is offline   Reply With Quote
Old 03-13-2018, 07:24 PM   #12
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,179
Thanks: 58
Thanked 1,127 Times in 1,029 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Calc Query with Multiple Field Criteria

Quote:
Also, what's a "Group By" section?
Ouch! Quickie refresher here. You can do aggregates over an entire table (aggregate of the whole) or you can do aggregates subdivided according to some field like a department or a person or a part category or a zip code (aggregate of the group). When you want subdivisions of your aggregates, you use a GROUP BY clause for the segregating characteristic and form an aggregate for each group uniquely identified by a value in the field selected in the GROUP BY clause. For aggregates other than "of the whole", Access wants to gather similar records together which is why you have a GROUP BY clause in the query - to identify the way you can differentiate group membership.

When you group similar records together, Access has a problem in that if you have records with non-identical values AND have initiated an aggregate operation, you are telling Access to do two different things at once.

So let's take the extreme case. You have an autonumber prime key (PK) and a department name and the values you want to form in aggregation. You want to see the aggregates for each department - so your SQL statement includes a GROUP BY clause on the department name or code. Then you include the aggregate function, let's say SUM(). So what Access wants to do next is to show you one record with the department name or code and the sum of the field or expression you have developed for this aggregate. So far, so good. Now add the PK in the list of things returned by the query. But THAT is a unique field for every record.

Access has to EITHER (a) return each unique PK value, which negates its ability to form a group for aggregation, or (b) it returns a unique department number - but if at least two records exist to be summed for that department, it needs to return at least two different values of the PK at the same time because it can only return one record. Which is why the rule exists that a field must EITHER be referenced by GROUP BY, SORT, an aggregate function, or a WHERE clause element. Anything else forces Access to try to do two incompatible things at once.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
jtidfold (03-14-2018)
Old 03-14-2018, 05:33 AM   #13
jtidfold
Newly Registered User
 
Join Date: Mar 2018
Location: Dallas, TX, USA
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
jtidfold is on a distinguished road
Re: Calc Query with Multiple Field Criteria

No need for the "ouch" LOL - I warned everyone that I was a newbie again. I'm probably trying to do something too complex for someone with my level of knowledge of Access

So technically I can "group" the "WBS Element" and "Account" fields so the Sum() command will work off that by telling it to sum the "Amount in local currency" field to any UNIQUE GROUPING of the other two fields? If so, I need a refresher in how to do that. I didn't see the grouping option in the ribbon (probably looking in the wrong place).

I don't have a Primary Key in the link table, and that's actually on purpose. There are going to be cases where the rows can be duplicated so I'm ok without one. So if this grouping can do what I need it to do in the query, then I'm set for this and actually quite a few other things.

The only other question I have is whether or not this subtotaling query should be it's own, and then a separate linked query to bring the rest of the results. If the grouping works then probably not but I want to be sure to ask.
jtidfold is offline   Reply With Quote
Old 03-14-2018, 09:43 AM   #14
jtidfold
Newly Registered User
 
Join Date: Mar 2018
Location: Dallas, TX, USA
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
jtidfold is on a distinguished road
Re: Calc Query with Multiple Field Criteria

Quote:
Originally Posted by arnelgp View Post
First create a simple query and select your table. Add the job number, acct, and the currency field. Press the Total on the ribbon. On the currency field choose sum (not group).
Ok, I'm going to close this string and open a new one because now I need to link the "simple" query with the table data to create an output query.

I used arnelgp's suggestion to create the simple query and it worked. The only thing I had to do was go in to SQL view and change it a little bit to this:

SELECT US8246.[WBS element], US8246.Account, Round(Sum(US8246.[Amount in local currency]),2) AS [SumOfAmount in local currency]
FROM US8246
GROUP BY US8246.[WBS element], US8246.Account
ORDER BY US8246.[WBS element];

I had to add the "Round" to the Sum because it was returning more than two decimal places, even though I made the field in the table "Currency."
jtidfold is offline   Reply With Quote
Old 03-14-2018, 10:14 AM   #15
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,585
Thanks: 55
Thanked 2,093 Times in 2,005 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Calc Query with Multiple Field Criteria

Instead if using round(), go to design view of your query. Select the sum field, on the property window (right pane) choose the format you like.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Reply

Tags
multiple criteria , query , report output , sumifs

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add Multiple Criteria to Same field in a Query bconner Queries 5 04-27-2009 05:31 AM
Query with multiple criteria on the same field denvrado Queries 2 03-04-2009 03:15 AM
Can you Query Using Multiple criteria from same field? rainman89 Queries 3 03-09-2007 11:45 AM
Multiple Criteria in same Query Field? Sticker Tires Queries 1 05-19-2004 05:53 AM
Query calc criteria question Kathleen Badeau Queries 11 09-06-2002 07:01 AM




All times are GMT -8. The time now is 07:39 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World