Count Problem

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
 
K

Klatuu

You will need to post the SQL of the query for anyone to be able to offer any
advice.
 
A

antmorano

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));
 
M

Michel Walsh

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
 
Top