query all subfield of an umbrella field

[jasminewhite]

my DB table:
CHAPTER FIELD1 FIELD2
1 text1a text2a
1 text1b text2b
2 text1c text2c
I want to do the following:
For each CHAPTER in the DB:
1)COUNT all FIELD1 records that belong to this CHAPTER
2)COUNT all FIELD2 records that belong to this CHAPTER
3)DISPLAY the above 2 counts on the same row of this CHAPTER
4)DISPLAY the SUM of the above 2 counts at the end of this row.
I want the query display the following results:
CHAPTER countFIELD1 countFIELD2 countThisCHAPTER
1 2 2 4
2 1 1 2
GRAND TOT 3 3 6
I need help creating SQL SELECT statement for my query. I use query wizard.
Thank you in advance for any big heart out there.

 

[Alansidman]

my DB table:
CHAPTER FIELD1 FIELD2
1 text1a text2a
1 text1b text2b
2 text1c text2c
I want to do the following:
For each CHAPTER in the DB:
1)COUNT all FIELD1 records that belong to this CHAPTER
2)COUNT all FIELD2 records that belong to this CHAPTER
3)DISPLAY the above 2 counts on the same row of this CHAPTER
4)DISPLAY the SUM of the above 2 counts at the end of this row.
I want the query display the following results:
CHAPTER countFIELD1 countFIELD2 countThisCHAPTER
1 2 2 4
2 1 1 2
GRAND TOT 3 3 6
I need help creating SQL SELECT statement for my query. I use query wizard.
Thank you in advance for any big heart out there.

Try this:

SELECT Table1.Chapter, Count(Table1.Field1) AS CountOfField1, Count(Table1.Field2) AS CountOfField2, [CountofField1]+[CountofField2] AS Total
FROM Table1]
GROUP BY Table1.Chapter;

To get the totals you will need to create a second query and sum the fields. Then you will need to create a UNION query to join the first two queries. See the example attached.

 

[jasminewhite]

Try this:

SELECT Table1.Chapter, Count(Table1.Field1) AS CountOfField1, Count(Table1.Field2) AS CountOfField2, [CountofField1]+[CountofField2] AS Total
FROM Table1]
GROUP BY Table1.Chapter;

To get the totals you will need to create a second query and sum the fields. Then you will need to create a UNION query to join the first two queries. See the example attached.

Alan, sorry for cross posting. Just joined forum for the 1st time, I am not aware of such problems. Thanks for the recommendations, will take it to heart.