COUNT and DISTINCT in Access

[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

 

[Marshall Barton]

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)

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]

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)

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.

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

 

[Marshall Barton]

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)

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.

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 ...)

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 ...)