Queries for sums where something is true and false

[icthus123]

I'm trying to create a query and have a problem.

I have a school number column, against which is cost column, against which is also a column with an option of "final" or "pending".

I need to create a query which gives the school number and the sum of all the costs which correspond to a given school number. However, I also need to have the sums that are "final" separate from the ones that are "pending." I don't know how to do the latter. Any ideas?

[CreganTur]

When you say
I also need to have the sums that are "final" separate from the ones that are "pending."

Do you mean that you need a sum column for all pending accounts and a separate sum column for all final accounts?

If so, then you could build a query that uses 2 calculated fields- a 'pending' field ( SchoolNumber & " " & Sum WHERE fldOption = 'Pending') and a 'final' field (SchoolNumber & " " & Sum WHERE fldOption = 'Final')

[icthus123]

When you say

Do you mean that you need a sum column for all pending accounts and a separate sum column for all final accounts?

If so, then you could build a query that uses 2 calculated fields- a 'pending' field ( SchoolNumber & " " & Sum WHERE fldOption = 'Pending') and a 'final' field (SchoolNumber & " " & Sum WHERE fldOption = 'Final')

Yes, that's exactly what I mean! Right, I've tried to do as you suggest, but I don't seem to be able to implement it! How do I do it? :dunno

[ben.oates]

Hi icthus,

It would be much easier to list them seperately. To do this, you need to use "Group By" and "Sum" SQL functions.

I created an example table for this so you can check that it works before fiddling with your own:

SchoolNum Cost Status
1 ?1,500.00 Pending
1 ?200.00 Pending
1 ?4,800.00 Final
1 ?800.00 Final
2 ?15,000.00 Final
3 ?52,800.00 Final
3 ?2,600.00 Pending


And the SQL I used is:

SELECT SchoolTest.SchoolNum, Sum(SchoolTest.Cost) As [Total Cost], SchoolTest.Status FROM SchoolTest GROUP BY SchoolNum, Status;

Which gave me:

SchoolNum Total Cost Status
1 ?5,600.00 Final
1 ?1,700.00 Pending
2 ?15,000.00 Final
3 ?52,800.00 Final
3 ?2,600.00 Pending

As you can see, it has added the values for school 1 together but kept results for Final and Pending seperate. By the way, I originally entered the schools in a random order in the table but it returned them in School order. Don't worry about the order of your table. It makes no difference.

Hope this helps.

