Checkbox Totals, how to? (1 Viewer)

[Jaydub1991]

Morning all,

My Data base is comprised of Tables which is organised by "Clients" and within these fields is a checkbox column. I am looking to calculate all the ticked boxes for each client.
Clients are separated by a GroupID but all reside in one Table. very basic view of it below ( there are more fields but they are irrelevant)

GroupID	Customer Name	File Closed
Client 1	Smith	Tickbox
Client 2	Jones	Tickbox
Client 1	Bob	Tickbox
Client 3	Davies	Tickbox
Client 2	Albert	Tickbox
Client 3	Peters	Tickbox

I would like the total for each Clients tickboxes displayed on a seperate form which makes up part of the menu for my database.

I am very very very basic with Access Databases and had the assistance of another to put this database together so please go easy on me!

Ta

James

 

[arnelgp]

create a Total query.
see query1.

 

[CJ_London]

tickboxes contain one of two values, -1 for true/ticked and 0 for not ticked

so you simply need an aggregate query

SELECT GROUPID, -sum(FileClosed) as numColsed
FROM myTable
GROUP BY GROUPID

 

[Jaydub1991]

Ok so sorry to confuse things, Thank you for you assistance to get me the number of Job closed, but how would I change this to show the Remaining open jobs?

 

[cheekybuddha]

SELECT
  GROUPID,
  -SUM(FileClosed) AS numClosed,
  SUM(IIf(FileClosed = False, 1, 0)) AS numOpen
FROM myTable
GROUP BY
  GROUPID
;

 

[arnelgp]

SELECT
    yourTable.GroupID,
    Sum([File Closed]*-1) AS [Total File Closed],
    Sum([File Closed]+1) AS [Total File Open]
FROM yourTable
GROUP BY yourTable.GroupID;

 

[Jaydub1991]

I have used the Query Method but i can only get it to show the total Files closed, Where would input this code

SELECT
    yourTable.GroupID,
    Sum([File Closed]*-1) AS [Total File Closed],
    Sum([File Closed]+1) AS [Total File Open]
FROM yourTable
GROUP BY yourTable.GroupID;

 

[cheekybuddha]

Where would input this code

Create a new query.

Don't add any tables.

Switch to SQL view.

Paste in the SQL suggested.

Run the query to see the results.

Open in design view if you wish to see how Access translates the SQL to the query.