Conditional Grouping

[Mykas_Robi]

Hello, I need to group data based on a field (name). If another
field(status) includes all "F"s then group and sum, if not I don't want to
see it included in query results.

ex.

name staus expense
andy F 25
andy F 10
andy F 5
gail F 25
gail O 10
bob F 5
bob F 7

the results would be
andy 40
bob 12

Everytime I try to include IIf in query it doesn't do the grouping

Please, any assistance with this would be greatly appreciated.

thanks and have a nice day

 

[Ofer]

Try this

SELECT TableName.[Name], Sum(TableName.expense) AS SumOfexpense
FROM TableName
GROUP BY TableName.[Name]
HAVING TableName.[Name] In (SELECT TableName.[Name]
FROM TableName
WHERE TableName.staus="f")

If you have a field called Name, you better change it because it a reserved
name in Access

 

[Mykas_Robi]

Thanks Ofer, but I get the same results referencing the below example I get

andy 40
gail 35
bob 12

and gail should not show up.

sql statement is follows:
SELECT [010100-121905].[claimant], sum([010100-121905].[paid expense]) as
sumofexpense
FROM [010100-121905]
Group By [010100-121905].[claimant]
HAVING [010100-121905].[claimant] IN (select [010100-121905].[claimant] from
[010100-121905] where [010100-121905].[status]="F");

Try this

SELECT TableName.[Name], Sum(TableName.expense) AS SumOfexpense
FROM TableName
GROUP BY TableName.[Name]
HAVING TableName.[Name] In (SELECT TableName.[Name]
FROM TableName
WHERE TableName.staus="f")

If you have a field called Name, you better change it because it a reserved
name in Access
--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

Hello, I need to group data based on a field (name). If another
field(status) includes all "F"s then group and sum, if not I don't want to
see it included in query results.

ex.

name staus expense
andy F 25
andy F 10
andy F 5
gail F 25
gail O 10
bob F 5
bob F 7

the results would be
andy 40
bob 12

Everytime I try to include IIf in query it doesn't do the grouping

Please, any assistance with this would be greatly appreciated.

thanks and have a nice day

 

[KARL DEWEY]

Gail should show with 25 it seems to me.

Try this--
sql statement is follows:
SELECT [010100-121905].[claimant], sum([010100-121905].[paid expense]) as
sumofexpense
FROM [010100-121905]
Group By [010100-121905].[claimant]
WHERE [010100-121905].[status]="F");

Thanks Ofer, but I get the same results referencing the below example I get

andy 40
gail 35
bob 12

and gail should not show up.

sql statement is follows:
SELECT [010100-121905].[claimant], sum([010100-121905].[paid expense]) as
sumofexpense
FROM [010100-121905]
Group By [010100-121905].[claimant]
HAVING [010100-121905].[claimant] IN (select [010100-121905].[claimant] from
[010100-121905] where [010100-121905].[status]="F");

Try this

SELECT TableName.[Name], Sum(TableName.expense) AS SumOfexpense
FROM TableName
GROUP BY TableName.[Name]
HAVING TableName.[Name] In (SELECT TableName.[Name]
FROM TableName
WHERE TableName.staus="f")

If you have a field called Name, you better change it because it a reserved
name in Access
--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

Hello, I need to group data based on a field (name). If another
field(status) includes all "F"s then group and sum, if not I don't want to
see it included in query results.

ex.

name staus expense
andy F 25
andy F 10
andy F 5
gail F 25
gail O 10
bob F 5
bob F 7

the results would be
andy 40
bob 12

Everytime I try to include IIf in query it doesn't do the grouping

Please, any assistance with this would be greatly appreciated.

thanks and have a nice day

 

[Mykas_Robi]

Mr. Dewey and others, that is my dilemma. I do not want gail to show at all.
what quantifies a record showing is that the status for all grouped entries
should be "F". If all entries (for example for gail) are not "F", I do not
want it to show. I really appreciate the help thus far.

thanks

Gail should show with 25 it seems to me.

Try this--
sql statement is follows:
SELECT [010100-121905].[claimant], sum([010100-121905].[paid expense]) as
sumofexpense
FROM [010100-121905]
Group By [010100-121905].[claimant]
WHERE [010100-121905].[status]="F");

Thanks Ofer, but I get the same results referencing the below example I get

andy 40
gail 35
bob 12

and gail should not show up.

sql statement is follows:
SELECT [010100-121905].[claimant], sum([010100-121905].[paid expense]) as
sumofexpense
FROM [010100-121905]
Group By [010100-121905].[claimant]
HAVING [010100-121905].[claimant] IN (select [010100-121905].[claimant] from
[010100-121905] where [010100-121905].[status]="F");

Try this

SELECT TableName.[Name], Sum(TableName.expense) AS SumOfexpense
FROM TableName
GROUP BY TableName.[Name]
HAVING TableName.[Name] In (SELECT TableName.[Name]
FROM TableName
WHERE TableName.staus="f")

If you have a field called Name, you better change it because it a reserved
name in Access
--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

Hello, I need to group data based on a field (name). If another
field(status) includes all "F"s then group and sum, if not I don't want to
see it included in query results.

ex.

name staus expense
andy F 25
andy F 10
andy F 5
gail F 25
gail O 10
bob F 5
bob F 7

the results would be
andy 40
bob 12

Everytime I try to include IIf in query it doesn't do the grouping

Please, any assistance with this would be greatly appreciated.

thanks and have a nice day

 

[Tom Ellison]

Dear Mykas:

It seems to me you should filter out the undesired names using a NOT IN
clause:

SELECT name, staus, expense
FROM YourTable T
WHERE name NOT IN
(SELECT name FROM YourTable T1
WHERE staus <> "F")

Having eliminated these, base your totals query on the above.

Tom Ellison

 

[Mykas_Robi]

Thanks, That worked fine. i really do appreciate the time and effort it took
to decipher my request and put into SQL.

thanks again and happy holidays