[Edit because tables don't display properly on here]

[icthus123]

Hi icthus,

It would be much easier to list them seperately. To do this, you need to use "Group By" and "Sum" SQL functions.

I created an example table for this so you can check that it works before fiddling with your own:

SchoolNum Cost Status
1 ?1,500.00 Pending
1 ?200.00 Pending
1 ?4,800.00 Final
1 ?800.00 Final
2 ?15,000.00 Final
3 ?52,800.00 Final
3 ?2,600.00 Pending


And the SQL I used is:

SELECT SchoolTest.SchoolNum, Sum(SchoolTest.Cost) As [Total Cost], SchoolTest.Status FROM SchoolTest GROUP BY SchoolNum, Status;

Which gave me:

SchoolNum Total Cost Status
1 ?5,600.00 Final
1 ?1,700.00 Pending
2 ?15,000.00 Final
3 ?52,800.00 Final
3 ?2,600.00 Pending

As you can see, it has added the values for school 1 together but kept results for Final and Pending seperate. By the way, I originally entered the schools in a random order in the table but it returned them in School order. Don't worry about the order of your table. It makes no difference.

Hope this helps.

[Edit because tables don't display properly on here]

Thanks, but that doesn't really work for what I want.

I've got to produce a report you see, and on the report I have one column which is called "Amount Committed" this is the sum that is not pending and I have another column which is "Amount in Progress" which is the amount pending. :think:

[ben.oates]

Of course if you want to get complicated and have all the information on one line, I just figured out another option:

SELECT SchoolTest.SchoolNum, Sum(SWITCH( SchoolTest.Status = "Pending", SchoolTest.Cost, SchoolTest.Status="Final", 0)) As [Amount in Progress], Sum(SWITCH( SchoolTest.Status = "Pending", 0, SchoolTest.Status="Final", SchoolTest.Cost)) As [Amount Committed] FROM SchoolTest GROUP BY SchoolNum;

Gives:

SchoolNum Amount in Progress Amount Committed
1 1700 5600
2 0 15000
3 2600 52800

Which are the same results as the last query and much prettier. :whistle:

[icthus123]

Of course if you want to get complicated and have all the information on one line, I just figured out another option:

SELECT SchoolTest.SchoolNum, Sum(SWITCH( SchoolTest.Status = "Pending", SchoolTest.Cost, SchoolTest.Status="Final", 0)) As [Amount in Progress], Sum(SWITCH( SchoolTest.Status = "Pending", 0, SchoolTest.Status="Final", SchoolTest.Cost)) As [Amount Committed] FROM SchoolTest GROUP BY SchoolNum;

Gives:

SchoolNum Amount in Progress Amount Committed
1 1700 5600
2 0 15000
3 2600 52800

Which are the same results as the last query and much prettier. :whistle:

Wow, that works great!! Thanks a lot!! :friends:

[ben.oates]

Wow, that works great!! Thanks a lot!! :friends:
No problem.

[icthus123]

One more problem! When I try to link other tables into the query the figures that I got from the above sums go completely crazy! Any ideas why? :banghead:

[Trevor]

hmmmm. try rebuilding your SQL query statment with the added linked tablels or fields ( sounds like you are using the query builder in access as oposed to hard coding or typing out your query in SQL view of the Query builder
If you are addin other fields or tables as an option to pull data from then you would need to re- write your SQL with a select Case but to help more with this we would need to know are you trying to do this in the query builder of SQL, hardcord your query, is the query a control source for a report to be generated ?

[ben.oates]

Could you post the SQL please? It might give us some clues as to why the figures are becoming invalid. Also, make sure you don't have the same named field in another table because I didn't explicitly identify the field in the above statement as being part of a specific table in the switch statement. You might need to do that.

[icthus123]

Also, make sure you don't have the same named field in another table because I didn't explicitly identify the field in the above statement as being part of a specific table in the switch statement. You might need to do that.

Aha, I think that must be the problem! I have the same named field in the tables that cause the problem. How can I identify which field I mean?

[CreganTur]

To identify fields from different tables you need to use the tblName.fldName convention. In SQL, using your code as example, it would look like:

SELECT SchoolTest.SchoolNum,SecondTable.SchoolNum, Sum(SWITCH( SchoolTest.Status = "Pending",
SchoolTest.Cost, SchoolTest.Status="Final", 0)) As [Amount in Progress],
Sum(SWITCH( SchoolTest.Status = "Pending", 0, SchoolTest.Status="Final", SchoolTest.Cost)) As [Amount Committed]
FROM SchoolTest, SecondTable GROUP BY SchoolNum

Basically it just means you need to declare all the tables that you're using as part of the SELECT clause along with the field name, and also you need to declare all the tables you're pulling from in the FROM statement.

Hope that helps to point you in the right direction ;)

[ben.oates]

Although CreganTur is completely correct with the [Table Name].[Field Name] advice it would still be helpful to see your SQL if you're having trouble.

I may be being pedantic but if SchoolNum is your relationship field (I imagine it would be) you shouldn't need to show it twice as above, but you would definitely need the table name to be specified at the end in the Group By component of the statement. Sorry, it's a problem of mine, to point these things out!

[icthus123]

Thanks CreganTur and ben.oates, but I'm still having problems! :banghead:

Here's my SQL

SELECT tblProjects.School, Format(nz(Sum(Switch([tblProjects].[Status]="Pending",[tblProjects].[DFC Y1],[tblProjects].[Status]="Approved" Or "Final" Or "Exceptionally Approved",0)),0),'?#,##0.00') AS [Amount in Progress], Format(nz(Sum(Switch([tblProjects].[Status]="Pending",0,[tblProjects].[Status]="Approved" Or "Final" Or "Exceptionally Approved",[tblProjects].[DFC Y1])),0),'?#,##0.00') AS [Amount Committed]
FROM ((tblLocalAuthority INNER JOIN (tblSchools INNER JOIN tblProjects ON tblSchools.[School Number] = tblProjects.School) ON (tblLocalAuthority.[Local Authority] = tblSchools.[Local Authority]) AND (tblLocalAuthority.[Local Authority] = tblProjects.[Local Authority])) INNER JOIN [tblDFC/School] ON (tblLocalAuthority.[Local Authority] = [tblDFC/School].[Local Authority]) AND (tblSchools.[School Number] = [tblDFC/School].School)) INNER JOIN tblInvoice ON (tblLocalAuthority.[Local Authority] = tblInvoice.LocalAuthority) AND (tblSchools.[School Number] = tblInvoice.School) AND (tblProjects.[Project Number] = tblInvoice.Project)
GROUP BY tblProjects.School;

[CreganTur]

I think I see the problem- you're nesting your Inner Joins in an unconventional way.

Take a look at this link, it discusses using Inner Join on 3 tables:http://www.waztech.com/support/sqlsnippets3.asp

NinjaEdit: Also, if this doesn't help, then I'd suggest putting a post about this in the SQL forum here on VBAX- there might be people there who could give you a better answer.

[ben.oates]

Do you need all those inner joins? You're not actually using any values from any of the tables in the code.

If you do put this up in the SQL forum then copy the link in here so I can take a look when I have a little more time. Thanks.

[ben.oates]

Do you need all those inner joins? You're not actually using any values from any of the tables in the code.

If you do put this up in the SQL forum then copy the link in here so I can take a look when I have a little more time. Thanks.

[icthus123]

Thanks CreganTur! I've tried changing my Inner Joins but I'm not really getting it to work! :banghead:


Do you need all those inner joins? You're not actually using any values from any of the tables in the code.

If you do put this up in the SQL forum then copy the link in here so I can take a look when I have a little more time. Thanks.

I do need them, because I'll be getting values from them later, I just want to get this bit right before I do!

Thanks I've added it to the SQL forum, the link is here: http://vbaexpress.com/forum/showthread.php?t=19131