Sum If Calculation Help

[CT_AccessHelp]

Haven't used access in years so I'm a little rusty. Trying to query sum of a column based on text selection of another column.

In the end I need Total Acres by Practice to equal Approved+Offered-Returned-Withdrawn CP23 would be 29 acres total in this example.



Any help yall can give would be appreciated, Thanks!

Practice	Acres	Status
CP23	12	Withdrawn
CP23	22	Approved
CP23	32	Offered
CP23	13	Returned
CP40	24	Approved
CP40	34	Offered

[CJ_London]

something like

SELECT Practice, sum(Acres*iif(status in ("approved","Offered"),1,-1)) as totalacres
FROM myTable
GROUP BY Practice

[John_G]

Use 2 queries.

First, create a crosstab query on the table, with Practice as the row heading, status as the Column heading, and Acres as the values.

Then create a second query with the first one as the source, and do the calculation there.

I tried it with your data - it works fine.

[John_G]

Hey, Ajax - I like that a lot better - very elegant.

J.

[CT_AccessHelp]

How would it work out if the table looked like the following? This is currently how the table is, I was maybe going to change it but if I can make it work the way they designed it I would like that as it will be a lot less work in the end.

CP23 CP40 Status
12 0 withdrawn
22 0 approved
32 0 offered
13 0 returned
0 24 approved
0 34 offered

[CJ_London]

the way you have your table at the moment is not normalised - every time you add a new practice, you need to modify the table and all related queries (which will be much more complicated). Store the data as you showed it in your original post

[CT_AccessHelp]

I was afraid you'd say that - This thing already has all kinds of forms reports and queries tied to it so I was hoping to not reinvent the wheel.

[CJ_London]

the time you spend now normalising the data will save you endless hours modifying reports and forms in the future and having to maintain unnecessarily complex code.

[CT_AccessHelp]

the time you spend now normalising the data will save you endless hours modifying reports and forms in the future and having to maintain unnecessarily complex code.

Thank you! I have updated the table and things are working much better now.