duplicate queries

  • Thread starter Thread starter Pam
  • Start date Start date
P

Pam

I have a table of Juveniles who received a citation(s)
within a 3 month period. I have a query set up to count
and sort by age. This gives me (example: 10 years old
(16 citations), 11 years of age (10 citations)etc., this
is correct on the total number of citations given, but I
need for it to only count a duplicate name one time. If
the same juvenile (ex.age 10) receives more than one
citation, I want it to count this as 1. I hope I have not
made this confusing, but I need help. Thanks,
 
Select Count(PersonID) from tblBadKids
Group By PersonID.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
-----Original Message-----
I have a table of Juveniles who received a citation(s)
within a 3 month period. I have a query set up to count
and sort by age. This gives me (example: 10 years old
(16 citations), 11 years of age (10 citations)etc., this
is correct on the total number of citations given, but I
need for it to only count a duplicate name one time. If
the same juvenile (ex.age 10) receives more than one
citation, I want it to count this as 1. I hope I have not
made this confusing, but I need help. Thanks,
.
There is a query property named "Unique Values". This is
Click to expand...
set to "No" by default. If you change this to "Yes", it
should work for you.
 
You need to run two TOTALS queries.
The first one to Group By Name, Age and Citation. This
will show 1 Citation per name, per age.

Then you need to create another TOTALS query based on the
first one. Include the fields Age and Citation in the
design grid. Age is Group By, Citation is Count. This
will count the # of times a citation has been issued per
age based on 1 count per name (as per the first query).

Good Luck!
 
Back
Top