Sort query on sum of count + additional count

**TomBP** · 11-13-2010, 07:32 AM

Hi,

I want to create a query which sums up the count of my Purchaser. Not for the Purchaser in total but per Group and Organisation as well.

Seeing as my query building knowledge is insufficient I currently cope by doing the following:

1) Exporting the Access table to Excel.
2) Creating a Pivot on this table so I have the sum of count per Purchaser per Group per Organisation.
3) Copy this Pivot so I can autofill the blanks and then sort descendingly.

Is it possible to build a query in Access to go straight to the result of point 3?

I've attached a visual overview of my explanation above. The table mentioned in the overview is also added for people who want to fiddle around in Acces.

**alansidman** · 11-13-2010, 10:56 PM

I created a query using your table. I inserted the field purchaser twice. I then clicked on the summation icon (Sigma--looks like a big E). For one of the purchaser fields I changed it to count. Sorted that field descending. Looks pretty close to what you showed. See attached.

**TomBP** · 11-17-2010, 05:34 AM

Hi Alan,

That's exactly what I'm looking for. Thanks again for your help.

**TomBP** · 11-24-2010, 02:31 PM

Hi Alan,

I am trying to add a second and third count field in the (summation)-query but it either results in:

Attempt 1: Data type mismatch
Attempt 2: Wrong count

Do you mind taking a look at my attempts seeing as you solved this topic and for attempt 2 I used a Dcount expression which you provided for an earlier topic.

I've attached a visual overview of both attempts. The tables mentioned in the overview are also available in Access.

**alansidman** · 11-29-2010, 09:58 AM

Not sure exactly what you are looking to do. I have attached what I think you may be looking for.

Alan

**alansidman** · 11-29-2010, 09:59 AM

Forgot to upload attachment.

**TomBP** · 11-30-2010, 10:43 AM

Hi Alan,

I was looking for this:

The way to do multiple counts based on a fields value is to use IIF statements with SUM selected in the Totals dropdown