SumIF in Access? Sum Field Values only if another field's values meet criteria

[orange]

You could try this in the query design, sql view

SELECT A.[Bus Unit], B.Charge, B.Account
, Sum(B.[Hours Paid]) AS [SumOfHours Paid]
, Sum(B.[Earnings Amount]) AS [SumOfEarnings Amount]
FROM [ref_Charge CC by Bus Unit] as A 
INNER JOIN [2015 Payroll Data] AS B 
ON A.[Charge CC] = B.Charge
WHERE (((B.[Pay Period Number])="03" Or (B.[Pay Period Number])="04"))
AND
 (((B.Account)="00013" Or (B.Account)="00014" Or (B.Account)="00019" Or (B.Account)="00021" Or (B.Account)="00024"))
GROUP BY A.[Bus Unit], B.Charge, B.Account
ORDER BY A.[Bus Unit], B.Charge, B.Account;

You really should remove all the embedded spaces in names, when you get a chance.

[orange]

For reference:
When you cross post you should advise readers that you have done so.
It isn't wrong to cross post, it is wrong to not inform people.

Here's why.

Good luck with your project.

[ssanfu]

PMFJI,

Just a few comments.

I totally agree with orange about the use of spaces in object names. Shouldn't do it.


I would have "Pay Code" in a look up TABLE (not field). You wouldn't have to type as much (the long description) and it would be consistent entries. In the table, you could have a Boolean field to indicate if the record should be used in the "ActualHours" calculation. Instead of the query field (in RED), you could use

ActualHours: IIF([Pay Code], [hours paid],0)

I question the table structure. You have a table "[2015 Payroll Data]". Anytime the year is used in the name of a table, it tells me that the structure is not normalized. When the year changes, do you have to edit the queries, forms and reports? If so, this is is considered a bad design.

The year "2015" in the table name is actually data. The table "Payroll Data" should have an additional field for the year. Then just filter by year - no design changes needed when the year changes.



Maybe you already know this. I don't know your level in Access, but I thought I would bring it up, just in case...

[JoeM]

Sorry, I was away out of town for a few days, but looks like you got some good responses from Orange and ssanfu.