|
 |
access thread: Unique/Distinct record filters?
Message #1 by devinda.r.fernando@d... on Fri, 25 Oct 2002 16:16:44
|
|
Name Award date of Award
Bob Good Behavior 1/1/2002
Bob Good Behavior 2/1/2002
Bob Good Behavior 3/1/2002
Bob Good Work 5/2/2002
Bob Good Work 3/23/2002
Bob Clean Teeth 4/1/2002
Bob Clean Breath 3/14/2002
Bob Clean Breath 7/18/2002
Bob Clean Breath 10/21/2002
Bob Clean Breath 6/4/2002
Joe Good Behavior 1/1/2002
Joe Good Behavior 3/1/2002
Joe Good Work 5/2/2002
Joe Clean Teeth 4/1/2002
Does any one know how I can narrow the above table records down to No
duplicates in the 1st two columns and the earliest date of that specific
award.
The result of the filtering should be the following:
Name Award First Date of Award
Bob Good Behavior 1/1/2002
Bob Good Work 3/23/2002
Bob Clean Teeth 4/1/2002
Bob Clean Breath 3/14/2002
Joe Good Behavior 1/1/2002
Joe Good Work 5/2/2002
Joe Clean Teeth 4/1/2002
I tried the DISTINCT and DISTINCTROW function but it does not work by
itself. Anyone know any creative SQL or ways to filter this?
-Devinda
Message #2 by "John Ruff" <papparuff@c...> on Fri, 25 Oct 2002 08:11:28 -0700
|
|
Will this work.
Create a Totals query. In the Totals: row under the field "Name" and
field "Award" select Group By. Under the field "date of Award" select
First in the Totals: row
John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities
www.noclassroom.com
Live software training
Right over the Internet
Home: xxx.xxx.xxxx
Cell: xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498
-----Original Message-----
From: devinda.r.fernando@d... [mailto:devinda.r.fernando@d...]
Sent: Friday, October 25, 2002 4:17 PM
To: Access
Subject: [access] Unique/Distinct record filters?
Name Award date of Award
Bob Good Behavior 1/1/2002
Bob Good Behavior 2/1/2002
Bob Good Behavior 3/1/2002
Bob Good Work 5/2/2002
Bob Good Work 3/23/2002
Bob Clean Teeth 4/1/2002
Bob Clean Breath 3/14/2002
Bob Clean Breath 7/18/2002
Bob Clean Breath 10/21/2002
Bob Clean Breath 6/4/2002
Joe Good Behavior 1/1/2002
Joe Good Behavior 3/1/2002
Joe Good Work 5/2/2002
Joe Clean Teeth 4/1/2002
Does any one know how I can narrow the above table records down to No
duplicates in the 1st two columns and the earliest date of that specific
award.
The result of the filtering should be the following:
Name Award First Date of Award
Bob Good Behavior 1/1/2002
Bob Good Work 3/23/2002
Bob Clean Teeth 4/1/2002
Bob Clean Breath 3/14/2002
Joe Good Behavior 1/1/2002
Joe Good Work 5/2/2002
Joe Clean Teeth 4/1/2002
I tried the DISTINCT and DISTINCTROW function but it does not work by
itself. Anyone know any creative SQL or ways to filter this?
-Devinda
Message #3 by "Gerald, Rand" <RGerald@u...> on Fri, 25 Oct 2002 10:24:12 -0500
|
|
Here is the SQL version of the query.
SELECT tblAward.Name, tblAward.Award, Min(tblAward.[Date of Award]) AS
[First Date of Award]
FROM tblAward
GROUP BY tblAward.Name, tblAward.Award;
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: devinda.r.fernando@d... [mailto:devinda.r.fernando@d...]
Sent: Friday, October 25, 2002 11:17
To: Access
Subject: [access] Unique/Distinct record filters?
Name Award date of Award
Bob Good Behavior 1/1/2002
Bob Good Behavior 2/1/2002
Bob Good Behavior 3/1/2002
Bob Good Work 5/2/2002
Bob Good Work 3/23/2002
Bob Clean Teeth 4/1/2002
Bob Clean Breath 3/14/2002
Bob Clean Breath 7/18/2002
Bob Clean Breath 10/21/2002
Bob Clean Breath 6/4/2002
Joe Good Behavior 1/1/2002
Joe Good Behavior 3/1/2002
Joe Good Work 5/2/2002
Joe Clean Teeth 4/1/2002
Does any one know how I can narrow the above table records down to No
duplicates in the 1st two columns and the earliest date of that
specific
award.
The result of the filtering should be the following:
Name Award First Date of Award
Bob Good Behavior 1/1/2002
Bob Good Work 3/23/2002
Bob Clean Teeth 4/1/2002
Bob Clean Breath 3/14/2002
Joe Good Behavior 1/1/2002
Joe Good Work 5/2/2002
Joe Clean Teeth 4/1/2002
I tried the DISTINCT and DISTINCTROW function but it does not work by
itself. Anyone know any creative SQL or ways to filter this?
-Devinda
Message #4 by "Devinda R Fernando" <devinda.r.fernando@d...> on Fri, 25 Oct 2002 11:46:11 -0400
|
|
Rand,
Unbelievable! It works like a charm. Thank you so much for your help.
Sincerely,
Devinda
--
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this
e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution
of the material in this e-mail is strictly forbidden.
Message #5 by "Amy Wyatt" <amyw@c...> on Wed, 30 Oct 2002 18:11:50
|
|
Just be careful using First. It will not necessarily give you what you
want. It is to extract the first record entered, it is not looking at the
date. If you want the Oldest (first) date the level is acheived you would
be better off using MIN([Date of Award]) instead of FIRST([Date of Award]).
Amy
> Here is the SQL version of the query.
SELECT tblAward.Name, tblAward.Award, Min(tblAward.[Date of Award]) AS
[First Date of Award]
FROM tblAward
GROUP BY tblAward.Name, tblAward.Award;
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: devinda.r.fernando@d... [mailto:devinda.r.fernando@d...]
Sent: Friday, October 25, 2002 11:17
To: Access
Subject: [access] Unique/Distinct record filters?
Name Award date of Award
Bob Good Behavior 1/1/2002
Bob Good Behavior 2/1/2002
Bob Good Behavior 3/1/2002
Bob Good Work 5/2/2002
Bob Good Work 3/23/2002
Bob Clean Teeth 4/1/2002
Bob Clean Breath 3/14/2002
Bob Clean Breath 7/18/2002
Bob Clean Breath 10/21/2002
Bob Clean Breath 6/4/2002
Joe Good Behavior 1/1/2002
Joe Good Behavior 3/1/2002
Joe Good Work 5/2/2002
Joe Clean Teeth 4/1/2002
Does any one know how I can narrow the above table records down to No
duplicates in the 1st two columns and the earliest date of that
specific
award.
The result of the filtering should be the following:
Name Award First Date of Award
Bob Good Behavior 1/1/2002
Bob Good Work 3/23/2002
Bob Clean Teeth 4/1/2002
Bob Clean Breath 3/14/2002
Joe Good Behavior 1/1/2002
Joe Good Work 5/2/2002
Joe Clean Teeth 4/1/2002
I tried the DISTINCT and DISTINCTROW function but it does not work by
itself. Anyone know any creative SQL or ways to filter this?
-Devinda
|
|
|
View Cart
