Sum of 2 lines of Count query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mary - 23 Mar 2006 21:00 GMT

Hello Access community,
I am a novice and want the sum of a query that results in
CountID Type
10 1
2 2

I would like to know how to get the sum of 12, preferably as a new field
within that same query, or else in a new query. Thank you in advance.
Mary

Reply to this Message

Darren - 23 Mar 2006 21:47 GMT

Just wrap you query in another query

Select Sum([Countid]) as SumOfCountID
From (Select Count([someField]) as CountID from [sometable])

> Hello Access community,
> I am a novice and want the sum of a query that results in
[quoted text clipped - 5 lines]
> within that same query, or else in a new query. Thank you in advance.
> Mary

Reply to this Message

Klatuu - 23 Mar 2006 21:49 GMT

You will need another query that uses this query as its recordsource. It
should have only one field, CountID, and be a Totals query using Sum in the
Group by.

> Hello Access community,
> I am a novice and want the sum of a query that results in
[quoted text clipped - 5 lines]
> within that same query, or else in a new query. Thank you in advance.
> Mary

Reply to this Message

Mary - 23 Mar 2006 21:53 GMT

Can you nest the count query into that main query in any way? thanks Klatuu,
m

> You will need another query that uses this query as its recordsource. It
> should have only one field, CountID, and be a Totals query using Sum in the
[quoted text clipped - 9 lines]
> > within that same query, or else in a new query. Thank you in advance.
> > Mary

Reply to this Message

Klatuu - 23 Mar 2006 22:02 GMT

Sorry, Mary, I really don't know if you can. It might be possible with a
CrossTab query, but I skipped class that day and it is a weak point for me.

> Can you nest the count query into that main query in any way? thanks Klatuu,
> m
[quoted text clipped - 12 lines]
> > > within that same query, or else in a new query. Thank you in advance.
> > > Mary

Reply to this Message

John Vinson - 24 Mar 2006 00:24 GMT

>Hello Access community,
>I am a novice and want the sum of a query that results in
[quoted text clipped - 5 lines]
>within that same query, or else in a new query. Thank you in advance.
>Mary

Not at all easily. A hairy UNION query might do it.

However, if you just want to *see* these subtotals, consider using a
Form (for onscreen use) or Report (for printing). Base it on this
query and put a textbox on the Form (or Report) footer with a control
source

=Sum([CountID])

John W. Vinson[MVP]

Reply to this Message