SUMIF

[Erin M 2021]

Columns: AppealID, ExpenseCode, ExpenseAmt

There can be several Expense codes per AppealID but the AppealID table will change over time. How can I generically say SUMIF ("AppealID is the same",ExpenseAmt)

What I don't want to do is have to recode this each year when new AppealIIDs are added.

=SUMIF([AppealID],"Specific Appeal ID",[ExpenseAmt])


Thanks in advance.

 

[Ranman256]

its not excel, youd run a query that SUM(field),
then you can add an IIF( [sumOfFld1]<[sumOfFld2],"answer1", "answer2")

 

[Erin M 2021]

its not excel, youd run a query that SUM(field),
then you can add an IIF( [sumOfFld1]<[sumOfFld2],"answer1", "answer2")

I only want to sum if the AppealID is the same. Not sum all.

 

[plog]

Please demonstrate your issue with data. Provide 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with when you feed in the data from A into your query.

Again, 2 sets of data--starting and expected results.

 

[Erin M 2021]

Please demonstrate your issue with data. Provide 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with when you feed in the data from A into your query.

Again, 2 sets of data--starting and expected results.

EAppealID	ExpenseAmt
G1	$1,000.00
G1	$600.00
G1	$40.00
G1	$42.00
G1	$1,200.00
G1	$1,400.00
F2	$30.00
F2	$300.00
F2	$30.00
F2	$150.00
F2	$75.00
F2	$400.00

EAppealID	ExpenseAmt
G1	$4,282.00
F2	$985.00

 

[Pat Hartman]

It is not clear what your problem is but the solution is to store the variable amount when you add the record. The alternative is more complex and requires you to keep dated price change records so that the lookup always has to find the price at the time the record was created.

 

[Erin M 2021]

It is not clear what your problem is but the solution is to store the variable amount when you add the record. The alternative is more complex and requires you to keep dated price change records so that the lookup always has to find the price at the time the record was created.

Not sure I understand. I'm wanting a query that sums if the appealID is the same. But we have new appealIDs frequently, so it needs to be a generic formula somehow.

 

[Mike Krailo]

Why wouldn't a simple totals query work based on the AppealID? Just group on AppealID.

 

[Gasman]

Am I missing something here?
A Group by query on EAppealID and sum of ExpenseAmt is all that is needed?

 

[plog]

Based on your sample data you just want a simple aggregate query:

Sum data by using a query - Microsoft Support

Learn how to use an aggregate function in Access to sum the data in a query result set.

support.microsoft.com

Your explanation made it sound as if it was conditional on some values being equal, but nothing in your data demonstrates it. So this query will achieve what you want:

SELECT EAppealID, SUM(ExpenseAmt) AS TotalExpenses
FROM YourTableNameHere
GROUP BY EAppealID;

If that doesn't do it, please provide more sample data to demonstrate where the above query fails.

 

[arnelgp]

maybe create a New table (tblGenericAppeal):

AppealName	EAppealID
A	G1
B	F2

then Join this table to your Expense table:

SELECT tblGenericAppeal.AppealName, Sum(Appeal_table.ExpenseAmt) AS SumOfExpenseAmt
FROM tblGenericAppeal INNER JOIN Appeal ON tblGenericAppeal.AppealID = Appeal_table.EAppealID
GROUP BY tblGenericAppeal.AppealName;


if EAppealID for "A" (or "B") changes, you add them to tblGenericAppeal table:

AppealName	EAppealID
A	G1
B	F2
A	H3	G1 was changes to H3
B	J4	F2 was changed to J4

your above query will Still work! (provided the New EAppealID is not a "recycled" one).