Calc Query with Multiple Field Criteria (1 Viewer)

jtidfold

Registered User.
Local time
Today, 10:09
Joined
Mar 12, 2018
Messages
11
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!!
 

Ranman256

Well-known member
Local time
Today, 11:09
Joined
Apr 9, 2015
Messages
4,339
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)
 

jtidfold

Registered User.
Local time
Today, 10:09
Joined
Mar 12, 2018
Messages
11
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?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:09
Joined
May 7, 2009
Messages
19,169
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).
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:09
Joined
Feb 19, 2002
Messages
42,970
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.
 

jtidfold

Registered User.
Local time
Today, 10:09
Joined
Mar 12, 2018
Messages
11
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?
 

isladogs

MVP / VIP
Local time
Today, 15:09
Joined
Jan 14, 2017
Messages
18,186
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]
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:09
Joined
Feb 28, 2001
Messages
26,999
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.
 

jtidfold

Registered User.
Local time
Today, 10:09
Joined
Mar 12, 2018
Messages
11
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:09
Joined
Feb 19, 2002
Messages
42,970
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.
 

jtidfold

Registered User.
Local time
Today, 10:09
Joined
Mar 12, 2018
Messages
11
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
:banghead:
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:09
Joined
Feb 28, 2001
Messages
26,999
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.
 

jtidfold

Registered User.
Local time
Today, 10:09
Joined
Mar 12, 2018
Messages
11
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 :p

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

Registered User.
Local time
Today, 10:09
Joined
Mar 12, 2018
Messages
11
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."
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:09
Joined
May 7, 2009
Messages
19,169
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.
 

Users who are viewing this thread

Top Bottom