Count Problem

  • Thread starter Thread starter antmorano
  • Start date Start date
A

antmorano

Good Afternoon:

I am attempting to use the Count feature in the Totals row of my
query. When I do this I only receive "1" for all my records. Any
reason why this would happen?

-Anthony Morano
 
SELECT [All Plans].[Chain Sequence Number], [All Plans].[Retiree Last
Name], [All Plans].[Retiree First Name], [All Plans].[Retiree SSN],
[All Plans].Plan, [All Plans].Fund, [All Plans].[Retiree DOB], [All
Plans].[Retiree Gender], [All Plans].[Retiree Eligibility Date], [All
Plans].[Retirement Date], [All Plans].Address, [All Plans].City, [All
Plans].State, [All Plans].Zip, [All Plans].[Control Group], [All
Plans].[Spouse First Name], [All Plans].[Spouse Last Name], [All
Plans].[Spouse SSN], [All Plans].[Spouse DOB], [All Plans].[Spouse
Gender], [All Plans].[Spouse Eligibility Date], [All Plans].[Retiree
DOD], [All Plans].[Spouse DOD], [All Plans].[Date of Entry], [All
Plans].[Status of Coverage- Retiree], [All Plans].[Status of Coverage-
Spouse], IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],
[TODAY])-IIf(Format([Retiree DOB],"mmdd")<=Format([TODAY],"mmdd"),
0,1))) AS [Retiree Age], IIf([Spouse DOD] Is Null,Abs(DateDiff("yyyy",
[Spouse DOB],[TODAY])-IIf(Format([Spouse
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))) AS [Spouse Age]
FROM [All Plans]
WHERE ((([All Plans].Plan)="VA" Or ([All Plans].Plan)="VB" Or ([All
Plans].Plan)="VAL" Or ([All Plans].Plan)="VBL") AND (([All
Plans].Fund)="H&W") AND (([All Plans].[Retiree Eligibility
Date])<Date()) AND (([All Plans].[Control Group])<>"GRAND UNION,
NYVI") AND (([All Plans].[Status of Coverage- Retiree]) Is Null) AND
((IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],[TODAY])-
IIf(Format([Retiree DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65))
OR ((([All Plans].Plan)="VA" Or ([All Plans].Plan)="VB" Or ([All
Plans].Plan)="VAL" Or ([All Plans].Plan)="VBL") AND (([All
Plans].Fund)="H&W") AND (([All Plans].[Control Group])<>"GRAND UNION,
NYVI") AND (([All Plans].[Spouse Eligibility Date])<Date()) AND (([All
Plans].[Status of Coverage- Spouse]) Is Null) AND ((IIf([Spouse DOD]
Is Null,Abs(DateDiff("yyyy",[Spouse DOB],[TODAY])-IIf(Format([Spouse
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65));
 
There is probably just ONE record per group. I suspect you have to remove
some field in the GROUP BY list of fields, so some groups will become
logical 'bucket' for more than one record.


Vanderghast, Access MVP
 
Back
Top