Count different values in the same field (1 Viewer)

[philwol]

Something of a beginner - so please excuse if this is a lack of basic knowledge

I have a table containing ID values 1 to 8 each with a text value which appear as text in a combo box on a form
These numeric values then appear in a 'MembersDetails' table

I am trying to write a query to give three outputs from MembersDetails

Count of Total Records
Count of Records where ID = 1 or 5 or 6 or 7
Count of Records where ID = 2 or 3 or 4 or 8

With three three values put into a report generated by a button click.

I have tried the Count function in the Totals line of a query but as this is all coming from the same table I can't get the desired result - either nonsense answers or errors.

Can anyone offer help that a relative beginner can follow.

Many thanks in anticipation

 

[plog]

With queries, what you want requires 3. 1 for total, 1 for the first set and 1 for the second set.

With a report. What you want requires just 1 query and 1 report and 1 new table to define what set each id belongs to. That table will will have 2 fields---id and set. For each id you assign it a set (e.g. id=1, set=1: id=2, set=2, id=3,set=2). Then you build your query using your 2 tables linking them via id. For the query you then bring down the set field and the field you want to count. Change the query to an aggregate query (click the Sigma/Summation symbol in the ribbon) and under the field you want to count change it from Group By to count. Save that query and you are ready to build the report.

The report is based off that query and at the report's footer you add a text box that sums the field you want to count.

 

[Josef P.]

select
   count(*) as TotalCount,
   Sum(iif(ID in (1, 5 , 6 , 7), 1, 0)) as Count_1_5_6_7,
   Sum(iif(ID in (2, 3 , 4 , 8), 1, 0)) as Count_2_3_4_8
from
   MembersDetails

It would be simpler to have a group identifier in the ID table:
ID | Grp | ...
1 | 1 |...
2 | 2 | ..
3 | 2 | ...

select
   count(*) as TotalCount,
   Sum(iif(I.Grp = 1, 1, 0)) as Count_Grp1,
   Sum(iif(I.Grp = 2, 1, 0)) as Count_Grp2
from
   MembersDetails as M
   Inner JOIN
   TabWithIDs as I ON I.ID = M.ID

or

select
   I.Grp as IdGroup,
   Count(*) as CountOfGroup
from
   MembersDetails as M
   Inner JOIN
   TabWithIDs as I ON I.ID = M.ID
group by
    I.Grp

 

[philwol]

Thank you for the help.

 

[Pat Hartman]

Since we have no way of knowing what your actual situation is given the dumbed down nature of the question, I would suggest a different possibility.

I agree with Josef P. Add a table that groups the items. This allows you to support more than two groups without code changes. You just change the table to add a new group. Then you can use a cross tab which will produce one column for each group plus a total for all the groups.