Conditional Grouping

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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");

Ofer said:
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



Mykas_Robi said:
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
Click to expand...
Click to expand...
 
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");


Mykas_Robi said:
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");

Ofer said:
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



Mykas_Robi said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

KARL DEWEY said:
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");


Mykas_Robi said:
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");

Ofer said:
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
Click to expand...
Click to expand...
Click to expand...
 
Try this sql:
SELECT [claimant], sum([paid expense]) as SumofExp
FROM [010100-121905]
WHERE [claimant] NOT IN
(SELECT [claimant]
FROM [010100-121905]
WHERE [010100-121905].[status]<>"F")
Group By [010100-121905].[claimant];



--
Duane Hookom
MS Access MVP
--

Mykas_Robi said:
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

KARL DEWEY said:
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");


Mykas_Robi said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
Dear Mykas:

To reduce confusion, I'd like to state it appears to me Duane has the same
(hopefully correct) solution I posted only 6 minutes after he did, but with
somewhat more detail of your actual column names and table name.

Tom Ellison


Duane Hookom said:
Try this sql:
SELECT [claimant], sum([paid expense]) as SumofExp
FROM [010100-121905]
WHERE [claimant] NOT IN
(SELECT [claimant]
FROM [010100-121905]
WHERE [010100-121905].[status]<>"F")
Group By [010100-121905].[claimant];



--
Duane Hookom
MS Access MVP
--

Mykas_Robi said:
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

KARL DEWEY said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top