COUNT and DISTINCT in Access

  • Thread starter Thread starter mehstg1319
  • Start date Start date
M

mehstg1319

Hi there

I am trying to build a query in Access that uses Count and Distinct to
count unique records:

The code without the Distinct is as follows:
SELECT Count(tblyearlydetails.memberid) AS [Number of Members]
FROM TblMember, TblFlareEntry, tblFlare, tblyearlydetails, TblStatus
WHERE (((TblMember.TrueMember)=Yes) AND ((TblFlareEntry.memberid)=
[tblmember].[memberid]) AND ((TblFlareEntry.FlareID)=[TblFlare].
[FlareID]) AND ((tblFlare.FlareID)=[TblStatus].[FlareID]) AND
((TblMember.memberid)=[tblyearlydetails].[memberid]) AND
((tblyearlydetails.status)=[forms]![FrmReport_Flare]![Cbostatus]) AND
((tblFlare.[Registration Year])=[Forms]![FrmReport_Flare]![cboDate2])
AND ((TblStatus.[entered round 1])=Yes));

I cannot work out how to get the Distinct working, as I understand in
Access it is not possible to just write SELECT Count(DISTINCT
tblyearlydetails.memberid)

Any help would be much appreciated.


Paul
 
mehstg1319 said:
I am trying to build a query in Access that uses Count and Distinct to
count unique records:

The code without the Distinct is as follows:
SELECT Count(tblyearlydetails.memberid) AS [Number of Members]
FROM TblMember, TblFlareEntry, tblFlare, tblyearlydetails, TblStatus
WHERE (((TblMember.TrueMember)=Yes) AND ((TblFlareEntry.memberid)=
[tblmember].[memberid]) AND ((TblFlareEntry.FlareID)=[TblFlare].
[FlareID]) AND ((tblFlare.FlareID)=[TblStatus].[FlareID]) AND
((TblMember.memberid)=[tblyearlydetails].[memberid]) AND
((tblyearlydetails.status)=[forms]![FrmReport_Flare]![Cbostatus]) AND
((tblFlare.[Registration Year])=[Forms]![FrmReport_Flare]![cboDate2])
AND ((TblStatus.[entered round 1])=Yes));

I cannot work out how to get the Distinct working, as I understand in
Access it is not possible to just write SELECT Count(DISTINCT
tblyearlydetails.memberid)
Click to expand...


First you need to select the data, then you can count it.

These operations can be combined by using a subquery
something like:
SELECT Count(*) As [Number of Members]
FROM (SELECT DISTINCT ...)
or it may be easier/clearer to use two separate queries.
 
mehstg1319 said:
I am trying to build a query in Access that uses Count and Distinct to
count unique records:
Click to expand...
The code without the Distinct is as follows:
SELECT Count(tblyearlydetails.memberid) AS [Number of Members]
FROM TblMember, TblFlareEntry, tblFlare, tblyearlydetails, TblStatus
WHERE (((TblMember.TrueMember)=Yes) AND ((TblFlareEntry.memberid)=
[tblmember].[memberid]) AND ((TblFlareEntry.FlareID)=[TblFlare].
[FlareID]) AND ((tblFlare.FlareID)=[TblStatus].[FlareID]) AND
((TblMember.memberid)=[tblyearlydetails].[memberid]) AND
((tblyearlydetails.status)=[forms]![FrmReport_Flare]![Cbostatus]) AND
((tblFlare.[Registration Year])=[Forms]![FrmReport_Flare]![cboDate2])
AND ((TblStatus.[entered round 1])=Yes));
Click to expand...
I cannot work out how to get the Distinct working, as I understand in
Access it is not possible to just write SELECT Count(DISTINCT
tblyearlydetails.memberid)
Click to expand...

First you need to select the data, then you can count it.

These operations can be combined by using a subquery
something like:
        SELECT Count(*) As [Number of Members]
        FROM (SELECT DISTINCT ...)
or it may be easier/clearer to use two separate queries.
Click to expand...

Thank you

I had looked into this but didnt quite understand where the
tblyearlydetails.memberid would fit in if I used

SELECT Count(*) As [Number of Members]
FROM (SELECT DISTINCT ...)


Paul
 
mehstg1319 said:
mehstg1319 said:
I am trying to build a query in Access that uses Count and Distinct to
count unique records:
Click to expand...
The code without the Distinct is as follows:
SELECT Count(tblyearlydetails.memberid) AS [Number of Members]
FROM TblMember, TblFlareEntry, tblFlare, tblyearlydetails, TblStatus
WHERE (((TblMember.TrueMember)=Yes) AND ((TblFlareEntry.memberid)=
[tblmember].[memberid]) AND ((TblFlareEntry.FlareID)=[TblFlare].
[FlareID]) AND ((tblFlare.FlareID)=[TblStatus].[FlareID]) AND
((TblMember.memberid)=[tblyearlydetails].[memberid]) AND
((tblyearlydetails.status)=[forms]![FrmReport_Flare]![Cbostatus]) AND
((tblFlare.[Registration Year])=[Forms]![FrmReport_Flare]![cboDate2])
AND ((TblStatus.[entered round 1])=Yes));
Click to expand...
I cannot work out how to get the Distinct working, as I understand in
Access it is not possible to just write SELECT Count(DISTINCT
tblyearlydetails.memberid)
Click to expand...

First you need to select the data, then you can count it.

These operations can be combined by using a subquery
something like:
        SELECT Count(*) As [Number of Members]
        FROM (SELECT DISTINCT ...)
or it may be easier/clearer to use two separate queries.
Click to expand...

Thank you

I had looked into this but didnt quite understand where the
tblyearlydetails.memberid would fit in if I used

SELECT Count(*) As [Number of Members]
FROM (SELECT DISTINCT ...)
Click to expand...


If that's the field that is used to determine what is
distinct, then you probably want:

SELECT Count(*) As [Number of Members]
FROM (SELECT DISTINCT tblyearlydetails.memberid
FROM ...
WHERE ...)
 
Back
Top