Using a query to count a field multiple times (sub totals and a total)

[HarrySpencer]

Hello

Sure this is possible and currently trying different things but haven't been able to find any information on what I'm trying to do but not really sure what to search for.

I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.

I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.

I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.

Any ideas and advice would be highly appreciated.

Many thanks, Harry

 

[CazB]

I'm no Guru myself but could you try using an expression in for each one? So you finish up with one field for each type, and then one for the total?
eg
MC: iif([type]="MC",1,0) (and Sum it)
MI: iif([type]="MI",1,0) (and Sum it)

... and so on?
For the overall total:
Total: Type (and Count it)

I hope that makes some kind of sense!

as SQL:

SELECT Sum(IIf([type]="MI",1,0)) AS MI, Sum(IIf([type]="MC",1,0)) AS MC, Count([type]) AS Total
FROM yourtable;

 

[Mihail]

This can't be managed at query level.
But are very easy to manage in a report.
And the report is as good as a query to be emailed.

 

[HarrySpencer]

Thanks for the replies. I have a quick update. I've manage to get the counts to work with sub queries but only if there is something to count. So if a person doesn't have a merit type it doesn't return zero and the query doesn't return any record.

So I guess a side question, is it possible for Access queries to count zero?

@CazB I'll give your suggestion a go and see what happens. The expression you've provided make sense and will report back what happens.

@Mihail Using a report might be a much better idea, the mail merge process isn't ever as smooth as I would like it to be so a SQL statement and a report sounds very appealing, I'll give it a go.

Cheers, Harry

 

[HarrySpencer]

@CazB you're idea worked with a little playing around.

I now have 8 queries working this all out. The main one collecting the the person info and then 8 total sub-queries, 7 using a variation of the expression you provided and shown below and the last one just doing a simple count on the type field to give me a total.

MC: Sum(IIf([Type]="MC",1,0))

The main query now returns a 0 if nothing is found and counts the types if there is data rather than not showing the record at all. Thanks for your help gonna use this again sometime I'm sure.

@Mihail think I'm probably going to a use a report rather than a mail merge as it would be a more user friendly and smoother expereince but looks like I'll be able to use the query rather than writting a SQL statement. Thanks for your help it was all very much appreciated.

Cheers, Harry