Counting records in a query

T

Tony Williams

I have a query that counts records of various types . Here is the sql
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
DCount("[txtcompnbr]","tblcomplaints","[txtwithdrawn]=-1") AS [Count of
withdrawn], DCount("[txtcompnbr]","tblcomplaints","[txtcompcom]=-1") AS
[Count of Complaints Comm],
DCount("[txtcompnbr]","tblcomplaints","[txtintproc]=-1") AS [Count of
Internal], DCount("[txtcompnbr]","tblcomplaints","[txttype]='members'") AS
[Count of members],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='solicitors'") AS [Count of
solicitors], DCount("[txtcompnbr]","tblcomplaints","[txttype]='Defendants'")
AS [Count of Defendants],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Debt Recovery Firms'") AS
[Count of Debt Recovery Firms], tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

However I want all the count calculations to only count records where the
value of txtcompnbr is not Null. At the moment it is counting all records
for each type whether there is a value in txtcompnbr or not.

Help would be appreciated
Thanks
Tony
 
J

John Spencer

SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr
, Abs(Sum(TxtWithDrawn and txtCompNbr is Not Null)) as CountWithDrawn
, Abs(Sum(TxtCompCom and txtCompNbr is Not Null)) as CountCommComplaints
, Count(txtCompNbr + txtIntProc) as CountInternal
, Abs(Sum(txtCompNr is NOT Null and txtType='Members') ) as CountMembers
, Abs(Sum(txtCompNr is NOT Null and txtType='Solicitor')) as CountSolicitors
, Abs(Sum(txtCompNbr is NOT Null and txtType = 'Defendants')) as
CountDefendants
, Abs(Sum(textCompNr is NOT Null and txtType = 'Debt Recovery Firms'") AS
CountRecovery
, tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

If txtCompNbr can be other than null then the expressions will be more like
Abs(Sum(Len(txtCompNbr & "")>0 AND txtWithDrawn))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
T

Tony Williams

John I'm getting a Data mismatch error when I try to run the query?
Any ideas?
tony
John Spencer said:
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr
, Abs(Sum(TxtWithDrawn and txtCompNbr is Not Null)) as CountWithDrawn
, Abs(Sum(TxtCompCom and txtCompNbr is Not Null)) as CountCommComplaints
, Count(txtCompNbr + txtIntProc) as CountInternal
, Abs(Sum(txtCompNr is NOT Null and txtType='Members') ) as CountMembers
, Abs(Sum(txtCompNr is NOT Null and txtType='Solicitor')) as
CountSolicitors
, Abs(Sum(txtCompNbr is NOT Null and txtType = 'Defendants')) as
CountDefendants
, Abs(Sum(textCompNr is NOT Null and txtType = 'Debt Recovery Firms'") AS
CountRecovery
, tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

If txtCompNbr can be other than null then the expressions will be more
like
Abs(Sum(Len(txtCompNbr & "")>0 AND txtWithDrawn))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Tony Williams said:
I have a query that counts records of various types . Here is the sql
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
DCount("[txtcompnbr]","tblcomplaints","[txtwithdrawn]=-1") AS [Count of
withdrawn], DCount("[txtcompnbr]","tblcomplaints","[txtcompcom]=-1") AS
[Count of Complaints Comm],
DCount("[txtcompnbr]","tblcomplaints","[txtintproc]=-1") AS [Count of
Internal], DCount("[txtcompnbr]","tblcomplaints","[txttype]='members'")
AS [Count of members],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='solicitors'") AS [Count
of solicitors],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Defendants'") AS [Count
of Defendants], DCount("[txtcompnbr]","tblcomplaints","[txttype]='Debt
Recovery Firms'") AS [Count of Debt Recovery Firms],
tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

However I want all the count calculations to only count records where the
value of txtcompnbr is not Null. At the moment it is counting all records
for each type whether there is a value in txtcompnbr or not.

Help would be appreciated
Thanks
Tony
Click to expand...
Click to expand...
 
J

John Spencer

It is possible that txtType is not really a text field, but is a lookup
field based on a lookup combobox in the table. In that case the real
value of the field could be a number.

Try paring down the query to just

SELECT (txtCompNr is NOT Null and txtType='Members') as Member
, tblComplaints.txtdaterecd
FROM tblComplaints

If you get no errors on that add in the where clause.

WHERE (((Nz([txtcompnbr],""))<>""))

Still no errors then go back to the original query and add in one
calculation at a time until you do get an error.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Tony said:
John I'm getting a Data mismatch error when I try to run the query?
Any ideas?
tony
John Spencer said:
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr
, Abs(Sum(TxtWithDrawn and txtCompNbr is Not Null)) as CountWithDrawn
, Abs(Sum(TxtCompCom and txtCompNbr is Not Null)) as CountCommComplaints
, Count(txtCompNbr + txtIntProc) as CountInternal
, Abs(Sum(txtCompNr is NOT Null and txtType='Members') ) as CountMembers
, Abs(Sum(txtCompNr is NOT Null and txtType='Solicitor')) as
CountSolicitors
, Abs(Sum(txtCompNbr is NOT Null and txtType = 'Defendants')) as
CountDefendants
, Abs(Sum(textCompNr is NOT Null and txtType = 'Debt Recovery Firms'") AS
CountRecovery
, tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

If txtCompNbr can be other than null then the expressions will be more
like
Abs(Sum(Len(txtCompNbr & "")>0 AND txtWithDrawn))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Tony Williams said:
I have a query that counts records of various types . Here is the sql
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
DCount("[txtcompnbr]","tblcomplaints","[txtwithdrawn]=-1") AS [Count of
withdrawn], DCount("[txtcompnbr]","tblcomplaints","[txtcompcom]=-1") AS
[Count of Complaints Comm],
DCount("[txtcompnbr]","tblcomplaints","[txtintproc]=-1") AS [Count of
Internal], DCount("[txtcompnbr]","tblcomplaints","[txttype]='members'")
AS [Count of members],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='solicitors'") AS [Count
of solicitors],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Defendants'") AS [Count
of Defendants], DCount("[txtcompnbr]","tblcomplaints","[txttype]='Debt
Recovery Firms'") AS [Count of Debt Recovery Firms],
tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

However I want all the count calculations to only count records where the
value of txtcompnbr is not Null. At the moment it is counting all records
for each type whether there is a value in txtcompnbr or not.

Help would be appreciated
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

Thanks John, new day fresher mind. I'll do what you suggest.
Thanks again
Tony
John Spencer said:
It is possible that txtType is not really a text field, but is a lookup
field based on a lookup combobox in the table. In that case the real
value of the field could be a number.

Try paring down the query to just

SELECT (txtCompNr is NOT Null and txtType='Members') as Member
, tblComplaints.txtdaterecd
FROM tblComplaints

If you get no errors on that add in the where clause.

WHERE (((Nz([txtcompnbr],""))<>""))

Still no errors then go back to the original query and add in one
calculation at a time until you do get an error.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Tony said:
John I'm getting a Data mismatch error when I try to run the query?
Any ideas?
tony
John Spencer said:
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr
, Abs(Sum(TxtWithDrawn and txtCompNbr is Not Null)) as CountWithDrawn
, Abs(Sum(TxtCompCom and txtCompNbr is Not Null)) as CountCommComplaints
, Count(txtCompNbr + txtIntProc) as CountInternal
, Abs(Sum(txtCompNr is NOT Null and txtType='Members') ) as CountMembers
, Abs(Sum(txtCompNr is NOT Null and txtType='Solicitor')) as
CountSolicitors
, Abs(Sum(txtCompNbr is NOT Null and txtType = 'Defendants')) as
CountDefendants
, Abs(Sum(textCompNr is NOT Null and txtType = 'Debt Recovery Firms'")
AS CountRecovery
, tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

If txtCompNbr can be other than null then the expressions will be more
like
Abs(Sum(Len(txtCompNbr & "")>0 AND txtWithDrawn))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have a query that counts records of various types . Here is the sql
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
DCount("[txtcompnbr]","tblcomplaints","[txtwithdrawn]=-1") AS [Count of
withdrawn], DCount("[txtcompnbr]","tblcomplaints","[txtcompcom]=-1") AS
[Count of Complaints Comm],
DCount("[txtcompnbr]","tblcomplaints","[txtintproc]=-1") AS [Count of
Internal], DCount("[txtcompnbr]","tblcomplaints","[txttype]='members'")
AS [Count of members],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='solicitors'") AS
[Count of solicitors],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Defendants'") AS
[Count of Defendants],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Debt Recovery
Firms'") AS [Count of Debt Recovery Firms], tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

However I want all the count calculations to only count records where
the value of txtcompnbr is not Null. At the moment it is counting all
records for each type whether there is a value in txtcompnbr or not.

Help would be appreciated
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

John I played around with your original sql and ended up with this that
works:

SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
Abs(Sum(TxtWithDrawn And txtCompnbr Is Not Null)) AS CountWithDrawn,
Abs(Sum(TxtCompCom And txtCompnbr Is Not Null)) AS CountCommComplaints,
Abs(Sum(txtIntProc and txtCompnbr Is Not Null)) AS CountInternal,
Abs(Sum(txtCompNbr Is Not Null And txtType='Members')) AS CountMembers,
Abs(Sum(txtCompNbr Is Not Null And txtType='Solicitors')) AS
CountSolicitors,
Abs(Sum(txtCompNbr Is Not Null And txtType='Defendants')) AS
CountDefendants,
Abs(Sum(txtCompNbr Is Not Null And txtType='Debt Recovery Firms')) AS
CountRecovery,
tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

Now the next step is how do I amend this to give me the total for each of
the counts?
Thanks for all your help it got me on the right track.

Tony

John Spencer said:
It is possible that txtType is not really a text field, but is a lookup
field based on a lookup combobox in the table. In that case the real
value of the field could be a number.

Try paring down the query to just

SELECT (txtCompNr is NOT Null and txtType='Members') as Member
, tblComplaints.txtdaterecd
FROM tblComplaints

If you get no errors on that add in the where clause.

WHERE (((Nz([txtcompnbr],""))<>""))

Still no errors then go back to the original query and add in one
calculation at a time until you do get an error.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Tony said:
John I'm getting a Data mismatch error when I try to run the query?
Any ideas?
tony
John Spencer said:
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr
, Abs(Sum(TxtWithDrawn and txtCompNbr is Not Null)) as CountWithDrawn
, Abs(Sum(TxtCompCom and txtCompNbr is Not Null)) as CountCommComplaints
, Count(txtCompNbr + txtIntProc) as CountInternal
, Abs(Sum(txtCompNr is NOT Null and txtType='Members') ) as CountMembers
, Abs(Sum(txtCompNr is NOT Null and txtType='Solicitor')) as
CountSolicitors
, Abs(Sum(txtCompNbr is NOT Null and txtType = 'Defendants')) as
CountDefendants
, Abs(Sum(textCompNr is NOT Null and txtType = 'Debt Recovery Firms'")
AS CountRecovery
, tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

If txtCompNbr can be other than null then the expressions will be more
like
Abs(Sum(Len(txtCompNbr & "")>0 AND txtWithDrawn))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have a query that counts records of various types . Here is the sql
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
DCount("[txtcompnbr]","tblcomplaints","[txtwithdrawn]=-1") AS [Count of
withdrawn], DCount("[txtcompnbr]","tblcomplaints","[txtcompcom]=-1") AS
[Count of Complaints Comm],
DCount("[txtcompnbr]","tblcomplaints","[txtintproc]=-1") AS [Count of
Internal], DCount("[txtcompnbr]","tblcomplaints","[txttype]='members'")
AS [Count of members],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='solicitors'") AS
[Count of solicitors],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Defendants'") AS
[Count of Defendants],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Debt Recovery
Firms'") AS [Count of Debt Recovery Firms], tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

However I want all the count calculations to only count records where
the value of txtcompnbr is not Null. At the moment it is counting all
records for each type whether there is a value in txtcompnbr or not.

Help would be appreciated
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

To get a grand total you will need another query just like this one, but
where you drop the txtDateRecd field from the query.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Tony Williams said:
John I played around with your original sql and ended up with this that
works:

SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
Abs(Sum(TxtWithDrawn And txtCompnbr Is Not Null)) AS CountWithDrawn,
Abs(Sum(TxtCompCom And txtCompnbr Is Not Null)) AS CountCommComplaints,
Abs(Sum(txtIntProc and txtCompnbr Is Not Null)) AS CountInternal,
Abs(Sum(txtCompNbr Is Not Null And txtType='Members')) AS CountMembers,
Abs(Sum(txtCompNbr Is Not Null And txtType='Solicitors')) AS
CountSolicitors,
Abs(Sum(txtCompNbr Is Not Null And txtType='Defendants')) AS
CountDefendants,
Abs(Sum(txtCompNbr Is Not Null And txtType='Debt Recovery Firms')) AS
CountRecovery,
tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

Now the next step is how do I amend this to give me the total for each of
the counts?
Thanks for all your help it got me on the right track.

Tony

John Spencer said:
It is possible that txtType is not really a text field, but is a lookup
field based on a lookup combobox in the table. In that case the real
value of the field could be a number.

Try paring down the query to just

SELECT (txtCompNr is NOT Null and txtType='Members') as Member
, tblComplaints.txtdaterecd
FROM tblComplaints

If you get no errors on that add in the where clause.

WHERE (((Nz([txtcompnbr],""))<>""))

Still no errors then go back to the original query and add in one
calculation at a time until you do get an error.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Tony said:
John I'm getting a Data mismatch error when I try to run the query?
Any ideas?
tony
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr
, Abs(Sum(TxtWithDrawn and txtCompNbr is Not Null)) as CountWithDrawn
, Abs(Sum(TxtCompCom and txtCompNbr is Not Null)) as
CountCommComplaints
, Count(txtCompNbr + txtIntProc) as CountInternal
, Abs(Sum(txtCompNr is NOT Null and txtType='Members') ) as
CountMembers
, Abs(Sum(txtCompNr is NOT Null and txtType='Solicitor')) as
CountSolicitors
, Abs(Sum(txtCompNbr is NOT Null and txtType = 'Defendants')) as
CountDefendants
, Abs(Sum(textCompNr is NOT Null and txtType = 'Debt Recovery Firms'")
AS CountRecovery
, tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

If txtCompNbr can be other than null then the expressions will be more
like
Abs(Sum(Len(txtCompNbr & "")>0 AND txtWithDrawn))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have a query that counts records of various types . Here is the sql
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
DCount("[txtcompnbr]","tblcomplaints","[txtwithdrawn]=-1") AS [Count
of withdrawn],
DCount("[txtcompnbr]","tblcomplaints","[txtcompcom]=-1") AS [Count of
Complaints Comm],
DCount("[txtcompnbr]","tblcomplaints","[txtintproc]=-1") AS [Count of
Internal],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='members'") AS [Count
of members],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='solicitors'") AS
[Count of solicitors],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Defendants'") AS
[Count of Defendants],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Debt Recovery
Firms'") AS [Count of Debt Recovery Firms], tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

However I want all the count calculations to only count records where
the value of txtcompnbr is not Null. At the moment it is counting all
records for each type whether there is a value in txtcompnbr or not.

Help would be appreciated
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

Thanks John worked fine!
Tony
John Spencer said:
To get a grand total you will need another query just like this one, but
where you drop the txtDateRecd field from the query.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Tony Williams said:
John I played around with your original sql and ended up with this that
works:

SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
Abs(Sum(TxtWithDrawn And txtCompnbr Is Not Null)) AS CountWithDrawn,
Abs(Sum(TxtCompCom And txtCompnbr Is Not Null)) AS CountCommComplaints,
Abs(Sum(txtIntProc and txtCompnbr Is Not Null)) AS CountInternal,
Abs(Sum(txtCompNbr Is Not Null And txtType='Members')) AS CountMembers,
Abs(Sum(txtCompNbr Is Not Null And txtType='Solicitors')) AS
CountSolicitors,
Abs(Sum(txtCompNbr Is Not Null And txtType='Defendants')) AS
CountDefendants,
Abs(Sum(txtCompNbr Is Not Null And txtType='Debt Recovery Firms')) AS
CountRecovery,
tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

Now the next step is how do I amend this to give me the total for each of
the counts?
Thanks for all your help it got me on the right track.

Tony

John Spencer said:
It is possible that txtType is not really a text field, but is a lookup
field based on a lookup combobox in the table. In that case the real
value of the field could be a number.

Try paring down the query to just

SELECT (txtCompNr is NOT Null and txtType='Members') as Member
, tblComplaints.txtdaterecd
FROM tblComplaints

If you get no errors on that add in the where clause.

WHERE (((Nz([txtcompnbr],""))<>""))

Still no errors then go back to the original query and add in one
calculation at a time until you do get an error.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Tony Williams wrote:
John I'm getting a Data mismatch error when I try to run the query?
Any ideas?
tony
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr
, Abs(Sum(TxtWithDrawn and txtCompNbr is Not Null)) as CountWithDrawn
, Abs(Sum(TxtCompCom and txtCompNbr is Not Null)) as
CountCommComplaints
, Count(txtCompNbr + txtIntProc) as CountInternal
, Abs(Sum(txtCompNr is NOT Null and txtType='Members') ) as
CountMembers
, Abs(Sum(txtCompNr is NOT Null and txtType='Solicitor')) as
CountSolicitors
, Abs(Sum(txtCompNbr is NOT Null and txtType = 'Defendants')) as
CountDefendants
, Abs(Sum(textCompNr is NOT Null and txtType = 'Debt Recovery Firms'")
AS CountRecovery
, tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

If txtCompNbr can be other than null then the expressions will be more
like
Abs(Sum(Len(txtCompNbr & "")>0 AND txtWithDrawn))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have a query that counts records of various types . Here is the sql
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
DCount("[txtcompnbr]","tblcomplaints","[txtwithdrawn]=-1") AS [Count
of withdrawn],
DCount("[txtcompnbr]","tblcomplaints","[txtcompcom]=-1") AS [Count of
Complaints Comm],
DCount("[txtcompnbr]","tblcomplaints","[txtintproc]=-1") AS [Count of
Internal],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='members'") AS
[Count of members],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='solicitors'") AS
[Count of solicitors],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Defendants'") AS
[Count of Defendants],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Debt Recovery
Firms'") AS [Count of Debt Recovery Firms], tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

However I want all the count calculations to only count records where
the value of txtcompnbr is not Null. At the moment it is counting all
records for each type whether there is a value in txtcompnbr or not.

Help would be appreciated
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

Ah just one problem though! How do I then use the txtDateRecd as a date
parameter so that I'm only counting records between two dates?
Thanks
tony
John Spencer said:
To get a grand total you will need another query just like this one, but
where you drop the txtDateRecd field from the query.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Tony Williams said:
John I played around with your original sql and ended up with this that
works:

SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
Abs(Sum(TxtWithDrawn And txtCompnbr Is Not Null)) AS CountWithDrawn,
Abs(Sum(TxtCompCom And txtCompnbr Is Not Null)) AS CountCommComplaints,
Abs(Sum(txtIntProc and txtCompnbr Is Not Null)) AS CountInternal,
Abs(Sum(txtCompNbr Is Not Null And txtType='Members')) AS CountMembers,
Abs(Sum(txtCompNbr Is Not Null And txtType='Solicitors')) AS
CountSolicitors,
Abs(Sum(txtCompNbr Is Not Null And txtType='Defendants')) AS
CountDefendants,
Abs(Sum(txtCompNbr Is Not Null And txtType='Debt Recovery Firms')) AS
CountRecovery,
tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

Now the next step is how do I amend this to give me the total for each of
the counts?
Thanks for all your help it got me on the right track.

Tony

John Spencer said:
It is possible that txtType is not really a text field, but is a lookup
field based on a lookup combobox in the table. In that case the real
value of the field could be a number.

Try paring down the query to just

SELECT (txtCompNr is NOT Null and txtType='Members') as Member
, tblComplaints.txtdaterecd
FROM tblComplaints

If you get no errors on that add in the where clause.

WHERE (((Nz([txtcompnbr],""))<>""))

Still no errors then go back to the original query and add in one
calculation at a time until you do get an error.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Tony Williams wrote:
John I'm getting a Data mismatch error when I try to run the query?
Any ideas?
tony
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr
, Abs(Sum(TxtWithDrawn and txtCompNbr is Not Null)) as CountWithDrawn
, Abs(Sum(TxtCompCom and txtCompNbr is Not Null)) as
CountCommComplaints
, Count(txtCompNbr + txtIntProc) as CountInternal
, Abs(Sum(txtCompNr is NOT Null and txtType='Members') ) as
CountMembers
, Abs(Sum(txtCompNr is NOT Null and txtType='Solicitor')) as
CountSolicitors
, Abs(Sum(txtCompNbr is NOT Null and txtType = 'Defendants')) as
CountDefendants
, Abs(Sum(textCompNr is NOT Null and txtType = 'Debt Recovery Firms'")
AS CountRecovery
, tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

If txtCompNbr can be other than null then the expressions will be more
like
Abs(Sum(Len(txtCompNbr & "")>0 AND txtWithDrawn))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have a query that counts records of various types . Here is the sql
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
DCount("[txtcompnbr]","tblcomplaints","[txtwithdrawn]=-1") AS [Count
of withdrawn],
DCount("[txtcompnbr]","tblcomplaints","[txtcompcom]=-1") AS [Count of
Complaints Comm],
DCount("[txtcompnbr]","tblcomplaints","[txtintproc]=-1") AS [Count of
Internal],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='members'") AS
[Count of members],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='solicitors'") AS
[Count of solicitors],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Defendants'") AS
[Count of Defendants],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Debt Recovery
Firms'") AS [Count of Debt Recovery Firms], tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

However I want all the count calculations to only count records where
the value of txtcompnbr is not Null. At the moment it is counting all
records for each type whether there is a value in txtcompnbr or not.

Help would be appreciated
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Include the field in the where clause but don't group by it or put it in the
select clause.

In the query grid that would mean you select WHERE under the field instead
of Group By.



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Tony Williams said:
Ah just one problem though! How do I then use the txtDateRecd as a date
parameter so that I'm only counting records between two dates?
Thanks
tony
John Spencer said:
To get a grand total you will need another query just like this one, but
where you drop the txtDateRecd field from the query.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Tony Williams said:
John I played around with your original sql and ended up with this that
works:

SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
Abs(Sum(TxtWithDrawn And txtCompnbr Is Not Null)) AS CountWithDrawn,
Abs(Sum(TxtCompCom And txtCompnbr Is Not Null)) AS CountCommComplaints,
Abs(Sum(txtIntProc and txtCompnbr Is Not Null)) AS CountInternal,
Abs(Sum(txtCompNbr Is Not Null And txtType='Members')) AS CountMembers,
Abs(Sum(txtCompNbr Is Not Null And txtType='Solicitors')) AS
CountSolicitors,
Abs(Sum(txtCompNbr Is Not Null And txtType='Defendants')) AS
CountDefendants,
Abs(Sum(txtCompNbr Is Not Null And txtType='Debt Recovery Firms')) AS
CountRecovery,
tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

Now the next step is how do I amend this to give me the total for each
of the counts?
Thanks for all your help it got me on the right track.

Tony

It is possible that txtType is not really a text field, but is a lookup
field based on a lookup combobox in the table. In that case the real
value of the field could be a number.

Try paring down the query to just

SELECT (txtCompNr is NOT Null and txtType='Members') as Member
, tblComplaints.txtdaterecd
FROM tblComplaints

If you get no errors on that add in the where clause.

WHERE (((Nz([txtcompnbr],""))<>""))

Still no errors then go back to the original query and add in one
calculation at a time until you do get an error.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Tony Williams wrote:
John I'm getting a Data mismatch error when I try to run the query?
Any ideas?
tony
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr
, Abs(Sum(TxtWithDrawn and txtCompNbr is Not Null)) as CountWithDrawn
, Abs(Sum(TxtCompCom and txtCompNbr is Not Null)) as
CountCommComplaints
, Count(txtCompNbr + txtIntProc) as CountInternal
, Abs(Sum(txtCompNr is NOT Null and txtType='Members') ) as
CountMembers
, Abs(Sum(txtCompNr is NOT Null and txtType='Solicitor')) as
CountSolicitors
, Abs(Sum(txtCompNbr is NOT Null and txtType = 'Defendants')) as
CountDefendants
, Abs(Sum(textCompNr is NOT Null and txtType = 'Debt Recovery
Firms'") AS CountRecovery
, tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

If txtCompNbr can be other than null then the expressions will be
more like
Abs(Sum(Len(txtCompNbr & "")>0 AND txtWithDrawn))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have a query that counts records of various types . Here is the
sql
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
DCount("[txtcompnbr]","tblcomplaints","[txtwithdrawn]=-1") AS [Count
of withdrawn],
DCount("[txtcompnbr]","tblcomplaints","[txtcompcom]=-1") AS [Count
of Complaints Comm],
DCount("[txtcompnbr]","tblcomplaints","[txtintproc]=-1") AS [Count
of Internal],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='members'") AS
[Count of members],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='solicitors'") AS
[Count of solicitors],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Defendants'") AS
[Count of Defendants],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Debt Recovery
Firms'") AS [Count of Debt Recovery Firms],
tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

However I want all the count calculations to only count records
where the value of txtcompnbr is not Null. At the moment it is
counting all records for each type whether there is a value in
txtcompnbr or not.

Help would be appreciated
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

Thanks John
Tony
John Spencer said:
Include the field in the where clause but don't group by it or put it in
the select clause.

In the query grid that would mean you select WHERE under the field instead
of Group By.



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Tony Williams said:
Ah just one problem though! How do I then use the txtDateRecd as a date
parameter so that I'm only counting records between two dates?
Thanks
tony
John Spencer said:
To get a grand total you will need another query just like this one, but
where you drop the txtDateRecd field from the query.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

John I played around with your original sql and ended up with this that
works:

SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
Abs(Sum(TxtWithDrawn And txtCompnbr Is Not Null)) AS CountWithDrawn,
Abs(Sum(TxtCompCom And txtCompnbr Is Not Null)) AS CountCommComplaints,
Abs(Sum(txtIntProc and txtCompnbr Is Not Null)) AS CountInternal,
Abs(Sum(txtCompNbr Is Not Null And txtType='Members')) AS CountMembers,
Abs(Sum(txtCompNbr Is Not Null And txtType='Solicitors')) AS
CountSolicitors,
Abs(Sum(txtCompNbr Is Not Null And txtType='Defendants')) AS
CountDefendants,
Abs(Sum(txtCompNbr Is Not Null And txtType='Debt Recovery Firms')) AS
CountRecovery,
tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

Now the next step is how do I amend this to give me the total for each
of the counts?
Thanks for all your help it got me on the right track.

Tony

It is possible that txtType is not really a text field, but is a
lookup field based on a lookup combobox in the table. In that case
the real value of the field could be a number.

Try paring down the query to just

SELECT (txtCompNr is NOT Null and txtType='Members') as Member
, tblComplaints.txtdaterecd
FROM tblComplaints

If you get no errors on that add in the where clause.

WHERE (((Nz([txtcompnbr],""))<>""))

Still no errors then go back to the original query and add in one
calculation at a time until you do get an error.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Tony Williams wrote:
John I'm getting a Data mismatch error when I try to run the query?
Any ideas?
tony
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr
, Abs(Sum(TxtWithDrawn and txtCompNbr is Not Null)) as
CountWithDrawn
, Abs(Sum(TxtCompCom and txtCompNbr is Not Null)) as
CountCommComplaints
, Count(txtCompNbr + txtIntProc) as CountInternal
, Abs(Sum(txtCompNr is NOT Null and txtType='Members') ) as
CountMembers
, Abs(Sum(txtCompNr is NOT Null and txtType='Solicitor')) as
CountSolicitors
, Abs(Sum(txtCompNbr is NOT Null and txtType = 'Defendants')) as
CountDefendants
, Abs(Sum(textCompNr is NOT Null and txtType = 'Debt Recovery
Firms'") AS CountRecovery
, tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

If txtCompNbr can be other than null then the expressions will be
more like
Abs(Sum(Len(txtCompNbr & "")>0 AND txtWithDrawn))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have a query that counts records of various types . Here is the
sql
SELECT Count(tblComplaints.txtcompnbr) AS CountOftxtcompnbr,
DCount("[txtcompnbr]","tblcomplaints","[txtwithdrawn]=-1") AS
[Count of withdrawn],
DCount("[txtcompnbr]","tblcomplaints","[txtcompcom]=-1") AS [Count
of Complaints Comm],
DCount("[txtcompnbr]","tblcomplaints","[txtintproc]=-1") AS [Count
of Internal],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='members'") AS
[Count of members],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='solicitors'") AS
[Count of solicitors],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Defendants'") AS
[Count of Defendants],
DCount("[txtcompnbr]","tblcomplaints","[txttype]='Debt Recovery
Firms'") AS [Count of Debt Recovery Firms],
tblComplaints.txtdaterecd
FROM tblComplaints
WHERE (((Nz([txtcompnbr],""))<>""))
GROUP BY tblComplaints.txtdaterecd;

However I want all the count calculations to only count records
where the value of txtcompnbr is not Null. At the moment it is
counting all records for each type whether there is a value in
txtcompnbr or not.

Help would be appreciated
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count records in update query 11
Counting records 'prior to' other records 13
dcount in a query? 4
Counting yes/no selection in a query. 3
Counting the days sequentially in a month on a date field. 2
Counting records 1
How do I count non-duplicated records in a query in Access? 1
Counting records with Values 4

Top